best way?...pivot tables?..joins? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

best way?...pivot tables?..joins?

Author

Topic

eddie
Starting Member

45 Posts

Posted - 2002-03-01 : 10:04:36

I have a table that holds many "raw score data" for a particular client. When the data is inserted, I have a trigger that uses different algorithms to score all this data and put the scores into a score table. So there is one row in table one, to many rows in table two depending on how many scales are scored:

EX.
table 1
Client Ques1 Ques2 Ques3 Ques4 Ques5
1 2 2 2 2 3

The trigger then takes Ques1 and 2 and scores them and 4 and 5 and scores them:
Table 2
Client Score Totalid
1 4 1
1 5 2

What I want to do now, write a script that will join the two tables to get this info"
Client, Ques1 Ques2 Ques3 Ques4 Ques5 Score1 Score2
1 2 2 2 2 3 4 5

What is the best way to do this?

Thanks,
Eddie

Jay99

468 Posts

Posted - 2002-03-01 : 11:34:52

select
t1.client,
t1.ques1,
t1.ques2,
t1.ques3,
t1.ques4,
t1.ques5,
(select t2.score from table2 t2 where t2.client = t1.client and t2.totalid = 1) as score1,
(select t2.score from table2 t2 where t2.client = t1.client and t2.totalid = 2) as score2
from
table1 t1

Jay

eddie
Starting Member

45 Posts

Posted - 2002-03-01 : 16:43:17

Thanks Jay, that works great!

Eddie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources