Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display multiple rows values as single row value


Display multiple rows values as single row value

Author
Message
Bharat Panthee
Bharat Panthee
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 162
Comments posted to this topic are about the item Display multiple rows values as single row value
Jonathan Armstrong
Jonathan Armstrong
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 55
Bharat,

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

Thanks!
Pulivarthi Sasidhar
Pulivarthi Sasidhar
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1000
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
slawomir.adamski
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 19
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
Cheetah
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 299
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
craig buckham
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 136
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
R-WIN
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 16
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?
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9686 Visits: 885
Thanks for the script.
R-WIN
R-WIN
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 16
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 ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search