Amedo Amedo - 7 months ago 10
SQL Question

Pivot table with more then one record

I have data that is structured as follow:

LocationId, GroupId, DayOfWeek, Count, DatetimeValue15Min
2 9 4 5 2014-01-02 08:15:00.000
2 9 4 5 2014-01-02 09:15:00.000


I want to calculate the mode for each day, the data above already contains the count to know the mode. I have written a query with a pivot.

SELECT
pvt.LocationId, pvt.GroupId, [1], [2], [3], [4],[5]
FROM
@TempResult
PIVOT
(min ([DatetimeValue15Min])
FOR DayOfWeek IN ( [1], [2], [3], [4],[5])) AS pvt


In this case I have two modes but i want to show them both. My query returns in this case just the mode with the minimum value. I know that I can make a second query with the max value but what if i have more than two modes?

The output should be like:

LocationId GroupId 1 2 3 4 5
2 9 08:15, 09:15


I am using SQL Server 2005.

Answer

You are almost there. You just need to build the comma-separated list. A little xml type abuse works really well for that.

;WITH
t1 AS ( --Add a grouping id for quick reference
  SELECT
    [LocationId],[GroupId],[DayOfWeek],[DatetimeValue15Min],
    DENSE_RANK() OVER(ORDER BY [LocationId],[GroupId],[DayOfWeek]) [i]
  FROM @TempResult
),
t2 AS ( --Build a comma-separated list of all [DatetimeValue15Min] with same grouping id
  SELECT [LocationId],[GroupId],[DayOfWeek],
    CAST(REPLACE((SELECT CONVERT(time, [DatetimeValue15Min]) AS a FROM t1 WHERE [i] = t.[i] FOR xml PATH('')),'</a><a>',',') AS xml).value('a[1]','varchar(max)') [dtv_list]
  FROM t1 t
)
SELECT pvt.LocationId, pvt.GroupId, [1], [2], [3], [4],[5]
                FROM t2

                PIVOT
                (
                    min ([dtv_list])
                    FOR DayOfWeek IN ( [1], [2], [3], [4],[5])
                ) AS pvt

The xml trick works like this:

  • SELECT [DatetimeValue15Min] FOR XML -> <a>08:15</a><a>09:15</a>
  • replace '</a><a>' with ',' -> <a>08:15,09:15</a>
  • extract first node from xml -> '08:15,09:15'