Home Forums SQL Server 7,2000 Administration converting Columns into Rows RE: converting Columns into Rows

  • September 22, 2009 at 4:41 am

    #1056672

    Dave Ballantyne (9/22/2009)

    Simple enough with a union

    select Job ,Name1_1 ,city1_1 , phone1_1

    from table

    union all

    select Job ,Name1_2 ,city1_2 , phone1_2

    from table

    union all

    select Job ,Name1_3 ,city1_3 , phone1_3

    from table

    union all

    select Job ,Name1_4 ,city1_4 , phone1_4

    from table

    Hi,

    Use the UNION to get correct result.

    create table #temp

    (

    jobs varchar(10),

    colA1 varchar(10),

    colB1 varchar(10),

    colC1 varchar(10),

    colA2 varchar(10),

    colB2 varchar(10),

    colC2 varchar(10),

    colA3 varchar(10),

    colB3 varchar(10),

    colC3 varchar(10)

    )

    insert into #temp

    select 'XYZ','aaaa','abcd','1111','bbbb','bcda','2222','cccc','cdab','3333'

    union all

    select 'YZA','aaaa','abcd','1111','bbbb','bcda','2222','cccc','cdab','3333'

    union all

    select 'ZAB','','','','','','','','',''

    select jobs ,colA1 ,colB1 , colC1

    from #temp

    union

    select jobs ,colA2 ,colB2 , colC2

    from #temp

    union

    select jobs ,colA3 ,colB3 , colC3

    from #temp

    RESULT

    jobscolA1colB1colC1

    XYZaaaaabcd1111

    XYZbbbbbcda2222

    XYZcccccdab3333

    YZAaaaaabcd1111

    YZAbbbbbcda2222

    YZAcccccdab3333

    ZAB

    select jobs ,colA1 ,colB1 , colC1

    from #temp

    union all

    select jobs ,colA2 ,colB2 , colC2

    from #temp

    union all

    select jobs ,colA3 ,colB3 , colC3

    from #temp

    RESULT

    jobscolA1colB1colC1

    XYZaaaaabcd1111

    YZAaaaaabcd1111

    ZAB

    XYZbbbbbcda2222

    YZAbbbbbcda2222

    ZAB

    XYZcccccdab3333

    YZAcccccdab3333

    ZAB