Analogue of SUM and GROUP BY for string concatenation

Question

Suppose I have a really long query:

select T.TipoVers As TypeVers,
       sum(T.ImpTot) As N, 
       C.DataCalendario As Date, 
from ( SELECT ... )
group by C.DataCalendario, T.TipoVers

This produce output like:

 TypeVers     N      Date
================================
   Type1      1    2012-09-10
   Type2      47   2012-09-10 
   Type3      5    2012-09-11

I almost done but the final touch will be: Rows with the same date needs to be concatenate (for string value) and summed (for numeric value - right now this is the only part working), i.e.:

   TypeVers       N       Date
====================================
  Type1,Type2     48    2012-09-10
     Type3        5     2012-09-11

I have read round here about XML path. The problem with that solution is great amount of code (I should rewrite my query inside the STUFF clause generating really long query). What alternatives I have?

Pathetically enough, the XML Path trick is the best SQL Server can do for you. — Andomar, May 31 '13 at 11:08
Try this query `select group_concat(typevers), sum(N), date from tbl group by date` — Meherzad, May 31 '13 at 11:08

score 2 · Accepted Answer · answered May 31 '13 at 11:20

If you do not want to write your query again, then I would suggesting using a CTE so you can self-reference it in the STUFF/FOR XML PATH query:

;with cte as
(
  select typeVers, n, date  -- your current query will go here
  from yd 
) 
select STUFF((SELECT DISTINCT ', ' + TypeVers 
              FROM cte t
              WHERE c.Date = t.Date
              FOR XML PATH('')), 1, 1, '') TypeVers,
  sum(n) n,
  date
from cte c
group by date;

See SQL Fiddle with Demo

@bluefeet Is this clause present in MS SQL Server 2005? Why I get "Incorrect syntax near ')'."?? — BAD_SEED, May 31 '13 at 12:44

score 0 · Answer 2 · answered May 31 '13 at 11:28

You don't have to write the long code twice. Write your really long code that gets the result set you want your inner for xml path('') query to work on as a common table expression:

--Creates a common table expression that we can reference more than once in the select.
WITH myLongQueryCTE AS
(
SELECT
ROW_NUMBER() over (order by something) AS SOME_UNIQUE_COLUMN --Put you unique column here or create a new one with row_number
,TipoVers 
,ImpTot
,DataCalendario 
...
GROUP BY DataCalendario
)

SELECT
STUFF((SELECT ', ' + TipoVers FROM myLongQueryCTE AS a WHERE a.SOME_UNIQUE_COLUMN = b.SOME_UNIQUE_COLUMN FOR XML PATH('')),1,1,'') as TypeVers
,SUM(ImpTot) AS N
,DataCalendario AS Date
FROM myLongQueryCTE AS b

Analogue of SUM and GROUP BY for string concatenation

2 Answers2