Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akpaga
Constraint Violating Yak Guru

USA
331 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
52326 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

3873 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
52326 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
331 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
52326 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
331 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
331 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
52326 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  
 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.1 seconds. Powered By: Snitz Forums 2000