This question already has an answer here:
-
Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
5 answers
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?
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
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
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
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)
