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?
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 |
+----------+----------------+---------------------------+