Reformat Table with SQL - crosstab? - 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

Transact-SQL (2000)

Reformat Table with SQL - crosstab?

Author

Topic

budalite
Starting Member

1 Post

Posted - 2004-02-26 : 08:56:56

I have a table where there can be multiple lines for the same id. The difference is, one column in the table can either have an A,B,C,D etc in it. The best way I can do this is to try and create a table below showing my problem. I hope this comes out clear. This is what I am trying to do:

current table is like this:
ID OPTION
== ======
ID1 A
ID1 C
ID1 F
ID2 B
ID2 A
ID3 D
ID3 F

I need my final output to be this:
ID | A B C D E F
== = = = = = =
ID1|1 0 1 0 0 1
ID2|1 1 0 0 0 0
ID3|0 0 0 1 0 1

Any help is greatly appreciated. Sorry... can't think of a better way to explain this.
Thanks!

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-02-26 : 09:14:39

Try something like this

SELECT ID,
SUM(CASE option WHEN 'A' THEN 1 ELSE 0 END) AS 'A',
SUM(CASE option WHEN 'B' THEN 1 ELSE 0 END) AS 'B',
SUM(CASE option WHEN 'C' THEN 1 ELSE 0 END) AS 'C',
SUM(CASE option WHEN 'D' THEN 1 ELSE 0 END) AS 'D',
SUM(CASE option WHEN 'E' THEN 1 ELSE 0 END) AS 'E',
SUM(CASE option WHEN 'F' THEN 1 ELSE 0 END) AS 'F'
FROM TableName
GROUP BY id

Raymond

claire
Starting Member

19 Posts

Posted - 2004-02-26 : 09:14:47

Not that neat, but usable.

select id,sum(A) as A ,sum(b) as B ,sum(C) as C ,sum(D) as D ,sum(E) as E ,sum(F) as F from
(select id ,
case when [option] = 'A' and id = id then 1 else 0 end as 'A',
case when [option] = 'B' and id = id then 1 else 0 end as 'B',
case when [option] = 'c' and id = id then 1 else 0 end as 'C',
case when [option] = 'D' and id = id then 1 else 0 end as 'D',
case when [option] = 'E' and id = id then 1 else 0 end as 'E',
case when [option] = 'F' and id = id then 1 else 0 end as 'F'
from tb3 group by id,[option]) as a group by id

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-02-26 : 09:18:42

If you know how many different column values you can have then the above will do. If you do not then search this site for "Dynamic Cross-Tab"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources