sqlserver 2000 query help (cross tab)

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)

sqlserver 2000 query help (cross tab)

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-10-12 : 22:53:13

I have a table with the following data:

-- Prepare test data
DECLARE @Test TABLE (ID TINYINT, RCODE VARCHAR(10))

INSERT @Test

SELECT 101, 'A1' UNION ALL
SELECT 102, 'A1' UNION ALL
SELECT 101, 'A2' UNION ALL
SELECT 102, 'A2' UNION ALL
SELECT 113, 'A2' UNION ALL
SELECT 120, 'A2' UNION ALL
SELECT 101, 'A3' UNION ALL
SELECT 102, 'A3' UNION ALL
SELECT 108, 'A3' UNION ALL
SELECT 124, 'A3' UNION ALL
SELECT 102, 'A4' UNION ALL
SELECT 102, 'A5' UNION ALL
SELECT 106, 'A5'
I need the below output with the query

id A1 A2 A3 A4 A5
--- -- ---- --- --- ---
101 A1 A2 A3 null null
102 A1 A2 A3 A4 A5
106 null null null null A5
108 null null A3 null null
113 null A2 null null null
120 null A2 null null null
124 null null A3 null null

Thanks for yur help in advance..

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-12 : 23:01:59

[code]

select
ID,
max( case when RCODE = 'A1' then RCODE else null end) A1 ,
max( case when RCODE = 'A2' then RCODE else null end) A2 ,
max( case when RCODE = 'A3' then RCODE else null end) A3 ,
max( case when RCODE = 'A4' then RCODE else null end) A4 ,
max( case when RCODE = 'A5' then RCODE else null end) A5
from
@Test
group by
ID
order by
ID

[/code]

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-13 : 08:27:11

If Rcode has many values, then refer this also
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources