Post reply

Rows to Columns

  • 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

  • June 6, 2013 at 1:14 am

    #1621605

    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 ...

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 6, 2013 at 1:54 am

    #1621622

    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

  • June 6, 2013 at 2:26 am

    #1621632

    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

    NULL NULL

  • June 6, 2013 at 2:46 am

    #1621639

    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'

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 6, 2013 at 4:02 am

    #1621659

    Thanks for your help. It works perfectly 🙂

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

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