Trying to combine a comma separated list from one column with three table joins and get duplicates

Question

Here is an example of my tables:

the details

+----------+---------------+
| txt_item | txt_unique_id |
+----------+---------------+
| Circle   |             1 |
| Square   |             2 |
| Triangle |             3 |
+----------+---------------+

tag_master

+---------+----------+
| txt_tag | opt_type |
+---------+----------+
| red     | color    |
| blue    | color    |
| yellow  | color    |
| large   | size     |
| medium  | size     |
| small   | size     |
+---------+----------+

item_tags

+---------+---------------+
| txt_tag | txt_unique_id |
+---------+---------------+
| red     |             1 |
| blue    |             1 |
| large   |             1 |
| small   |             1 |
| red     |             2 |
| yellow  |             2 |
| small   |             2 |
| medium  |             2 |
| red     |             3 |
| yellow  |             3 |
+---------+---------------+

I want to return this:

+----------+----------------------------+
| Circle   | red, blue, large, small    |
| Square   | red, yellow, small, medium |
| Triangle | red, yellow                |
+----------+----------------------------+

This is what I get:

+----------+---------------------------------------------+
| Circle   | red, red, red, blue, large, small, small    |
| Square   | red, red, red, yellow, yellow, small, small |
| Triangle | red, red, red, yellow, yellow               |
+----------+---------------------------------------------+

This is where I am located:

CREATE TABLE #screening_tags
  (
     txt_unique_id VARCHAR(36),
     tags          VARCHAR(1000)
  )

INSERT INTO #screening_tags
            (txt_unique_id,
             tags)
(SELECT txt_unique_id,
        ( STUFF((SELECT ' , ' + t.txt_tag
                 FROM   item_tags t
                        JOIN tag_master_ tm
                          ON t.txt_tag = tm.txt_tag
                        JOIN items i
                          ON t.txt_unique_id = i.txt_unique_id
                 ORDER  BY opt_type,
                           txt_tage
                 FOR xml path('')), 1, 1, '') )
 FROM   item_tags t)

SELECT *
FROM   #screening_tags

I also tried using COALESCE, but I am missing something. I need DISTINCT or something else, but everything I tried does not work. I cannot use DISTINCT or TOP 1 if I want to ORDER opt_type. Appreciate the help.

Answer 1

Try to delete the connection on tag_master - looking at the expected results, you do not need anything from this table. In fact, you do not need any connections.

    CREATE TABLE #screening_tags
      (
         txt_unique_id VARCHAR(36),
         tags          VARCHAR(1000)
      )

    INSERT INTO #screening_tags
                (txt_unique_id,
                 tags)
    (SELECT txt_unique_id,
            ( STUFF((SELECT ' , ' + t.txt_tag
                     FROM   items i
                     where t.txt_unique_id = i.txt_unique_id
                     ORDER  BY opt_type,
                               txt_tag
                     FOR xml path('')), 1, 1, '') )
     FROM   item_tags t)

    SELECT *
    FROM   #screening_tags

Answer 2

, , Postgres, :

SELECT i.item, string_agg(DISTINCT tm.text_tag,',') AS txt_tag_agg
FROM items i
INNER JOIN item_tags it ON i.txt_unique_id = it.txt_unique_id
INNER JOIN tag_master tm ON it.txt_tag = tm.text_tag
GROUP BY i.item

string_agg , GROUP BY.

Answer 3

, SQL- >= 2017, string_agg().

SQL Fiddle

MS SQL Server 2017:

1:

select i.txt_item , string_agg(it.txt_tag, ',')
from items i
inner join item_tags it
  on i.txt_unique_id = it.txt_unique_id
inner join tag_master tm
  on tm.txt_tag = it.txt_tag
where opt_type = 'color'
group by i.txt_item

:

| txt_item |            |
|----------|------------|
|   Circle |   red,blue |
|   Square | red,yellow |
| Triangle | red,yellow |

Answer 4

, , sql, mysql, sqlite postgresql:

select i.txt_item, 
(select string_agg(txt_tag, ',') 
from item_tags it where i.txt_unique_id=it.txt_unique_id) as txt_tag
from items i

:

txt_item    txt_tag
Circle      red,blue,large,small
Square      red,yellow,small,medium
Triangle    red,yellow

mysql group_agg group_concat (txt_tag). postgres array_agg (txt_tag).

sqlfiddle.com

Answer 5

- STUFF WHERE

CREATE TABLE #screening_tags
  (
     txt_unique_id VARCHAR(36),
     tags          VARCHAR(1000)
  )

INSERT INTO #screening_tags
            (txt_unique_id,
             tags)
(SELECT txt_unique_id,
        ( STUFF((SELECT ', ' + t.txt_tag
                 FROM   item_tags t1
                       WHERE t.txt_unique_id = t2.txt_unique_id
                 FOR xml path('')), 1, 1, '') )
 FROM   item_tags t2)

SELECT *
FROM   #screening_tags

+----------+----------------------------+
| Circle   | red, blue, large, small    |
| Square   | red, yellow, small, medium |
| Triangle | red, yellow                |
+----------+----------------------------+