Post reply

I need to convert mulitple rows from col B to one row

  • November 24, 2008 at 3:02 pm

    #124970

    I need to do about 500,00 rows this way and it is currently done in a CURSOR..way to long..

    some how I need to con cat off the PK which will be col A...does not look like a pivot table will work

    current ---

    col A COL B

    43522791332

    4352280527

    4352280707

    4352280761

    4352281703

    4352281735

    4352281736

    need--

    col A COL B

    4352279 1332

    4352280 537,707,761

    4352281 703,735,736

    THANKS

  • November 24, 2008 at 3:16 pm

    #902780

    Hope the following test code helps you out:

    create table #TestTab (

    Col1 int,

    Col2 int

    );

    insert into #TestTab

    select 4352279, 1332 union all

    select 4352280, 527 union all

    select 4352280, 707 union all

    select 4352280, 761 union all

    select 4352281, 703 union all

    select 4352281, 735 union all

    select 4352281, 736;

    select

    *

    from

    #TestTab;

    select

    a.Col1,

    stuff((select N', ' + cast(b.Col2 as varchar)

    from #TestTab b

    where b.Col1 = a.Col1

    for xml path('')),1,2,'')

    from

    #TestTab a

    drop table #TestTab;

  • November 24, 2008 at 3:30 pm

    #902787

    FANTASTIC.......I should be able to fill the temp table with a qry and I am off and running

    😀

  • November 24, 2008 at 3:35 pm

    #902791

    Noticed one thing, make it a select distinct on the outer select.

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

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