SQL Concatenate String By Category in One Line

Question

I have some tables in SQL Server 2005:

• Product: ID, Name
• Category: ID, Name
• Tags: ID, tagName
• ProductCategory: productId, tagId
• CategoryTags: categoryId, tagId

Basically I need a query that will display the category and list of tags associated for each product on ONE line.

For example, there are 3 categories: Animals, Countries, Color.

Each one of those have a bunch of tags.

Lets say that Product 1 has a bunch of tags associated such as: bird, duck, dog, canada, russia, japan, black, red, white

I need the query result to be in the format:

productId, [Category:tag,tag,tag;Category:tag,tag,tag:Category:tag,tag,tag]

1, [Animal:bird,duck,dog;Country:canada,russia,japan;Color:black,red,white]

The text in the square brackets should be in one column returned by SQL.

I found something similar here: Concatenate many rows into a single text string?

But I need to take it a step further and have it all on one line instead of it returning the different categories on separate rows.

Kinda hard to explain but hope you get it.

Is this possible?

Thanks in advance.

UPDATE: Thanks for everyone's help and input. Really appreciate it! Here is what I have so far which is close, but not quite there yet. Perhaps it'll help you figure it out for me :D

http://sqlfiddle.com/#!3/eed14/5

Answer 1

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table Product
(
  ID int, 
  Name varchar(10)
)

create table Category
(
  ID int, 
  Name varchar(10)
)

create table Tags
(
  ID int, 
  Name varchar(10)
)

create table ProductCategory
(
  productId int,
  categoryId int
)

create table CategoryTags
(
  categoryId int, 
  tagId int
)

insert into Product values(1, 'Product 1')

insert into Category values(1, 'Animals')
insert into Category values(2, 'Countries')
insert into Category values(3, 'Color')

insert into Tags values(1, 'Bird') 
insert into Tags values(2, 'Duck') 
insert into Tags values(3, 'Dog') 
insert into Tags values(4, 'Candada') 
insert into Tags values(5, 'Russia') 
insert into Tags values(6, 'Japan') 
insert into Tags values(7, 'Black') 
insert into Tags values(8, 'Red') 
insert into Tags values(9, 'White') 

insert into ProductCategory values(1, 1)
insert into ProductCategory values(1, 2)
insert into ProductCategory values(1, 3)

insert into CategoryTags values(1, 1)
insert into CategoryTags values(1, 2)
insert into CategoryTags values(1, 3)
insert into CategoryTags values(2, 4)
insert into CategoryTags values(2, 5)
insert into CategoryTags values(2, 6)
insert into CategoryTags values(3, 7)
insert into CategoryTags values(3, 8)
insert into CategoryTags values(3, 9)

Query 1:

select P.ID,
       P.Name,
       (
       select ';'+C.Name+':'+
              (
              select ','+T.Name
              from CategoryTags as CT
                inner join Tags as T
                  on CT.tagId = T.ID
              where CT.categoryId = C.ID
              for xml path(''), type
              ).value('substring(text()[1], 2)', 'varchar(max)') 
       from ProductCategory as PC
         inner join Category as C
           on PC.categoryId = C.ID
       where PC.productId = P.ID
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as ColumnName
from Product as P

Results:

| ID |      NAME |                                                                 COLUMNNAME |
-----------------------------------------------------------------------------------------------
|  1 | Product 1 | Animals:Bird,Duck,Dog;Countries:Candada,Russia,Japan;Color:Black,Red,White |

Answer 2

Just to give a sample try this please: Well this is done in MYSQL with it's easiest group_concat function...though. Coz you haven't mentioned which RDBMS exactly you are using.

select x.productid, group_concat(c.name), 
group_concat(x.tags)
from (
select p.productid, ct.categoryid,
group_concat(t.tagname) as tags
from productcategory p
left join tags t
on t.id = p.tagid
left join  
categorytags ct
on t.id = ct.tagid
group by p.productid, ct.categoryid) x
left join category c
on c.id = x.categoryid
group by x.productid
;

Results:

PRODUCTID   GROUP_CONCAT(C.NAME)    GROUP_CONCAT(X.TAGS)
1       cat1,cat2                   tag2,tag1,tag1
2       cat1                        tag2

SQLFIDDLE DEMO

Answer 3

This is a really ugly way to get this data into a single string. But you can use something like this:

select distinct 
  p.name ProductName,
  STUFF((SELECT distinct ', ' + 
           c.name +':'+ STUFF((SELECT DISTINCT ', ' + Name 
                               FROM tags t
                               LEFT JOIN CategoryTags ct
                                  on t.id = ct.tagid
                               WHERE c.id = ct.categoryid
                               FOR XML PATH('')), 1, 1, '')
         FROM category c 
         LEFT JOIN CategoryTags ct
           ON ct.categoryid = c.id
         LEFT JOIN productcategory pc
           ON pc.tagid = ct.tagid
         WHERE p.id = pc.productid
         FOR XML PATH('')), 1, 1, '')  List
from product p

See SQL Fiddle with Demo

The result is:

| PRODUCTNAME |                                                                               LIST |
----------------------------------------------------------------------------------------------------
|        Test |  Animal: Bird, dog, duck, Color: black, red, white, Country: canada, japan, russia |

Edit #1: This is producing the result that you want:

select distinct 
  p.name ProductName,
  STUFF((SELECT distinct '; ' + 
           c.name +':'+ STUFF((SELECT DISTINCT ', ' + Name 
                               FROM catalogTags t
                               LEFT JOIN catalogProductTags pt
                                 on t.id = pt.catalogTagId
                               LEFT JOIN catalogCategoryTags ct
                                 on pt.catalogTagId = ct.catalogTagId
                               WHERE c.id = ct.catalogCategoryId
                                  AND p.id = pt.catalogProductId
                               FOR XML PATH('')), 1, 1, '')
         FROM catalogProductTags pt
         LEFT JOIN catalogCategoryTags ct
           ON pt.catalogTagId = ct.catalogTagId
         LEFT JOIN catalogCategory c 
           ON ct.catalogCategoryId = c.id
         WHERE p.id = pt.catalogProductId
         FOR XML PATH('')), 1, 1, '')  List
from catalogProduct p;

See SQL Fiddle with Demo. This can probably be refactored to a cleaner version.

The result is:

| PRODUCTNAME |                                                        LIST |
-----------------------------------------------------------------------------
|     tshirt1 |                           Animals: dog; Color: black, white |
|     tshirt2 |              Animals: dog; Color: blue, red; Countries: USA |
|     tshirt3 |  Color: blue, pink, red, white; Countries: Australia, Japan |