Post reply

Transpose data

  • February 27, 2009 at 1:16 am

    #126733

    I have following table

    if object_id('tempdb..#TEST') is not null

    drop table #TEST;

    CREATE TABLE #TEST(

    [DealNo] [int] NOT NULL,

    [Comment1] [varchar](512) NULL,

    [Comment2] [varchar](512) NULL,

    [Comment3] [varchar](512) NULL,

    ) ON [PRIMARY]

    GO

    INSERT INTO #TEST

    SELECT 1, '1Comment1','1Comment2','1Comment3'

    UNION ALL

    SELECT 2, '2Comment1',NULL,'2Comment3'

    UNION ALL

    SELECT 3, NULL,'3Comment2','3Comment3'

    SELECT * FROM #TEST

    I want the result in following format:

    DealNo CommentType Comment

    1 Comment1 1Comment1

    1 Comment2 1Comment2

    1 Comment3 1Comment3

    2 Comment1 2Comment1

    2 Comment3 2Comment3

    3 Comment2 3Comment2

    3 Comment3 3Comment3

    Can you please help me?

    -Vikas Bindra

  • February 27, 2009 at 2:13 am

    #951029

    SELECT DealNo,'Comment1' AS CommentType,Comment1 AS Comment

    FROM #TEST

    WHERE Comment1 IS NOT NULL

    UNION ALL

    SELECT DealNo,'Comment2' AS CommentType,Comment2 AS Comment

    FROM #TEST

    WHERE Comment2 IS NOT NULL

    UNION ALL

    SELECT DealNo,'Comment3' AS CommentType,Comment3 AS Comment

    FROM #TEST

    WHERE Comment3 IS NOT NULL

    ORDER BY DealNo,CommentType,Comment

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 27, 2009 at 3:30 am

    #951042

    Thanks Mark!!

    -Vikas Bindra

Viewing 3 posts - 1 through 2 (of 2 total)

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