Post reply

How to get data in multiple columns into a single column

  • May 6, 2009 at 11:36 pm

    #130685

    Hi All,

    I need to transfer data in multiple columns into a single column

    for example if the source table is like

    Source table:

    Col1 Col2 Col3

    xxx yyy zzz

    I need to get the data into the destination table as 3 rows

    Destination Table:

    Col1

    xxx

    yyy

    zzz

    please help me out in this...

  • May 10, 2009 at 11:13 pm

    #991892

    you can try it

    pivot

  • May 11, 2009 at 1:15 am

    #991920

    Hi can you explain it in detail how to pivot it

  • May 11, 2009 at 3:00 am

    #991956

    You don't need a PIVOT but an UNPIVOT method.

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

    DROP TABLE #test

    CREATE TABLE #test

    (

    id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    col1 VARCHAR(10) NOT NULL,

    col2 VARCHAR(10) NOT NULL,

    col3 VARCHAR(10) NOT NULL

    )

    INSERT#test( col1, col2, col3 )

    SELECT'aaa', 'bbb', 'ccc'

    UNION ALL

    SELECT'xxx', 'yyy', 'zzz'

    SELECTid, colname, colvalue

    FROM#test t

    UNPIVOT ( colvalue FOR colname IN( [col1], [col2], [col3] ) ) up

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

    DROP TABLE #test

    --Ramesh

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

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