Select statement that combines rows in a subquery (pivot)

Question

I have:

tbl_product:
------------
product_id
name
description

tbl_user:
------------
user_id
first_name
last_name
initials

Multiple users can own a single product and I represent that by creating a table:

xref_product_user: 
product_id
user_id

that make up a composite primary key where each column is a foreign_key to their respective tables.

Since each product can have multiple users, I need to write a select statement that contains

product name, description, combined user initials (comma separated string).

So lets say I have a product chocolate that are owned by user mike johnson and dan williams. Well my results should be

NAME        DESCRIPTION    INTIALS
chocolate   candy          mj, dw

I can't seem to figure out how to write this select statement because of the initials part. Anyone have any ideas?

@JeffO Indeed, and for the initial part the op could use something like `substring(first_name, 1, 1) + initials + substring(last_name, 1, 1)`, depending on what the initials field contains. — jpw, Feb 07 '14 at 18:57

score 1 · Accepted Answer · answered Feb 07 '14 at 18:54

A Function would probably be a good, easily maintainable way to handle that:

CREATE FUNCTION [dbo].[fn_GetInitialsForProduct]
(
    @product_id
)
RETURNS varchar(200)
AS
BEGIN
    declare @Initials varchar(200)

    set @Initials = ''

    select @Initials=@Initials + ', ' + isnull(u.Initials, '')
    from dbo.tbl_user u
    inner join dbo.xref_product_user x
    on u.user_id = x.user_id
    where x.product_id = @product_id
    order by u.Initials

    if left(@Initials, 2) = ', '
        set @Initials = substring(@Initials, 3, len(@Initials) - 2)

    return @Initials
END

--AND HERE'S HOW TO CALL IT

select p.name, p.description, dbo.GetInitialsForProduct(p.product_id) as Initials
from tbl_product p

I went with the function. Function made it super easy. Thanks! — RiceRiceBaby, Feb 07 '14 at 20:01

Guy Nethery · Answer 2 · 2014-02-07T19:24:33.620

How about this? No need for a stored procedure.

    create table #product (product_id int, name nvarchar(50), description nvarchar(50))
create table #user(user_id int, initials nvarchar(50))
Create table #xref_product_user (product_id int, user_id int)

insert into #product values (1,'chocolate','candy')
insert into #product values (2,'shrimp','seafood')
insert into #user values (1, 'mj')
insert into #user values (2, 'dw')
insert into #xref_product_user values (1,1)
insert into #xref_product_user values (1,2)
insert into #xref_product_user values (2,2)


SELECT p.*,
    STUFF((
            SELECT ', ' + initials
            FROM #user u
            INNER JOIN #xref_product_user x ON x.user_id = u.user_id AND x.product_id = p.product_id
            FOR XML path('')
            ), 1, 2, '') [INTIALS]
FROM #product p




drop table #product, #user, #xref_product_user

M.Ali · Answer 3 · 2014-02-07T19:41:29.137

Test Data

DECLARE @tbl_product TABLE(product_id INT,name VARCHAR(10),[description] VARCHAR(20))
INSERT INTO @tbl_product VALUES
(1,'Name 1','description 1'),(2,'Name 2','description 2'),(3,'Name 3','description 3')

DECLARE @tbl_user TABLE([user_id] INT,first_name VARCHAR(10)
                       ,last_name VARCHAR(10),initials VARCHAR(10))
INSERT INTO @tbl_user VALUES
(1,'f_name 1','l_name 1','i1'),(2,'f_name 2','l_name 2','i2'),
(3,'f_name 3','l_name 3','i3')

DECLARE @xref_product_user TABLE(product_id INT,[user_id] INT)
INSERT INTO @xref_product_user VALUES
(1, 1), (1, 2), (1, 3),
(2, 1), (2, 2),
(3, 3)

Query 1

SELECT  P.Name 
       ,P.[description]
       ,STUFF((SELECT ', ' + U.initials
               FROM @tbl_user u INNER JOIN @xref_product_user UP
               ON U.[user_id] = UP.[user_id]
               WHERE UP.product_id = P.product_id
               FOR XML PATH(''), TYPE)
               .value('.','NVARCHAR(MAX)'),1,2,'') AS InitialsList
FROM @tbl_product P

Query 2

using CROSS APPLY

SELECT DISTINCT  
        P.Name 
       ,P.[description]
       ,STUFF(L.Initials, 1, 2,'') AS Initials
FROM @tbl_product P
         CROSS APPLY
            (
             SELECT ', ' + U.initials [text()]
             FROM @tbl_user u INNER JOIN @xref_product_user UP
             ON U.[user_id] = UP.[user_id]
             WHERE UP.product_id = P.product_id
             FOR XML PATH('')
             )L(Initials)

Result Set

╔════════╦═══════════════╦══════════════╗
║  Name  ║  description  ║ InitialsList ║
╠════════╬═══════════════╬══════════════╣
║ Name 1 ║ description 1 ║ i1, i2, i3   ║
║ Name 2 ║ description 2 ║ i1, i2       ║
║ Name 3 ║ description 3 ║ i3           ║
╚════════╩═══════════════╩══════════════╝

See here SQL Fiddle

score 0 · Answer 4 · answered Feb 07 '14 at 19:41

Here you go.

Working SQL Fiddle: http://sqlfiddle.com/#!3/8793d/3

The SQL Statement

SELECT  p.name 
       ,p.[description]
       ,Initials = 
          STUFF(
            (  SELECT ', ' + u.initials
               FROM tbl_user u INNER JOIN xref_product_user pux
                   ON u.user_id = pux.user_id
                   AND pux.product_id = p.product_id
               FOR XML PATH(''), TYPE)
               .value('.','NVARCHAR(MAX)'),1,2,'')
FROM tbl_product p

Results from Sample Data

NAME            DESCRIPTION             INITIALS
Twinkies        Sugary Snack            fm
Chocolate       Candy                   mj, dw
Glazed Donuts   High calorie Breakfast  (null)

Select statement that combines rows in a subquery (pivot)

4 Answers4

Working SQL Fiddle: http://sqlfiddle.com/#!3/8793d/3

The SQL Statement

Results from Sample Data

Linked