Display multiple rows values as single row value

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

  • Bharat,

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

    Thanks!

  • 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

  • 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

  • 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)

  • 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.

  • 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?

  • Thanks for the script.

  • 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 8 (of 8 total)

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