InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 19:32:48 GMT20http://www.sqlservercentral.com/Forums/Topic1421033-392-1.aspxOne possible modification that you might want to make is to change this:[code="sql"]d2.Color AS [text()][/code]to this:[code="sql"]d2.Color AS [data()][/code]That way your items would have a space between them instead of being run together in a continuous string.Wed, 20 Feb 2013 11:56:39 GMTbuddy__ahttp://www.sqlservercentral.com/Forums/Topic1421033-392-1.aspx[quote][b]dgowrij (2/19/2013)[/b][hr]Could you please clarify what the below statement actually does?[code="sql"].value('.[1]', 'varchar(MAX)')[/code]Especially the '.[1]'[/quote]The value XML method is documented in Books Online:[url]http://msdn.microsoft.com/en-us/library/ms178030.aspx[/url]The '.[1]' is an XQuery expression, where the '.' is a path expression containing an axis step and a node test:[url]http://msdn.microsoft.com/en-us/library/ms190451.aspx[/url]More precisely the '.' is approximate shorthand for the axis step and node test, self::node():[url]http://msdn.microsoft.com/en-us/library/ms191460.aspx[/url]The [1] is a predicate:[url]http://msdn.microsoft.com/en-us/library/ms177470.aspx[/url]The XML value method requires a single value as input, hence the technical need for the predicate [1].Tue, 19 Feb 2013 00:35:57 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1421033-392-1.aspxThank you both, this is very helpful!Mon, 18 Feb 2013 06:54:09 GMTTrialityhttp://www.sqlservercentral.com/Forums/Topic1421033-392-1.aspx[url=http://sqlfiddle.com/#!3/3d695/44]SQLFiddle[/url] (link) showing the following solution:[code="sql"]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');[/code][code="sql"]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 dGROUP BY d.StoreName, d.Category, d.ProductORDER BY d.StoreName, d.Category, d.Product;[/code]Mon, 18 Feb 2013 02:34:44 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1421033-392-1.aspxtry this link[url]http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx[/url]Mon, 18 Feb 2013 01:05:23 GMTBriPanhttp://www.sqlservercentral.com/Forums/Topic1421033-392-1.aspxHi -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:[b][u]Data:[/u][/b]Store Name | Category | Product | ColorMy Store | Clothing | Awesome Shirt | WhiteMy Store | Clothing | Awesome Shirt | GreenMy Store | Clothing | Awesome Shirt | BlueMy Store | Cars | Chevy Impala | WhiteMy Store | Cars | Chevy Impala | BlackMy Store | Cars | Chevy Impala | Grey[b][u]Desired Result:[/u][/b]My Store | Clothing | Awesome Shirt | WhiteGreenBlueMy Store | Cars | Chevy Impala | WhiteBlackGreyThanks for your help!Sun, 17 Feb 2013 22:53:44 GMTTriality