How to display SQL result like that

Question

I want to join table Documents(id, name) and Tags(tagId, tagname, docId(FK)). However result will return many redundant rows because one document can has many tags:

Document_name |Tag
----------------------
document01     tag01
document01     tag02
document02     tag01

All I try to resolve is:

document_name |Tags
----------------------------
document01    | tag01, tag02
document02    | tag01

or can be like this:

Document_name |Tag_1   |Tag_2  |Tag_...
---------------------------------------
document01     tag01  |tag02
document02     tag01

Anyone know how can implement this case? Thanks a lot! (I'm trying to find other answer in this site, but I don't know suitable keyword to search in this case)

[This can be helpfull][1] I specially like the one with the STUFF clause [1]: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 — Yaroslav, Jul 05 '12 at 06:23

score 2 · Accepted Answer · answered Jul 05 '12 at 06:14

2

if you are using MySQL you can do this:

select document_name, group_concat(tag SEPARATOR ', ')
from Documents
group by document_name

answered Jul 05 '12 at 06:14

juergen d

201,996
37
293
362

I'm using SQL Server 2008, is it have any function like group_concat()? – Azzurri Jul 05 '12 at 06:16
MSSQL does not have that function. But you can search on stackoverflow for questions like `group_concat for sql-server` and you will find workarounds. – juergen d Jul 05 '12 at 06:17

score 1 · Answer 2 · answered Jul 05 '12 at 06:30

1

For MS-SQL try this

select 
    d1.Document_name, 
    ( 
        select d2.Tag +','
        from Docs d2
        where d2.Document_name = d1.Document_name
        for xml path('')
    ) as Tags
from Docs d1
group by d1.Document_name

answered Jul 05 '12 at 06:30

yogi

19,175
13
62
92

score 0 · Answer 3 · edited Jul 05 '12 at 10:53

0

In MySQL:

SELECT doc.name, GROUP_CONCAT( tag.tagname SEPARATOR ', ') 
       FROM documents AS doc
       JOIN Tags AS tag ON doc.id = tag.docId
       GROUP BY tag.docId

Result:


document_name |Tags
----------------------------
document01    | tag01, tag02
document02    | tag01

edited Jul 05 '12 at 10:53

Nikson Kanti Paul

3,394
1
35
51

answered Jul 05 '12 at 06:50

Suresh K

1

OP said he was using SQL Server 2008. – Jonas T Jul 05 '12 at 06:55

How to display SQL result like that

3 Answers3