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)
 splittinig a column into multiple rows?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akpaga
Constraint Violating Yak Guru

USA
313 Posts

Posted - 08/28/2013 :  12:11:09  Show Profile  Reply with Quote
Hi friends,

I have a two columns in table Customer in this way

City Name
Houston John
dallas James
Austin Jack


I would like to reaarane the data where
houston Dallas Austin
John James Jack

where the city names become column headers..Is there a way to do it?

Thank you very much

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/28/2013 :  12:21:24  Show Profile  Reply with Quote
you need to use pivot

SELECT *
FROM Table t
PIVOT (MAX(Name) FOR City IN ([Houston],[Dallas],[Austin]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 08/28/2013 :  12:23:58  Show Profile  Reply with Quote
If you have more than one name in a given city, add something else to distinguish them - for example, a row number like this:
SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY city ORDER BY name) AS RN FROM Table) t
PIVOT (MAX(Name) FOR City IN ([Houston],[Dallas],[Austin]))p
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/28/2013 :  12:24:33  Show Profile  Reply with Quote
to make citynames dynamic use

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
313 Posts

Posted - 08/28/2013 :  13:11:01  Show Profile  Reply with Quote
Hi visakh 16,

Thank You for the response but the city names are dynamic and can change..in that case how can i achieve this?


quote:
Originally posted by visakh16

to make citynames dynamic use

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/28/2013 :  13:13:06  Show Profile  Reply with Quote
quote:
Originally posted by akpaga

Hi visakh 16,

Thank You for the response but the city names are dynamic and can change..in that case how can i achieve this?


quote:
Originally posted by visakh16

to make citynames dynamic use

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs





see the logic used in the link

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
313 Posts

Posted - 08/28/2013 :  14:37:11  Show Profile  Reply with Quote
Thank You visakh16. I could get teh idea now.
but the dynamic sql of mine has parameter CorpID
where its is

EXEC dbo.dynamic_pivot 'SELECT City, name
FROM Customer
WHERE Convert(uniqueidentifier, @CorpID) = CorpID
,'City','Name',

Can you show me the proper syntax for the above query--eroor at @CorpID

Edited by - akpaga on 08/28/2013 14:37:39
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
313 Posts

Posted - 08/28/2013 :  15:56:16  Show Profile  Reply with Quote
Thank you visakh 16 i solved my issue.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/29/2013 :  03:36:24  Show Profile  Reply with Quote
quote:
Originally posted by akpaga

Thank you visakh 16 i solved my issue.


you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

kameswararao polireddy
Starting Member

India
19 Posts

Posted - 08/29/2013 :  04:27:16  Show Profile  Reply with Quote
Hi,
This is the sample data i had created while i am working with DYNAMIC PIVOT in one of my task.
I think this may meet your requirement.You can replace Your MAIN TABLE with the #table used in this example.


if object_id('tempdb..#Table') is not null

drop table #Table

CREATE TABLE #Table (
ID INT,
ColumnName VARCHAR(250),
Value VARCHAR(250)
)

INSERT INTO #Table SELECT 1,'name','Peter'
INSERT INTO #Table SELECT 1,'phone','12345678'
INSERT INTO #Table SELECT 1,'email','peter@host.com'
INSERT INTO #Table SELECT 2,'name','John'
INSERT INTO #Table SELECT 2,'phone','87654321'
INSERT INTO #Table SELECT 2,'email','john@host.com'
INSERT INTO #Table SELECT 3,'name','Sarah'
INSERT INTO #Table SELECT 3,'phone','55667788'
INSERT INTO #Table SELECT 3,'email','sarah@host.com'
select * from #Table

---I assumed your tablename as TESTTABLE---

DECLARE @cols NVARCHAR(2000)
DECLARE @query NVARCHAR(4000)

SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + t.ColumnName
FROM #Table AS t
--ORDER BY '],[' + t.ID
FOR XML PATH('')
), 1, 2, '') + ']'

SELECT @cols

SET @query = N'SELECT ID,'+ @cols +' FROM
(SELECT t1.ID,t1.ColumnName , t1.Value FROM #Table AS t1 where t1.id=1) p
PIVOT (MAX([Value]) FOR ColumnName IN ( '+ @cols +' ))
AS pvt;'

EXECUTE(@query)

P.Kameswara rao
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 08/29/2013 :  06:39:38  Show Profile  Reply with Quote
Use Pivot

SELECT [Houston],[Dallas],[Austin]
FROM (SELECT City,Name FROM TableName)t
PIVOT(MAX([Name]) FOR City in([Houston],[Dallas],[Austin]))p

veeranjaneyulu
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.12 seconds. Powered By: Snitz Forums 2000