I'm working with some data that looks like this, in a Customer table (there are other columns too):

There is also a Product table that contains a Product ID and Product Name (among other columns)

I need to write a query, which will form the basis of a view, that essentially substitutes the Product ID's for the Product names, but preserve it as a comma separated list in one column, so the output would look like this:

I know this seems pretty horrible, and I have other tables that separate out each product onto each line using cross_apply, but the customer wants to see the data like this too.

CodePudding user response：

I prepared an example, I hope it satisfies your need

DECLARE @Tbl1 TABLE(ID INT, Value INT)
INSERT INTO @Tbl1 VALUES (1,100),(1,200),(1,300),(1,400)

SELECT  ID
       ,STUFF((SELECT ', '   CAST(Value AS VARCHAR(10)) [text()]
         FROM @Tbl1 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') Value
FROM @Tbl1 t
GROUP BY ID

CodePudding user response：

Given these tables and sample data:

CREATE TABLE dbo.BadIdea(CustomerID int, ProductID varchar(max));

INSERT dbo.BadIdea(CustomerID, ProductID) VALUES
(11111, '256,389'),
(11112, '789,564,468');

CREATE TABLE dbo.Products(ProductID int, Name varchar(32));

INSERT dbo.Products(ProductID, Name) VALUES
(256, 'Apples'),
(389, 'Oranges'),
(468, 'Melon'),
(564, 'Bananas'),
(789, 'Pears');

On SQL Server 2017 , you can use STRING_AGG() against the output of OPENJSON():

;WITH split AS 
(
  SELECT b.CustomerID, j.[key], p.Name
  FROM dbo.BadIdea AS b
  CROSS APPLY OPENJSON('["'   REPLACE(b.ProductID, ',','","')   '"]') AS j
  LEFT OUTER JOIN dbo.Products AS p
  ON j.[value] = p.ProductID
)
SELECT CustomerID, Products = STRING_AGG(Name,',')
  WITHIN GROUP (ORDER BY [key])
  FROM split
  GROUP BY CustomerID;

On SQL Server 2016 , you can still use OPENJSON(), but you need to do the concatenation with FOR XML PATH:

;WITH split AS 
(
  SELECT b.CustomerID, j.[key], p.Name
  FROM dbo.BadIdea AS b
  CROSS APPLY OPENJSON('["'   REPLACE(b.ProductID, ',','","')   '"]') AS j
  LEFT OUTER JOIN dbo.Products AS p
  ON j.[value] = p.ProductID
)
SELECT CustomerID, Products = STUFF((SELECT ','   Name FROM split
  WHERE CustomerID = s.CustomerID
  ORDER BY [key]
  FOR XML PATH, TYPE).value(N'.[1]', 'varchar(max)'), 1, 1, '')
FROM split AS s
GROUP BY CustomerID;

For older versions, you'll need an ordered split function, e.g. this one from Andy Mallon:

CREATE FUNCTION dbo.fn_split 
(
  @Text varchar(8000), 
  @Token varchar(20) = ','
)
RETURNS TABLE
AS
RETURN
SELECT ID, Value
FROM (
    SELECT ID = m.n.value('for $i in . return count(../*[. << $i])   1', 'int')
        , Value = LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)')))
    FROM (
        SELECT CAST('<XMLRoot><RowData>' 
            REPLACE(@Text,@Token,'</RowData><RowData>') 
            '</RowData></XMLRoot>' AS XML) AS x
        )t
    CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
    ) AS R;

Then you can say:

;WITH split AS 
(
  SELECT b.CustomerID, s.ID, p.Name
  FROM dbo.BadIdea AS b
  CROSS APPLY dbo.fn_split(b.ProductID, ',') AS s
  LEFT OUTER JOIN dbo.Products AS p
  ON s.Value = p.ProductID
)
SELECT CustomerID, Products = STUFF((SELECT ','   Name FROM split
  WHERE CustomerID = s.CustomerID
  ORDER BY ID
  FOR XML PATH, TYPE).value(N'.[1]', 'varchar(max)'), 1, 1, '')
FROM split AS s
GROUP BY CustomerID;

• Example db<>fiddle