Get distinct values into single column

  • 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!

  • 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;

  • Thank you both, this is very helpful!

  • 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].

  • 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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply