CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlpivot
Scott
Scott

Reputation: 13931

How do I PIVOT this SQL to achieve the desired result?

I have a stored proc that produces a table like this:

PeriodStart     PeriodEnd     TotalActive     TotalAdded     TotalRemoved
2011-03-01      2011-03-07    123             456            789
2011-03-08      2011-03-14    567             789            123
2011-03-15      2011-03-21    444             555            666

etc...

I need to display the results in a table that looks like this:

          03-01-2011     03-08-2011     03-15-2011
Active    123            567            444
Added     456            789            555
Removed   789            123            666

I've never had to use PIVOT before so I'm a little unsure of how to modify the below SQL in order to achieve the desired result. Here's the sproc:

ALTER PROCEDURE [dbo].[TrendReport]
    @DateStart datetime,
    @DateEnd datetime,
    @Frequency varchar(5)
AS
BEGIN
    SELECT
      PeriodStart,
      PeriodEnd,
      SUM(TotalActive) As TotalActive,
      SUM(TotalAdded) As TotalAdded,
      SUM(TotalRemoved) As TotalRemoved
    FROM (
      SELECT
        PeriodStart = CASE @Frequency
          WHEN 'day'     THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, [Date]))
          WHEN 'week'    THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(DAY, 1 - DATEPART(WEEKDAY, [Date]), [Date])))
          WHEN 'month'   THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(MONTH,   DATEDIFF(MONTH,   0, [Date]), 0)))
          WHEN 'quarter' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, [Date]), 0)))
          WHEN 'year'    THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(YEAR,    DATEDIFF(YEAR,    0, [Date]), 0)))
        END,
        PeriodEnd   = CASE @Frequency
          WHEN 'day'     THEN DATEADD(s, -1, DATEADD(day, 1, DATEDIFF(DAY, 0, [Date])))
          WHEN 'week'    THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, 7 - DATEPART(WEEKDAY, [Date]), [Date]))))
          WHEN 'month'   THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(MONTH,   DATEDIFF(MONTH,   0, [Date]) + 1, 0)))))
          WHEN 'quarter' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, [Date]) + 1, 0)))))
          WHEN 'year'    THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(YEAR,    DATEDIFF(YEAR,    0, [Date]) + 1, 0)))))
        END,
        TotalActive,
        TotalAdded,
        TotalRemoved
      FROM TrendReport P
      WHERE [Date] BETWEEN @DateStart AND @DateEnd
    ) s
    GROUP BY
      PeriodStart,
      PeriodEnd
    ORDER BY PeriodStart

END

Upvotes: 1

Views: 924

Answers (2)

Justin Pihony
Justin Pihony

Reputation: 67085

I do not have an instance of SQL on my machine at the moment, so this may have some problems with syntax. However, give this a try...it is my attempt at the craftiness via dynamic SQL that J Cooper referred to:

CREATE TABLE #Temp1 (ID INT IDENTITY(1,1), PeriodStart DATETIME, PeriodEnd DATETIME, TotalActive INT, TotalAdded INT, TotalRemoved INT)

INSERT INTO #Temp1 (PeriodStart, PeriodEnd, TotalActive, TotalAdded, TotalRemoved)
 SELECT
      PeriodStart,
      PeriodEnd,
      TotalActive,
      TotalAdded,
      TotalRemoved
    FROM (
      SELECT
    PeriodStart = CASE @Frequency
      WHEN 'day'     THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, [Date]))
      WHEN 'week'    THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(DAY, 1 - DATEPART(WEEKDAY, [Date]), [Date])))
      WHEN 'month'   THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(MONTH,   DATEDIFF(MONTH,   0, [Date]), 0)))
      WHEN 'quarter' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, [Date]), 0)))
      WHEN 'year'    THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(YEAR,    DATEDIFF(YEAR,    0, [Date]), 0)))
    END,
    PeriodEnd   = CASE @Frequency
      WHEN 'day'     THEN DATEADD(s, -1, DATEADD(day, 1, DATEDIFF(DAY, 0, [Date])))
      WHEN 'week'    THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, 7 - DATEPART(WEEKDAY, [Date]), [Date]))))
      WHEN 'month'   THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(MONTH,   DATEDIFF(MONTH,   0, [Date]) + 1, 0)))))
      WHEN 'quarter' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, [Date]) + 1, 0)))))
      WHEN 'year'    THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(YEAR,    DATEDIFF(YEAR,    0, [Date]) + 1, 0)))))
    END,
    TotalActive,
    TotalAdded,
    TotalRemoved
      FROM TrendReport P
      WHERE [Date] BETWEEN @DateStart AND @DateEnd
    ) s
        GROUP BY
      PeriodStart,
      PeriodEnd
    ORDER BY PeriodStart

DECLARE @CreatePivot VARCHAR(MAX), @PivotColumns VARCHAR(MAX)
SET @CreatePivot = 'CREATE TABLE #Temp2 (BaseColumn VARCHAR(25)'
DECLARE @Count INT, @CurrentDateBeingChecked DATETIME
SET @Count = 1
WHILE(SELECT COUNT(*) FROM #Temp1 WHERE ID >= @Count) > 0
BEGIN
    SELECT @CurrentDateBeingChecked = PeriodStart FROM #Temp1 WHERE ID = @Count
    IF @Count > 1
        SET @PivotColumns = @PivotColumns + ','
    SET @PivotColumns = @PivotColumns + '[' + CAST(@CurrentDateBeingChecked AS VARCHAR(15) + ']'
    SET @CreatePivot = @CreatePivot + ', [' + CAST(@CurrentDateBeingChecked AS VARCHAR(15) + '] INT' 
    SET @Count = @Count + 1
END

SET @CreatePivot = @CreatePivot + ')'

sp_executesql(@CreatePivot)

DECLARE @PivotStatementToRun VARCHAR(MAX)
SET @PivotStatementToRun = 
'
INSERT INTO #Temp2
SELECT 'Active' AS BaseColumn,
' + @PivotColumns + '
FROM
(SELECT PeriodStart, TotalActive
 FROM #Temp1) AS SourceTable
PIVOT
(
    SUM(TotalActive)
    FOR PeriodStart IN (
    ' + @PivotColumns + ')
) AS PivotTable;
'
sp_executesql(@PivotStatementToRun)


SET @PivotStatementToRun = 
'
INSERT INTO #Temp2
SELECT 'Added' AS BaseColumn,
' + @PivotColumns + '
FROM
(SELECT PeriodStart, TotalAdded
 FROM #Temp1) AS SourceTable
PIVOT
(
    SUM(TotalAdded)
    FOR PeriodStart IN (
    ' + @PivotColumns + ')
) AS PivotTable;
'
sp_executesql(@PivotStatementToRun)

SET @PivotStatementToRun = 
'
INSERT INTO #Temp2
SELECT 'Removed' AS BaseColumn,
' + @PivotColumns + '
FROM
(SELECT PeriodStart, TotalRemoved
 FROM #Temp1) AS SourceTable
PIVOT
(
    SUM(TotalRemoved)
    FOR PeriodStart IN (
    ' + @PivotColumns + ')
) AS PivotTable;
'
sp_executesql(@PivotStatementToRun)

SELECT * FROM #Temp2

Upvotes: 1

J Cooper
J Cooper

Reputation: 4998

You actually need to first apply UNPIVOT to rotate the three quantity types from columns to rows and then apply PIVOT to rotate the period start date to columns and aggregate the quantities.

Here's the thing with using the PIVOT operator though, you need to know what columns you will have ahead of time (03-01-2011, 03-08-2011, 03-15-2011), it will need hard coded into the query (unless you want to get down and dirty with dynamic sql, which i would read up on before using in production)

Here is the solution:

WITH CTE AS (
    SELECT
      PeriodStart,
      PeriodEnd,
      SUM(TotalActive) As TotalActive,
      SUM(TotalAdded) As TotalAdded,
      SUM(TotalRemoved) As TotalRemoved
    FROM (
      SELECT
        PeriodStart = CASE @Frequency
          WHEN 'day'     THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, [Date]))
          WHEN 'week'    THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(DAY, 1 - DATEPART(WEEKDAY, [Date]), [Date])))
          WHEN 'month'   THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(MONTH,   DATEDIFF(MONTH,   0, [Date]), 0)))
          WHEN 'quarter' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, [Date]), 0)))
          WHEN 'year'    THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(YEAR,    DATEDIFF(YEAR,    0, [Date]), 0)))
        END,
        PeriodEnd   = CASE @Frequency
          WHEN 'day'     THEN DATEADD(s, -1, DATEADD(day, 1, DATEDIFF(DAY, 0, [Date])))
          WHEN 'week'    THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, 7 - DATEPART(WEEKDAY, [Date]), [Date]))))
          WHEN 'month'   THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(MONTH,   DATEDIFF(MONTH,   0, [Date]) + 1, 0)))))
          WHEN 'quarter' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, [Date]) + 1, 0)))))
          WHEN 'year'    THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(YEAR,    DATEDIFF(YEAR,    0, [Date]) + 1, 0)))))
        END,
        TotalActive,
        TotalAdded,
        TotalRemoved
      FROM TrendReport P
      WHERE [Date] BETWEEN @DateStart AND @DateEnd
    ) s
    GROUP BY
      PeriodStart,
      PeriodEnd
    ORDER BY PeriodStart

), U AS (
    SELECT qty_type, PeriodStart, qty
    FROM CTE
    UNPIVOT(qty FOR qty_type IN([TotalActive], [TotalAdded], [TotalRemoved]))Q
)     
SELECT * FROM U 
PIVOT (SUM(Qty) FOR PeriodStart IN([03/01/2011], [03/08/2011], [03/15/2011])) AS P

Upvotes: 1

Related Questions