Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Display multiple rows values as single row value Expand / Collapse
Author
Message
Posted Saturday, January 8, 2011 11:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 3:48 PM
Points: 444, Visits: 162
Comments posted to this topic are about the item Display multiple rows values as single row value
Post #1044895
Posted Sunday, January 9, 2011 10:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 29, 2012 5:21 PM
Points: 7, Visits: 55
Bharat,

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

Thanks!
Post #1045110
Posted Sunday, January 9, 2011 10:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:03 AM
Points: 1,062, Visits: 708
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
Post #1045117
Posted Monday, January 10, 2011 3:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 2:10 AM
Points: 4, Visits: 17
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
Post #1045189
Posted Monday, January 10, 2011 4:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 24, Visits: 282
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)
Post #1045207
Posted Monday, January 10, 2011 9:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:27 PM
Points: 68, Visits: 117
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.
Post #1045397
Posted Monday, January 10, 2011 1:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 3, 2014 4:20 AM
Points: 3, Visits: 12
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?
Post #1045553
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse