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 2008 Forums
 Transact-SQL (2008)
 Query Help

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 Chris
179 ChrisCity SAN City
179 ChrisCounty Wendy
179 ChrisphoneNumber This is a test phone
180 ChrisDName WTS Test
180 ChrisDType This is for Dtpetest

Expected output:
-----------------

stid ChrisName ChrisCity ChrisCounty ChrisphoneNumber ChrisDName ChrisDType
----- ---------- ----------- ------------ --------------- ---------- ----------
255 Chris
179 SAN CITY Wendy This is a test phone
180 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
@Table
pivot (max(studwor) for stuName in ([ChrisName],[ChrisCity],[ChrisCounty]
,[ChrisphoneNumber],[ChrisDName],[ChrisDType]))P[/code]
Go to Top of Page

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

Go to Top of Page

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
Go to Top of Page

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..
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -