none
SQL SERVER 2012 T-SQL RRS feed

  • Question

  • Hi,

    Please provide me the query to get output from the below table.

    Table:

    1 abc Project1
    1 def Project1
    2 pqr Project2
    2 xyz Project2
    1 ghi Project1
    3 mno Project3

    Out put: 

    1 Project1 abc,def,ghi
    2 Project2 pqr,xyz
    3 Project3 mno

    Thanks,

    Rajesh.

    Tuesday, October 6, 2020 6:14 AM

All replies

  • Check the next sample on your server:

    create table #Table
    (
        n int,
        t varchar(10),
        p varchar(20)
    )
    
    insert into #Table values
    ( 1, 'abc', 'Project1' ),
    ( 1, 'def', 'Project1' ),
    ( 2, 'pqr', 'Project2' ),
    ( 2, 'xyz', 'Project2' ),
    ( 1, 'ghi', 'Project1' ),
    ( 3, 'mno', 'Project3' )
    
    select * from #Table
    
    /* 
    -- In modern SQL:
    select n, p, string_agg(t, ', ')
    from #Table
    group by n, p
    */
    
    ;
    with Q1 as
    (
        select *, row_number() over (partition by n order by t) as rn
        from #Table
    ),
    Q2 as
    (
        select *, cast(t as varchar(max)) as m
        from Q1
        where rn = 1
        union all
        select Q1.*, m + ', ' + Q1.t
        from Q2
        inner join Q1 on Q1.n = Q2.n and Q1.p = Q2.p and Q1.rn = Q2.rn + 1
    )
    select n, p, m 
    from Q2
    where rn = (select MAX(rn) from Q1 where Q1.n = Q2.n and Q1.p = Q2.p)
    order by n
    option (maxrecursion 0)


    Tuesday, October 6, 2020 10:43 AM