Transform Rows to Columns

Question

Post reply

Transform Rows to Columns

shahbaz_awan

Valued Member

Points: 73
More actions
July 13, 2010 at 11:13 pm

#223245

Hi All,
How to transform the rows data into columns, e.g I have one table with following structure and values;
EMP_IDContact_typeContact_Number
1Home 92 - 51 - 225478
1Cell 92 - 131 - 225478
1Office 92 - 51 - 325478
2Home 92 - 41 - 225478
2Cell 92 - 131 - 225478
2Office 92 - 41 - 325478
2Fax 93 - 41 - 325528
Now after transformation I want to see this in this way.
EMP_IDHome Cell Office Fax
192 - 51 - 22547892 - 131 - 22547892 - 51 - 325478
292 - 41 - 22547892 - 131 - 22547892 - 41 - 325478 93 - 41 - 325528
Thanks in advance for your help.
Shahbaz.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

duplicate post http://www.sqlservercentral.com/Forums/Topic952011-338-1.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Use PIVOT TABLE approach

USE DOMAIN_DBA

GO

DECLARE @t TABLE (EMP_ID int , Contact_type nvarchar(50), Contact_Number nvarchar(300))

INSERT INTO @t

SELECT 1 ,'Home' ,'92 - 51 - 225478'

UNION SELECT 1, 'Cell', '92 - 131 - 225478'

UNION SELECT 1, 'Office', '92 - 51 - 325478'

UNION SELECT 2, 'Home', '92 - 41 - 225478'

UNION SELECT 2, 'Cell', '92 - 131 - 225478'

UNION SELECT 2, 'Office', '92 - 41 - 325478'

UNION SELECT 2, 'Fax', '93 - 41 - 325528'

select emp_id, ISNULL([cell],'') as [cell], ISNULL([mobile],'') [mobile],ISNULL([office],'') [office],ISNULL([fax],'') [fax]

from

(

SELECT EMP_ID, Contact_type , Contact_Number

FROM @T

) t

PIVOT

(max(contact_number) FOR Contact_type IN ( [cell], [mobile],[office],[fax])

) as pvt

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

shahbaz_awan Valued Member Points: 73 More actions · Answer 3

Thanks Mr. Bhuvnesh for your prompt reply.

But, how I can catter it if there are unlimited Contact_types? is it possible to write dynamic query for it ?

Shahbaz

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

shahbaz_awan (7/14/2010)
is it possible to write dynamic query for it ?

yes it is possible

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

shahbaz_awan Valued Member Points: 73 More actions · Answer 5

shahbaz_awan

Valued Member

Points: 73

July 14, 2010 at 1:29 am

#1192606

ONE EXAMPLE PLEASE ....

Shahbaz.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

google it as i did

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

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

shahbaz_awan Valued Member Points: 73 More actions · Answer 7

Thanks Mr. Bhuvnesh for ur help.

Shahbaz

msn: shahbaz_awan@hotmail.com

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 8

IF OBJECT_ID( 'tempdb..#Employees' ) IS NOT NULL

DROP TABLE #Employees

DECLARE@strColumns VARCHAR(1000)

DECLARE @strQuery VARCHAR(2000)

CREATE TABLE #Employees

(

EMP_IDINT,

Contact_typeNVARCHAR(50),

Contact_NumberNVARCHAR(300)

)

INSERT INTO #Employees

SELECT 1 ,'Home' ,'92 - 51 - 225478'

UNION ALL SELECT 1, 'Cell', '92 - 131 - 225478'

UNION ALL SELECT 1, 'Office', '92 - 51 - 325478'

UNION ALL SELECT 2, 'Home', '92 - 41 - 225478'

UNION ALL SELECT 2, 'Cell', '92 - 131 - 225478'

UNION ALL SELECT 2, 'Office', '92 - 41 - 325478'

UNION ALL SELECT 2, 'Fax', '93 - 41 - 325528'

SELECT@strColumns = COALESCE( @strColumns + ', ', '' )

+ '[' + Contact_type + ']'

FROM(

SELECTDISTINCT Contact_type

FROM#Employees

) T

SET@strQuery= ' SELECTemp_id, ' + @strColumns

+ ' FROM( '

+ ' SELECTEMP_ID, Contact_type , Contact_Number '

+ 'FROM#Employees '

+ ') T '

+ ' PIVOT( MAX( contact_number ) FOR Contact_type IN ( ' + @strColumns + ' ) ) AS PivotTable '

EXECUTE ( @strQuery )

IF OBJECT_ID( 'tempdb..#Employees' ) IS NOT NULL

DROP TABLE #Employees

This should get you started.

But as Bhuvnesh mentioned have a google search too

I am also attaching a link to an article by Jeff moden on this issue

http://www.sqlservercentral.com/articles/Crosstab/65048/

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/