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

Get distinct values into single column Expand / Collapse
Author
Message
Posted Sunday, February 17, 2013 10:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:25 PM
Points: 72, Visits: 246
Hi -

I am having trouble writing a query to get the distinct values of a column grouping into a single column. Please see the example below:

Data:

Store Name | Category | Product | Color

My Store | Clothing | Awesome Shirt | White
My Store | Clothing | Awesome Shirt | Green
My Store | Clothing | Awesome Shirt | Blue

My Store | Cars | Chevy Impala | White
My Store | Cars | Chevy Impala | Black
My Store | Cars | Chevy Impala | Grey

Desired Result:

My Store | Clothing | Awesome Shirt | WhiteGreenBlue
My Store | Cars | Chevy Impala | WhiteBlackGrey

Thanks for your help!
Post #1421033
Posted Monday, February 18, 2013 1:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
try this link
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx
Post #1421052
Posted Monday, February 18, 2013 2:34 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:07 PM
Points: 9,926, Visits: 11,183
SQLFiddle (link) showing the following solution:

DECLARE @Data AS TABLE
(
StoreName varchar(50) NOT NULL,
Category varchar(50) NOT NULL,
Product varchar(50) NOT NULL,
Color varchar(50) NOT NULL,

PRIMARY KEY (StoreName, Category, Product, Color)
);

INSERT @Data
(StoreName, Category, Product, Color)
VALUES
('My Store', 'Clothing', 'Awesome Shirt', 'White'),
('My Store', 'Clothing', 'Awesome Shirt', 'Green'),
('My Store', 'Clothing', 'Awesome Shirt', 'Blue'),
('My Store', 'Cars', 'Chevy Impala', 'White'),
('My Store', 'Cars', 'Chevy Impala', 'Black'),
('My Store', 'Cars', 'Chevy Impala', 'Grey');

SELECT
d.StoreName,
d.Category,
d.Product,
Colors =
(
SELECT
d2.Color AS [text()]
FROM @Data AS d2
WHERE
d2.StoreName = d.StoreName
AND d2.Category = d.Category
AND d2.Product = d.Product
ORDER BY
d2.Color
FOR XML
PATH (''), TYPE
).value('.[1]', 'varchar(MAX)')
FROM @Data AS d
GROUP BY
d.StoreName,
d.Category,
d.Product
ORDER BY
d.StoreName,
d.Category,
d.Product;





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1421108
Posted Monday, February 18, 2013 6:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:25 PM
Points: 72, Visits: 246
Thank you both, this is very helpful!
Post #1421192
Posted Tuesday, February 19, 2013 12:35 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:07 PM
Points: 9,926, Visits: 11,183
dgowrij (2/19/2013)
Could you please clarify what the below statement actually does?
.value('.[1]', 'varchar(MAX)')

Especially the '.[1]'

The value XML method is documented in Books Online:

http://msdn.microsoft.com/en-us/library/ms178030.aspx

The '.[1]' is an XQuery expression, where the '.' is a path expression containing an axis step and a node test:

http://msdn.microsoft.com/en-us/library/ms190451.aspx

More precisely the '.' is approximate shorthand for the axis step and node test, self::node():

http://msdn.microsoft.com/en-us/library/ms191460.aspx

The [1] is a predicate:

http://msdn.microsoft.com/en-us/library/ms177470.aspx

The XML value method requires a single value as input, hence the technical need for the predicate [1].




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1421456
Posted Wednesday, February 20, 2013 11:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 4:50 PM
Points: 324, Visits: 217
One possible modification that you might want to make is to change this:

d2.Color AS [text()]

to this:

d2.Color AS [data()]

That way your items would have a space between them instead of being run together in a continuous string.



Post #1422235
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse