Display multiple rows values as single row value

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Bharat Panthee » Display multiple rows values as single row...

Display multiple rows values as single row value

Rate Topic

Display Mode

Topic Options

Author

Message

Bharat Panthee

Posted Saturday, January 08, 2011 11:06 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 28, 2012 11:28 AM
Points: 444, Visits: 157

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

Post #1044895

Jonathan Armstrong

Posted Sunday, January 09, 2011 10:19 PM

Forum 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

Pulivarthi Sasidhar

Posted Sunday, January 09, 2011 10:59 PM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:30 AM
Points: 870, Visits: 522

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

slawomir.adamski

Posted Monday, January 10, 2011 3:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 7:57 AM
Points: 2, Visits: 15

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 Smile

because the how_much was 5272

Post #1045189

Cheetah

Posted Monday, January 10, 2011 4:25 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 22, 2013 3:26 AM
Points: 23, Visits: 246

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

craig buckham

Posted Monday, January 10, 2011 9:33 AM

Valued Member

Group: General Forum Members
Last Login: Monday, March 11, 2013 9:00 PM
Points: 54, Visits: 99

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

R-WIN

Posted Monday, January 10, 2011 1:28 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, October 07, 2012 8:15 AM
Points: 2, Visits: 10

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 »

Permissions