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

Combining values into comma separated list - serialize Expand / Collapse
Author
Message
Posted Friday, April 18, 2014 1:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 86, Visits: 151
Hi,

I have a requirement for SSRS report where part of the input has the following structure:

Store Number Store Owner
542 Jaklin Givargidze
542 Raymond G. Givargidze
557 Hui Juan Lu
557 Tong Yu Lu

but the user would like to see the following:

Store Number Store Owner
542 Jaklin Givargidze, Raymond G. Givargidze
557 Hui Juan Lu, Tong Yu Lu

I am sure that this can be coded, just don't know how. I believe that proper term is to "serialize" the values. Any help would be greatly appreciated.

thanks,

Petr
Post #1563159
Posted Friday, April 18, 2014 2:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
Take a look at this article. http://www.sqlservercentral.com/articles/comma+separated+list/71700/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1563170
Posted Friday, April 18, 2014 2:56 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:53 PM
Points: 143, Visits: 316
see this..
DECLARE @Input TABLE
(
Store_Number INT,
Store_Owner VARCHAR(32)
)

INSERT INTO @Input VALUES(542, 'Jaklin Givargidze'), (542, 'Raymond G. Givargidze'), (557, 'Hui Juan Lu'), (557, 'Tong Yu Lu')

SELECT DISTINCT Store_Number,
STUFF((SELECT ','+Store_Owner
FROM @Input S2
WHERE S1.Store_Number = S2.Store_Number
FOR XML PATH('')), 1, 1, '') AS Store_Owner
FROM @Input S1



Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1563180
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse