Restructuring in query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Restructuring in query

Author

Topic

Adam Maras
Starting Member

6 Posts

Posted - 2007-07-09 : 12:44:35

I have data that looks essentially like this:


int       int     bit                bit        int
RecordNo  TestNo  HasSecondaryValue  Secondary  Score
-------------------------------------------------------
1         1       0                  null       10
2         2       0                  null       20
3         3       1                  0          30
4         3       1                  1          40

I would like to write a query that turns that into something like this:


int       int     bit                int    int           int
RecordNo  TestNo  HasSecondaryValue  Score  PrimaryScore  SecondaryScore
--------------------------------------------------------------------------
1         1       0                  10     null          null
2         2       0                  20     null          null
3         3       1                  null   30            40

I'm relatively new to SQL, could somebody help me with this?

Thanks,
Adam

DaleJ
Starting Member

7 Posts

Posted - 2007-07-09 : 13:49:03

create table #t (RecordNo int, TestNo int, HasSecondaryValue bit, Secondary bit, Score int)

insert into #t values (1, 1, 0, null, 10)
insert into #t values (2, 2, 0, null, 20)
insert into #t values (3, 3, 1, 0, 30)
insert into #t values (4, 3, 1, 1, 40)

SELECT min(RecordNo) as RecordNo,
TestNo,
SUM(Score) Score,
SUM([Primary Score]) [Primary Score],
SUM([Secondary Score]) [Secondary Score]
FROM
(
SELECT RecordNo, TestNo,
CASE HasSecondaryValue WHEN 0 THEN Score ELSE null END Score,
CASE HasSecondaryValue WHEN 1 THEN
CASE Secondary WHEN 0 THEN Score
ELSE 0
END
ELSE null END [Primary Score],
CASE HasSecondaryValue WHEN 1 THEN
CASE Secondary WHEN 1 THEN Score
ELSE 0
END
ELSE null END [Secondary Score]
FROM #t
) as Scores
GROUP BY TestNo

Adam Maras
Starting Member

6 Posts

Posted - 2007-07-09 : 14:49:26

Thank you! Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources