CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Ugur Yaman
Ugur Yaman

Reputation: 11

SQL Dynamic Pivot Group By

I'm not able to group by the code in my dynamic pivot. I need to group by the code area which is defined in ZONE table as shown below. I need to show the distinct code with group by. Some of the code has more than one zone_id, therefore, I need to merge them. I am using SQL 2008.

ZONE_ID SITE_ID     ZONE_NAME             CODE
197      103        ADANA CADDE CAM2    M01001
180      103        ADANA CADDE CAM1    M01001

Results

Declare @Date1 date = GetDate()


IF OBJECT_ID('tempdb..#Dates') IS NOT NULL
drop table #Dates


SELECT DISTINCT CONVERT(VARCHAR,START_TIME,106) AS [Date]
INTO #Dates
FROM dbo.DIRECTIONAL_METRIC
WHERE CAST(START_TIME as DATE) >=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Date1)-1),@Date1),101)
and CAST(START_TIME as DATE) <= @Date1
AND DATEPART(HOUR,START_TIME) BETWEEN 9 AND 23
order by [Date]

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(convert(varchar,[Date],106)) 
                                   From   #Dates 
                                   Order by 1 For XML Path('')
                                  ),1,1,'') 

Select  @SQL = N'

Declare @Date1 DATE
SELECT @Date1 = GetDate()


SELECT [CODE],' + @SQL + ',[Grand Total Enter],[Grand Total Exit] 
FROM  
(
        SELECT [CODE]
              ,[Date] = CONVERT(VARCHAR, START_TIME, 106)
              ,[NUM_TO_ENTER] 
              ,[Grand Total Enter] = (SELECT SUM(NUM_TO_ENTER) FROM DIRECTIONAL_METRIC WHERE Zone_ID=A.Zone_ID AND
                            CAST(START_TIME AS DATE) >='''+CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Date1)-1),@Date1),101)+'''AND CAST(START_TIME AS DATE) <='''+cast(@Date1 as varchar(10))+'''
                            AND DATEPART(HOUR,START_TIME)BETWEEN 9 AND 23)
              ,[Grand Total Exit] =(SELECT SUM(NUM_TO_EXIT)  FROM DIRECTIONAL_METRIC Where Zone_ID=A.Zone_ID AND 
                            CAST(START_TIME AS DATE) >='''+CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Date1)-1),@Date1),101)+''' AND CAST(START_TIME AS DATE) <='''+cast(@Date1 as varchar(10))+'''
                            AND DATEPART(HOUR,START_TIME)BETWEEN 9 AND 23)

FROM    DIRECTIONAL_METRIC A INNER JOIN ZONE Z ON Z.ZONE_ID = A.ZONE_ID
        WHERE CAST(START_TIME as DATE) >=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Date1)-1),@Date1),101)
        and CAST(START_TIME as DATE) <= @Date1
        AND DATEPART(HOUR,START_TIME)BETWEEN 9 AND 23
        AND Z.CODE IS NOT NULL
) S
PIVOT 
(
    SUM([NUM_TO_ENTER]) FOR 
    [Date] IN (' + @SQL + ') 
) AS Pvt GROUP BY [CODE] '

Exec(@SQL);

Upvotes: 0

Views: 960

Answers (1)

Deadsheep39
Deadsheep39

Reputation: 611

Problem is in your derivated query. In you selection is not all column which are in derived table. Group by do sql server automaticaly (it's main think of pivot).

Inside of your pivot is select:

SELECT 
    [CODE]
    ,[Date] = CONVERT(VARCHAR, START_TIME, 106)
    ,[NUM_TO_ENTER] 
    ,[Grand Total Enter] = (select ...)
    ,[Grand Total Exit] = (select ...)
FROM ...
PIVOT (SUM([NUM_TO_ENTER]) FOR [Date] ...

And engine in background do this:

SELECT ...
FROM ...
GROUP BY CODE, Grand Total Enter, Grand Total Exit

You have to remove your Grand Totals from inside qry. You can move it into outer column selection or separate it into qry and create joins.

You can do sumtotals with + operation of your dynamic columns too (01_Jan_2017 + 01_Feb_2017...).

EDITED 2017-01-04 - Try this:

SELECT 
    [CODE]
    ,[Date] = CONVERT(VARCHAR, START_TIME, 106)
    ,[NUM_TO_ENTER]
FROM ...
PIVOT (SUM([NUM_TO_ENTER]) FOR [Date] ...

EDITED 2017-01-05 - To get totals:

SELECT 
    [CODE],
    [Grand Total Enter] = (select ...),
    [Grand Total Exit] = (select ...)
FROM (
    SELECT 
        [CODE]
    FROM ...
    GROUP BY [CODE]
) codes

To get final output join this two datasets together.

Upvotes: 1

Related Questions