Query result from array string with numbers to array string text?

Question

I have two SQL Server tables.

First table: Product

Id	Title	Tags
1	P1583	2,5
2	P1234	1,3
3	P1456	1

Second table - Tag:

Id	Title
1	Pants
2	Shorts
3	Shirts
4	Tshirts
5	Skirts

I want this result

ProductId	Title	TagName
1	P1583	Short,Skirts
2	P1234	Pants,Shirts
3	P1456	Pants

How can I write the query?

SELECT
    P.Id AS ProductId,
    P.Title AS Title,
    ????
FROM 
    Product P
LEFT JOIN 
    Tag T ON T.Id = P.Tags  --- (this is not working)

Your first step is to split the CSV strings you have stored in your string column. Such splitting is the most common question on SO - try a simple search. If not too late, normalize your table properly which will remove the first part of your problem. — SMor, Feb 06 '22 at 19:19
This is very similar to a question asked two days ago [here](https://stackoverflow.com/questions/70989558/how-to-get-values-from-table-using-multiple-ids-in-single-column-in-sql). Whether that answer also applies here depends on which version of sql-server you are using (and whether the STRING_SPLIT function is supported). — T N, Feb 06 '22 at 19:34
I would personally recommend against `STRING_SPLIT` and just get the design fixed. It's severely flawed. You can't enforce the integrity of the database, you can't use efficient `JOIN`s or `WHERE` clauses, and simple queries are much more complex. Fix your design and this entire problem goes away. — Thom A, Feb 06 '22 at 19:51
You should really have a separate table `ProductTag` with two columns `ProductId` and `TagId` — Charlieface, Feb 06 '22 at 20:19

score 1 · Answer 1 · answered Feb 06 '22 at 20:00

So, creating the data:

CREATE TABLE #t (id int, title nvarchar(5), tags nvarchar(3));

INSERT INTO #t (id,title,tags)
VALUES
(1,'P1583','2,5'),
(2,'P1234','1,3'),
(3,'P1456','1');

CREATE TABLE #tag (id int, title nvarchar(10));

INSERT INTO #tag (id,title)
VALUES
(1,'Pants'),
(2,'Shorts'),
(3,'Shirts'),
(4,'Tshirts'),
(5,'Skirts');

And using STRING_SPLIT and CROSS APPLY gives us:

SELECT #t.id, #t.title, #t.tags, tags.value, #tag.title
FROM #t
CROSS APPLY STRING_SPLIT(#t.tags,',') tags
INNER JOIN #tag ON tags.value = #tag.id;

id	title	tags	value	title
1	P1583	2,5	2	Shorts
1	P1583	2,5	5	Skirts
2	P1234	1,3	1	Pants
2	P1234	1,3	3	Shirts
3	P1456	1	1	Pants

From there you just STRING_AGG back together:

WITH exp
AS (
SELECT #t.id, #t.title, #t.tags, tags.value, #tag.title AS tag_title
FROM #t
CROSS APPLY STRING_SPLIT(#t.tags,',') tags
INNER JOIN #tag ON tags.value = #tag.id
)
SELECT id, title, STRING_AGG(tag_title,',') WITHIN GROUP (ORDER BY tag_title) AS tag_titles
FROM exp
GROUP BY id, title;

score 1 · Accepted Answer · answered Feb 06 '22 at 20:26

An alternative way to do the split and re-aggregate is a modification of @FlexYourData's excellent answer:

SELECT
  p.Id,
  p.Title,
  tags.TagName
FROM Product p
CROSS APPLY (
    SELECT
        TagName = STRING_AGG(t.Title, ',')
    FROM STRING_SPLIT(p.Tags, ',') tags
    INNER JOIN Tag t ON tags.value = t.id
) tags;

Ideally, you should normalize your schema and create a separate join table:

CREATE TABLE ProductTag (
    ProductId REFERENCES Product (Id),
    TagId REFERENCES Tag (Id)
);

INSERT ProductTag (ProductId, TagId)
SELECT
  p.Id,
  tags.value
FROM Product p
CROSS APPLY STRING_SPLIT(p.Tags, ',') tags;

Then your query becomes:

SELECT
  p.Id,
  p.Title,
  tags.TagName
FROM Product p
CROSS APPLY (
    SELECT
        TagName = STRING_AGG(t.Title, ',')
    FROM ProductTags pt
    INNER JOIN Tag t ON pt.TagId = t.id
    WHERE pt.ProductId = p.Id
) tags;

Thank you so much, first way is succesfully worked for me. – Volkan Karayel Feb 07 '22 at 22:13 — Volkan Karayel, Feb 07 '22 at 22:13

RF1991 · Answer 3 · 2022-02-06T20:08:19.473

your table

declare @r TABLE (
   Id    int  NOT NULL 
  ,Title VARCHAR(10) NOT NULL
  ,Tags  VARCHAR(30) NOT NULL
);
INSERT INTO @r(Id,Title,Tags) VALUES (1,'P1583','2,5');
INSERT INTO @r(Id,Title,Tags) VALUES (2,'P1234','1,3');
INSERT INTO @r(Id,Title,Tags) VALUES (3,'P1456','1');

declare @t TABLE (
   Id    int  NOT NULL PRIMARY KEY 
  ,Title VARCHAR(70) NOT NULL
);
INSERT INTO @t(Id,Title) VALUES (1,'Pants');
INSERT INTO @t(Id,Title) VALUES (2,'Shorts');
INSERT INTO @t(Id,Title) VALUES (3,'Shirts');
INSERT INTO @t(Id,Title) VALUES (4,'Tshirts');
INSERT INTO @t(Id,Title) VALUES (5,'Skirts');

query to produce result the tag should be splitted and merge again with the title of second table

with t as(
SELECT Id,Title,
    PARSENAME(REPLACE(Tags,',','.'),2) 'Tag1' ,
    PARSENAME(REPLACE(Tags,',','.'),1) 'Tag2'
FROM @r)
select t.id,t.Title,concat(t1.Title,iif(t1.Title is null,'',','),t2.Title) 
tagName from t
full join @t t1 on t1.id=t.Tag1
join @t t2 on t2.id=t.Tag2

Query result from array string with numbers to array string text?

3 Answers3