CODEWITHSUNDEEP
group-byconcatenationsql-server-2014
IMTheNachoMan
IMTheNachoMan

Reputation: 5811

SQL to group one column of a table, select one value from another column, and concatenate the rest

I had asked this question before (sql to group table of product owners with a column for primary owner and concatenated secondary owners) but it was for MySQL and the answer uses group_concat which isn't available on SQL Server 2014. Since that one was answered and for MySQL I thought I should ask a new one for SQL Server 2014.

I have a table of products with their owners. Each owner is in their own row and has an owner type of either primary or secondary. Not every product has a secondary owner.

I need to get a table grouped by product with the primary owner in one column and all the secondary owners concatenated in a second column. If a product has multiple primary owners it should select the first one and the rest go to the secondary owners. If a product doesn't have a primary owner then it should just select the first/any secondary owner.

This is an input table:

+---------+------------+----------+
| Product | Owner Type |  Owner   |
+---------+------------+----------+
| a       | primary    | one      |
| a       | secondary  | two      |
| a       | secondary  | three    |
| b       | primary    | four     |
| b       | secondary  | five     |
| c       | primary    | six      |
| d       | secondary  | seven    |
| e       | secondary  | eight    |
| e       | secondary  | nine     |
| f       | primary    | ten      |
| f       | primary    | eleven   |
| f       | secondary  | twelve   |
| f       | secondary  | thirteen |
+---------+------------+----------+

The expected result is:

+---------+---------------+--------------------------+
| Product | Primary Owner |     Secondary Owners     |
+---------+---------------+--------------------------+
| a       | one           | two, three               |
| b       | four          | five                     |
| c       | six           |                          |
| d       | seven         |                          |
| e       | eight         | nine                     |
| f       | ten           | eleven, twelve, thirteen |
+---------+---------------+--------------------------+

If you notice, products d and e don't have a primary owner so it picks the first secondary owner and then doesn't include it again in the secondary owner column. Similar for product f that has two primary owner.

I know how to group by product and use FOR XML PATH to concatenate rows/fields. In the group I know how to select the first product where Owner Type is primary. What I cannot figure out is the logic that would be needed to pick the first primary owner and exclude it from the secondary owner column and/or select the first secondary owner if there is no primary owner and exclude it from the secondary owner column.

Any ideas?

Upvotes: 0

Views: 38

Answers (1)

Jason
Jason

Reputation: 945

With your sample data, I got it to work like this using ROW_NUMBER() and common table expressions to decide the primary and secondary owners.

WITH PrimaryOwners AS
(SELECT Product,Owner AS 'Primary Owner'
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Product) 'RowNumber',
       Product,OwnerType,Owner
        FROM Product) AS tableA
WHERE RowNumber = 1),

SecondaryOwners AS
(SELECT Product,Owner AS 'Secondary Owners'
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Product) 'RowNumber',
       Product,OwnerType,Owner
        FROM Product) AS tableB
WHERE RowNumber <> 1),

SecondaryOwnersGrouped AS
(SELECT DISTINCT Product,
        STUFF((SELECT CAST(', ' AS VARCHAR(MAX)) + [Secondary Owners]
            FROM SecondaryOwners a
            WHERE a.Product = b.Product
            for xml path('')
            ), 1, 1, '') AS 'Secondary Owners'
FROM SecondaryOwners b)

SELECT p.Product,p.[Primary Owner],
       ISNULL(s.[Secondary Owners],'') 'Secondary Owners'
FROM PrimaryOwners p
     LEFT JOIN SecondaryOwnersGrouped s
        ON p.Product = s.Product

Results:

+----------+----------------+------------------------ --+
| Product  | Primary Owner  | Secondary Owners          |
+----------+----------------+---------------------------+
|   a      |    one         |     two, three            |
|   b      |    four        |        five               |
|   c      |    six         |                           |
|   d      |    seven       |                           |
|   e      |    eight       |        nine               |
|   f      |    ten         | eleven, twelve, thirteen  |
+----------+----------------+---------------------------+

Upvotes: 1

Related Questions