How to write query using self join and group by?

Question

I have sql server 2008 db table FILE_DETAILS in following format.

ID    FileName    Filesize_in_MB
--------------------------------
1     a.txt        5
2     b.txt        2
3     c.txt        2
3     d.txt        4
4     e.txt        6
4     f.txt        1
4     g.txt        2
5     h.txt        8
6     i.txt        7

now what i want to fetch is as bellow

ID    FileName    Filesize_in_MB
--------------------------------
1     a.txt               5
2     b.txt               2
3     c.txt;d.txt         6
4     e.txt;f.txt;g.txt   9
5     h.txt               8
6     i.txt               7

In above results what happens ID became unique key and FILENAME has get attached and separated by ; and also FILESIZE_IN_MB field in sum of group by ID

I tried with various combination like groupby + self join, also sub queries and all that but i think i missing something.

is it possible to handle this in SQL query?

Thanks in advance

Answer 1

Try this:

SELECT  ID, 
        STUFF(( SELECT ';' + [FileName] 
                FROM FILE_DETAILS 
                WHERE ID = f.ID FOR XML PATH('')), 1, 1, ''), 
        SUM(Filesize_in_MB)
FROM    FILE_DETAILS f
GROUP BY ID

Here's some more information: Concatenate many rows into a single text string?

Answer 2

You should be able to do this using a group by. Aggregating Filesize_IN_MB can be done using sum as aggregator. However, to aggregate FileName you may need to create an AGGREGATE in SQL SERVER 2008R2. This will allow you to use Concatenate as an aggregation function.

select Concatenate(FileName), sum(Filesize_IN_MB) FROM FILE_DETAILS group by ID

There is another way of aggregate concatenation which seems fairly simple but I haven't tried.