Display multiple rows values as single row value

  • Bharat Panthee

    Default port

    Points: 1448

    Comments posted to this topic are about the item Display multiple rows values as single row value

  • Jonathan Armstrong

    SSC Enthusiast

    Points: 145

    Bharat,

    This looks like some useful code. Would it be possible to provide some discussion around how it works?

    Thanks!

  • Pulivarthi Sasidhar

    SSCertifiable

    Points: 6706

    Hi,

    U can declare a variable & and use "coalesce" or "ISNULL".

    U can get in Comma seperated by using "coalesce" or "ISNULL"

    Declare @var Varchar(MAX)

    CREATE Table #temp (id int identity(1,1),Name VARCHAR(10))

    INSERT INTO #temp

    Select 'a' UNION

    Select 'b' UNION

    Select 'c' UNION

    Select 'd' UNION

    Select 'e' UNION

    Select 'f' UNION

    Select 'g' UNION

    Select 'h'

    Select @var=ISNULL(@var,'')+Name+' , ' from #temp order by

    SELECT @var

    Regards,

    Pulivarthi Sasidhar

  • slawomir.adamski

    SSC Enthusiast

    Points: 130

    Very nice, but...

    I used that in my production database.

    Query:

    DECLARE @listValues VARCHAR(8000)

    DECLARE @how_much int

    SET @how_much = 0

    SELECT @listValues = ISNULL(@listValues + ';' + rTRIM(CAST(inv.NUMBER AS VARCHAR(8000))),

    rTRIM(CAST(inv.NUMBER AS VARCHAR(8000)))),

    @how_much = @how_much + 1

    FROM INVOICE AS inv

    ORDER BY inv.NUMBER

    SELECT list = @listValues, how_much = @how_much

    and I've got:

    list=ZZ/1573/04/10

    how_much=1

    but with query:

    DECLARE @listValues VARCHAR(8000)

    DECLARE @how_much int

    SET @how_much = 0

    SELECT @listValues = ISNULL(@listValues + ';' + rTRIM(CAST(inv.NUMBER AS VARCHAR(8000))),

    rTRIM(CAST(inv.NUMBER AS VARCHAR(8000)))),

    @how_much = @how_much + 1

    FROM INVOICE AS inv

    SELECT list = @listValues, how_much = @how_much

    I've got:

    list=FV 1/2009;FV 2/2009;P 1/2009;P 2/2009;FV 3/2009;FK 1/2009;FV 4/2009; FV 574/09;1498222;FV 5/2009;425/09/RA;FV 6/2009;FV 7/2009;FV 8/2009;FV 9/2009;FV 10/2009;FV 11/2009;FV 12/2009;FV 13/2009;FV 14/2009;FV 15/2009;FV 16/2009;FV 17/2009;FV 18/2009;FV 19/2009;FV 20/2009; ...

    and much more:-)

    because the how_much was 5272

  • Cheetah

    Old Hand

    Points: 332

    This is basicaly the concatenation of strings (like a sum for numeric).

    An alternative could be:

    declare @tblNames table([Name] varchar(100))

    insert @tblNames values ('Lucian')

    insert @tblNames values ('Terry')

    insert @tblNames values ('Jane')

    select SUBSTRING( (SELECT ',' + rtrim([Name]) FROM @tblNames

    FOR XML PATH('')) , 2, 7998) AS Name_List

    Result would be Lucian,Terry,Jane

    This is usefull when you need to aggregate a column and together with this concatenate a string column. ex.

    if exists(select 1 from information_schema.tables where table_name = 'tblTest')

    drop table tblTest

    Create table tblTest(id int , Category varchar(3),

    AnyNumber int, SCode varchar(255))

    insert tblTest(Id, Category, AnyNumber, SCode)

    select 1, 'Air', 15, 'SCode0' Union All

    select 1, 'Air', 15, 'SCode1' Union All

    select 1, 'Acc', 10, 'AccSCode1' Union All

    select 1, 'Acc', 10, 'AccSCode2' Union All

    select 2, 'Air', 9, 'AirSCode4' Union All

    select 2, 'Air', 15, 'AirSCode5' Union All

    select 3, 'Air', 20, 'AirSCode6' Union All

    select 3, 'Air', 13, 'AirSCode7' Union All

    select 3, 'Trf', 14, 'TrfSCode1' Union All

    select 3, 'Trf', 16, 'TrfSCode2'

    --select * from tblTest

    select T.Id, Category,

    sum(T.AnyNumber) as SumNbr

    ,SUBSTRING( (SELECT ',' + rtrim(SCode) FROM tblTest T2

    WHERE T.Id = T2.Id and T.Category = T2.Category

    FOR XML PATH('')) , 2, 7998) AS List_SCodes

    from tblTest T

    group by Id, Category

    drop table tblTest

    Give result:

    (10 row(s) affected)

    Id Category SumNbr List_SCodes

    ----------- -------- ----------- ----------------------------------------------------------

    1 Acc 20 AccSCode1,AccSCode2

    1 Air 30 SCode0,SCode1

    2 Air 24 AirSCode4,AirSCode5

    3 Air 33 AirSCode6,AirSCode7

    3 Trf 30 TrfSCode1,TrfSCode2

    (5 row(s) affected)

  • craig buckham

    SSC Enthusiast

    Points: 129

    In the original posting, the query should be

    SELECT @listValues = ISNULL( @listValues + @delimeter,'') +

    ISNULL(FirstName,'')

    FROM #Table1

    ORDER BY FirstName

    The original query will not include empty fields for NULL names, and worse, if you change the "ORDER BY" clause, it will re-start the lilst values variable every time a NULL name value is encountered. The first ISNULL is to skip the delimiter for the first field, the second is to make sure NULL values show up as empty fields.

  • R-WIN

    SSC Enthusiast

    Points: 108

    i think used delimeter not surprise to me, buat i ask to all, how if i have data eq "a;b;c;d;e", i want my report there are 5 records, anyone can help this?

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • R-WIN

    SSC Enthusiast

    Points: 108

    i have situation for my report

    table1

    no name prize

    1 A 1000

    1 B 2000

    1 C 3000

    1 D 4000

    1 E 5000

    2 A 1500

    2 E 5500

    3 E 6000

    i want my report like this

    A 1000 B 2000 C3000 D 4000 E 5000

    A 1500 B null C null D nulll E 5500

    A null B null C null D null E 6000

    can anyone help me ?

Viewing 9 posts - 1 through 9 (of 9 total)

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