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.
Author |
Topic |
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2013-03-26 : 15:59:24
|
[code]I need a query to get the required output..Thanks for your help in advance...DECLARE @Table TABLE( stid INT, stuName VARCHAR(200), studwor varchar(100))INSERT INTO @Table SELECT 255,'ChrisName','Chris'INSERT INTO @Table SELECT 179,'ChrisCity','SAN City'INSERT INTO @Table SELECT 179,'ChrisCounty','Wendy'INSERT INTO @Table SELECT 179,'ChrisphoneNumber','This is a test phone'INSERT INTO @Table SELECT 180,'ChrisDName','WTS Test'INSERT INTO @Table SELECT 180,'ChrisDType','This is for Dtpetest'Sourcetable:stid stuName studwor----- -------- --------255 ChrisName Chris179 ChrisCity SAN City179 ChrisCounty Wendy179 ChrisphoneNumber This is a test phone180 ChrisDName WTS Test180 ChrisDType This is for DtpetestExpected output:-----------------stid ChrisName ChrisCity ChrisCounty ChrisphoneNumber ChrisDName ChrisDType----- ---------- ----------- ------------ --------------- ---------- ----------255 Chris 179 SAN CITY Wendy This is a test phone180 WTS Test This is for Dtpetest[/code]Thanks for you help in advance. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-26 : 17:53:05
|
[code]select *from @Tablepivot (max(studwor) for stuName in ([ChrisName],[ChrisCity],[ChrisCounty] ,[ChrisphoneNumber],[ChrisDName],[ChrisDType]))P[/code] |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2013-03-27 : 11:10:09
|
Thanks James..But i don't won't be able to hard code the stuName since i have like more 100 stunames and have like 5 milllions records..stuName in ([ChrisName],[ChrisCity],[ChrisCounty] ,[ChrisphoneNumber],[ChrisDName],[ChrisDType]))P |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-27 : 11:56:58
|
You can use dynamic SQL - see here: http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2013-03-27 : 13:19:19
|
I did tried with dynamic sql due to performance the query did not show the results for more than 2 hours..is there any other way of writing the query.. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-27 : 13:50:00
|
I don't know of another way in SQL - you could use aggregate functions etc., but in the end, it is all pivoting.If you have more than 100 stunames and more than 5 million records, querying all of that would indeed take time. Apart from that, reading your posting and looking through the sample data, your requirements are not 100% clear to me. With 100+ stunames how many columns are you expecting to see in the output? If you can post sample data with more than one student (someone other than Chris), that would perhaps make it a bit more clearer. |
|
|
|
|
|
|
|