SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 03/26/2013 :  15:59:24  Show Profile  Reply with Quote

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



Thanks for you help in advance.

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 03/26/2013 :  17:53:05  Show Profile  Reply with Quote
select
	*
from
	@Table
pivot (max(studwor) for stuName in ([ChrisName],[ChrisCity],[ChrisCounty]
	,[ChrisphoneNumber],[ChrisDName],[ChrisDType]))P
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 03/27/2013 :  11:10:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 03/27/2013 :  11:56:58  Show Profile  Reply with Quote
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

609 Posts

Posted - 03/27/2013 :  13:19:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 03/27/2013 :  13:50:00  Show Profile  Reply with Quote
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.

Edited by - James K on 03/27/2013 13:50:26
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000