Pivot table in sql server 2000

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

General SQL Server Forums

New to SQL Server Programming

Pivot table in sql server 2000

Author

Topic

ruchijani
Starting Member

23 Posts

Posted - 2009-08-26 : 06:29:39

I have three table like

Table1

SId                Name
1                  abc
2                  def
3                  xyz


Table2

SId             Col1        Col2
1               SS          3
1               ES          2
1               MS          3
2               SS          5
2               ES          4
2               MS          2
3               SS          2
3               ES          5
3               MS          4


Table3

Id             Col3        Col4
1              SS           5
2              ES           5
3              MS           5


and i want output like this in sql server 2000

Sid      Name        SS         ES         MS
1         abc         3           2         3
2         def         5           4         2
3         xyz         2           5         4

Thanks
Ruchi

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-26 : 07:20:05

select t.sid,t.name,max(case when t2.col1 = 'ss' then t2.col2 end ) as 'ss',
max(case when t2.col1 = 'es' then t2.col2 end ) as 'es',
max(case when t2.col1 = 'ms' then t2.col2 end ) as 'ms'
from table1 t
inner join table3 t1 on t1.id = t.sid and t1.col3 = t.name
inner join table2 t2 on t2.sid = t.sid

ruchijani
Starting Member

23 Posts

Posted - 2009-08-26 : 07:33:49

Thank u for your reply

But i cant use case in query because i want to generate it dynamically like if any one add another row in TABLE3 like
[CODE]
Table3

Id Col3 Col4
1 SS 5
2 ES 5
3 MS 5
4

[/CODE]

Thanks
Ruchi

ruchijani
Starting Member

23 Posts

Posted - 2009-08-26 : 07:44:55

Thank u for your reply

But i cant use case in query because i want to generate it dynamically like if anyone add rows in TABLE3 and TABLE2 like this

[CODE]
Table2

SId Col1 Col2
1 SS 3
1 ES 2
1 MS 3
1 SW 1
2 SS 5
2 ES 4
2 MS 2
2 SW 1
3 SS 2
3 ES 5
3 MS 4
3 SW 2

Table3
Id Col3 Col4
1 SS 5
2 ES 5
3 MS 5
4 SW 5

so output should look like

Sid Name SS ES MS SW
1 abc 3 2 3 1
2 def 5 4 2 1
3 xyz 2 5 4 2

so i want generate column of table3 dynamically according new records added

[/CODE]

Thanks
Ruchi

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-26 : 08:39:32

go through this link
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

ruchijani
Starting Member

23 Posts

Posted - 2009-08-26 : 09:02:57

quote:

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Thank u so much

Thanks
Ruchi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources