Getting CSV list in a column, keeping a column distinct (SQL)

Question

I need help writing an SQL Query which would get me the results as I want.

I have a document upload feature, in which a document can be upload for clients on multiple locations. The table structure is simple, there are just 5 columns; FileID, FileName, Title, Description and LocationID. If a user uploads a document for 3 locations, 3 entries are entered in to this table. Lets say

FileID FileName    Title      Description      LocationID
1      File1       File1      Description 1    100
2      File1       File1      Description 1    21
4      File1       File1      Description 1    181

A user uploads another file for 4 locations, the data gets entered and the table now looks like this

FileID FileName    Title      Description      LocationID
1      File1.doc   File1      Description 1    100
2      File1.doc   File1      Description 1    21
4      File1.doc   File1      Description 1    181
5      File2.pdf   File2      Description 2    123
6      File2.pdf   File2      Description 2    12
7      File2.pdf   File2      Description 2    126
8      File2.pdf   File2      Description 2    100

Now what i want in the result is

FileName    Title      Description      LocationCSV
File1.doc   File1      Description 1    100, 21, 181
File2.pdf   File2      Description 2    123, 12, 126, 100

Ofcourse, I searched a lot. Tried using COALESCE but didnt help. — Taha Rehman Siddiqui, Dec 14 '12 at 11:25
TODAY there was an [answer to question like yours](http://stackoverflow.com/questions/13874671/return-the-maximum-number-of-num-and-ordersource-concatenated) — valex, Dec 14 '12 at 11:29
@Valex but look at the answer below, could it be more simpler than this? — Taha Rehman Siddiqui, Dec 14 '12 at 12:34

Taryn · Accepted Answer · 2012-12-14T11:40:32.680

In SQL Server you can use FOR XML PATH:

SELECT DISTINCT FileName,
  Title,
  Description,
  STUFF((SELECT ', ' + cast(LocationID as varchar(20))
         from yourtable m
         where p.FileName = m.FileName
         group by LocationID, fileid
         order by fileid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') LocationCSV
from yourtable p

See SQL Fiddle with Demo

Or another version using FOR XML PATH:

SELECT FileName,
  Title,
  Description, LEFT(LocationCSV , LEN(LocationCSV)-1) LocationCSV
FROM yourtable p
CROSS APPLY
(
    SELECT cast(LocationID as varchar(20)) + ','
    FROM yourtable m
    WHERE p.FileName = m.FileName
    FOR XML PATH('')
) m (LocationCSV)
group by FileName, Title, Description, LocationCSV

See SQL Fiddle with Demo

Result:

|  FILENAME | TITLE |   DESCRIPTION |        LOCATIONCSV |
----------------------------------------------------------
| File1.doc | File1 | Description 1 |       100, 21, 181 |
| File2.pdf | File2 | Description 2 |  123, 12, 126, 100 |

score 3 · Answer 2 · answered Dec 14 '12 at 11:25

3

In MySQL you can use GROUP_CONCAT

select FileName,
       Title,
       Description, 
       group_concat(LocationID) as  LocationCSV 
from t group by FileName,Title,Description

answered Dec 14 '12 at 11:25

valex

23,966
7
43
60

+1 didn't know about `group_concat`. Thanks. I'll have to check if SQL Server has something like this. – jimhark Dec 14 '12 at 11:28

score 0 · Answer 3 · edited May 23 '17 at 12:14

0

Take a look at the answer of Bradc. I like this method to concat columns.

Simulating group_concat MySQL function in Microsoft SQL Server 2005?

edited May 23 '17 at 12:14

Community

1
1

answered Dec 14 '12 at 11:39

syed mohsin

2,948
2
23
47

Getting CSV list in a column, keeping a column distinct (SQL)

3 Answers3