CODEWITHSUNDEEP
sqlsql-server-2008pivot
bobo
bobo

Reputation: 33

SQL Static and Dynamic Pivot not working

I would like to perform this query for all unique entries in a column. (Creating a basic pivot summary) (There are 160 unique values)

   SELECT CAST(Log_Local_Stamp as date) AS ForDate,
   DATEPART(hour,Log_Local_Stamp) AS OnHour,
   DATEPART(minute,Log_Local_Stamp) AS OnMinute,
   DATEPART(second,Log_Local_Stamp) AS OnSecond,
   COUNT(*) AS Totals
   FROM MainView

   GROUP BY CAST(Log_Local_Stamp as date),
   DATEPART(hour,Log_Local_Stamp),
   DATEPART(minute,Log_Local_Stamp),
   DATEPART(second,Log_Local_Stamp) ORDER BY ForDate

Now there are 2 ways to do this a static pivot, and a dynamic pivot. I used some code to generate the static pivot call, however both calls fail.

    SELECT *
    from 
    (
    SELECT 
    CAST(Log_Local_Stamp as date) AS ForDate,
    DATEPART(hour,Log_Local_Stamp) AS OnHour,
    DATEPART(minute,Log_Local_Stamp) AS OnMinute,
    DATEPART(second,Log_Local_Stamp) AS OnSecond,
    COUNT(*) AS Totals
    FROM MainView

    WHERE DATEPART(hour,Log_Local_Stamp) BETWEEN 10 and 13 and               CAST(Log_Local_Stamp as date) = '2015-12-09'

   GROUP BY CAST(Log_Local_Stamp as date),
   DATEPART(hour,Log_Local_Stamp),
   DATEPART(minute,Log_Local_Stamp),
   DATEPART(second,Log_Local_Stamp) ) as ps

   Pivot
   (
   sum(Totals) FOR MainView.Log_Machine_Name IN (Properly formatted list    here)
    ) as piv

The error received here is "The column prefix 'MainView' does not match with a table name or alias name used in the query.", at the second last line.Not sure why that is the case.

My second approach was to use a dynamic pivot 

     DECLARE @cols as NVARCHAR(MAX),
     @query AS NVARCHAR(MAX)

     select @cols = STUFF((SELECT distinct ',' 
     + QUOTENAME(Ltrim(rtrim(Log_Machine_Name))) 
                from MainView
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

    set @query = 
    'SELECT CAST(Log_Local_Stamp as date) AS ForDate,
    DATEPART(hour,Log_Local_Stamp) AS OnHour,
    DATEPART(minute,Log_Local_Stamp) AS OnMinute,
    DATEPART(second,Log_Local_Stamp) AS OnSecond,
    COUNT(*) AS Totals ' + @cols + '
    FROM (MainView
    GROUP BY CAST(Log_Local_Stamp as date),
    DATEPART(hour,Log_Local_Stamp),
    DATEPART(minute,Log_Local_Stamp),
   DATEPART(second,Log_Local_Stamp) ORDER BY ForDate ) x
   pivot
   (
    SUM(COUNT(*)) for Log_Machine_Name in (' + @cols + ')
    ) p '
execute(@query)

However it errors out at "Incorrect syntax near 'Data from Log_Machine_Name' in line 5. Not quite sure how trouble shoot this scenario.

Thanks

Upvotes: 3

Views: 96

Answers (1)

prenesh u
prenesh u

Reputation: 122

STATIC QUERY : You are referencing the column within the sub-query, but the alias you are using is for the table itself and not for the instance within the sub-query, this might help.

SELECT *
from 
(
SELECT 
CAST(Log_Local_Stamp as date) AS ForDate,
DATEPART(hour,Log_Local_Stamp) AS OnHour,
DATEPART(minute,Log_Local_Stamp) AS OnMinute,
DATEPART(second,Log_Local_Stamp) AS OnSecond,
COUNT(*) AS Totals
FROM MainView

WHERE DATEPART(hour,Log_Local_Stamp) BETWEEN 10 and 13 and
CAST(Log_Local_Stamp as date) = '2015-12-09'

GROUP BY CAST(Log_Local_Stamp as date),
DATEPART(hour,Log_Local_Stamp),
   DATEPART(minute,Log_Local_Stamp),
   DATEPART(second,Log_Local_Stamp) ) as ps

   Pivot
   (
   sum(Totals) FOR ps.Log_Machine_Name IN (Properly formatted list    here)
    ) as piv

Upvotes: 1

Related Questions