CODEWITHSUNDEEP
sqlsql-servert-sql
BAD_SEED
BAD_SEED

Reputation: 5086

Analogue of SUM and GROUP BY for string concatenation

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?

Upvotes: 0

Views: 1415

Answers (2)

David Söderlund
David Söderlund

Reputation: 998

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

Upvotes: 0

Taryn
Taryn

Reputation: 247870

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

Upvotes: 2

Related Questions