splittinig a column into multiple rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

splittinig a column into multiple rows?

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2013-08-28 : 12:11:09

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

52326 Posts

Posted - 2013-08-28 : 12:21:24

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-28 : 12:23:58

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 12:24:33

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

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2013-08-28 : 13:11:01

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 13:13:06

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

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2013-08-28 : 14:37:11

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

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2013-08-28 : 15:56:16

Thank you visakh 16 i solved my issue.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-29 : 03:36:24

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

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-08-29 : 04:27:16

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

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-08-29 : 06:39:38

Use Pivot

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

veeranjaneyulu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources