Display multiple rows values as single row value

Question

Post reply

Display multiple rows values as single row value

Bharat Panthee

Default port

Points: 1448
More actions
January 8, 2011 at 11:06 am

#232747

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

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

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

Jonathan Armstrong SSC Enthusiast Points: 155 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

akljfhnlaflkj

SSC Guru

Points: 76202

May 17, 2016 at 6:59 am

#1879514

Thanks for the script.

R-WIN SSC Enthusiast Points: 108 More actions · Answer 8

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 ?