Prev: Tom and Plamen...
Next: SQLjobvis 3.0 (free) - Visualize your SQL agent job histories...
From: Pasha on 3 Mar 2010 20:03
Hi All!

I'm trying to create a query that does a real transpose similar to Excel
function - it is slightly different from Pivot/Unpivot functionality of SQL
Server.

Basically, I have a date dimension table and I need to list corresponding
dates, separated by coma for each month. So, for January 2010, my results
would be something like this:
Month Dates
2010-01 01/01/2010, 01/02/2010, 01/03/2010...01/31/2010

What would be a way to achieve this?


Thanks,

Pasha
From: Plamen Ratchev on 3 Mar 2010 22:04
Seems you just need to concatenate the date. Here is method using FOR XML PATH:

SELECT DISTINCT A.[month],
STUFF(C.dates, 1, 1, '') AS dates
FROM Foo AS A
CROSS APPLY(SELECT ',' + CONVERT(VARCHAR(10), B.[date], 101)
FROM Foo AS B
WHERE B.[month] = A.[month]
ORDER BY B.[date]
FOR XML PATH('')) AS C(dates);

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Tom and Plamen...
Next: SQLjobvis 3.0 (free) - Visualize your SQL agent job histories...