Rows to Columns

Question

Rows to Columns

asashank82

SSC Journeyman

Points: 96
More actions
June 6, 2013 at 12:08 am

#294150

Hi All,
I have a requirement where the data in the table will be in this format.
CREATE TABLE #T1(Provider VARCHAR(200), MsgType VARCHAR(100))
INSERT INTO #T1 VALUES('ADAM','MQ')
INSERT INTO #T1 VALUES('ADAM','ISO')
INSERT INTO #T1 VALUES('CDM','MQ')
SELECT * FROM #T1
Now I need to write a query that will return me the output in this format.
ADAM CDM
MQ MQ
ISO NULL
Here ADAM and CDM are column names and the data in MsgType are coming are rows.
Can you please help me in getting an idea of how to get an output in this format
Thanks
Aditya

Viewing 6 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

Do you know in advance what the column names will be? That is, does this have to be a dynamic query? The static version is easier to write ...

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

B Raj Dhakal SSChasing Mays Points: 602 More actions · Answer 2

Hi Aditya,

I think you should be able to do it using PIVOT. Have a look on

http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/

B Raj Dhakal

asashank82 SSC Journeyman Points: 96 More actions · Answer 3

Hi,

I have used Pivot query but since I need to get values from MsgType field which is varchar, I am unable to get the desired result. Below is the query I am using.

CREATE TABLE #T1(Provider VARCHAR(200), MsgType VARCHAR(100))

INSERT INTO #T1 VALUES('ADAM','MQ')

INSERT INTO #T1 VALUES('ADAM','ISO')

INSERT INTO #T1 VALUES('CDM','MQ')

SELECT [ADAM],[CDM]

FROM

(SELECT Provider,MsgType FROM #T1 ) p

PIVOT

(

MIN(MsgType)

FOR MsgType IN

( [ADAM],[CDM] )

) AS pvt

ORDER BY Provider

When I run this query, I am getting NULL value for the field MsgType.

ADAM CDM

NULL NULL

Phil Parkin SSC Guru Points: 246987 More actions · Answer 4

Something like this?

with msgTypes as (select distinct msgType from #t1)

select m.msgType, ADAM = a.MsgType, CDM = c.msgType

from msgTypes m

left join #T1 a on m.msgType = a.MsgType and a.Provider = 'ADAM'

left join #t1 c on m.msgType = c.MsgType and c.Provider = 'CDM'

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

asashank82 SSC Journeyman Points: 96 More actions · Answer 5

asashank82

SSC Journeyman

Points: 96

June 6, 2013 at 4:02 am

#1621659

Thanks for your help. It works perfectly 🙂