CODEWITHSUNDEEP
sqldatepivot
csotelo
csotelo

Reputation: 1475

SQL: pivot dates and hours without aggregate function

I have a collection of dates and hours, I'd like pivot this information by the date and show the hours inside.

enter image description here

The problem is the aggregate function, I'm compelled to put a max/min function in the hour column, resulting this:

enter image description here

I need something like this, but the hours instead the dates (order by hours)

enter image description here

I'm using:

SELECT GtDesc, ErrDesc,  [05/02/2018], [06/02/2018], [07/02/2018], [08/02/2018], [09/02/2018], [12/02/2018]
FROM(
SELECT  ErrDesc, GtDesc, CONVERT(VARCHAR, dt, 103) 'DateTrans', FORMAT(dt, 'hh:mm tt') 'HourTrans',
        rn = ROW_NUMBER() Over (Partition By GtDesc, ErrDesc, dt Order By dt)
FROM    Trans t
INNER JOIN Gates g ON g.CID = t.CID
WHERE   ( dt >= CONVERT(DATETIME, '5/02/2018 7:00 AM', 103) AND dt <= CONVERT(DATETIME, '12/02/2018 7:00 PM', 103) )
) AS PivotData
PIVOT(
    MAX(DateTrans)
    FOR [DateTrans] IN (
      [05/02/2018], [06/02/2018], [07/02/2018], [08/02/2018], [09/02/2018], [12/02/2018]
    )
) AS PivotTable

What kind of change I need in my query?

Upvotes: 2

Views: 781

Answers (2)

Steve
Steve

Reputation: 960

@csotelo, I gather you're trying to do something like this below.

The only problem is that SQL has no facility (without significantly more complex dynamic-SQL approaches) to produce this kind of output dynamically - you'd have to hard-code the logic for every single date.

Also, are the times supposed to progress downwards and across diagonally (as in your image), or is each day's time supposed to start on the top row?

WITH source_table AS
(
    SELECT  
        ,GtDesc
        ,ErrDesc
        ,dt
        ,CONVERT(VARCHAR, dt, 103)  AS DateTrans
        ,FORMAT(dt, 'hh:mm tt')     AS HourTrans

    FROM
        Trans AS t

    INNER JOIN
        Gates AS g
        ON (g.CID = t.CID)

    WHERE 
        dt BETWEEN (CONVERT(DATETIME, '5/02/2018 7:00 AM', 103)) AND (CONVERT(DATETIME, '12/02/2018 7:00 PM', 103))
)
,source_table_ext AS
(
    SELECT 
        GtDesc
        ,ErrDesc
        ,dt
        ,ROW_NUMBER() OVER (PARTITION BY GtDesc, ErrDesc, DateTrans ORDER BY dt) AS date_line_num
        ,DateTrans
        ,HourTrans
    FROM source_table
)
,pivoted_table AS
(
    SELECT *
    FROM source_table_ext PIVOT (
        MAX(HourTrans)
        FOR DateTrans IN (
             [05/02/2018]
            ,[06/02/2018]
            ,[07/02/2018]
            ,[08/02/2018]
            ,[09/02/2018]
            ,[12/02/2018]
            ) 
    ) AS pvt
)

SELECT
    GtDesc
    ,ErrDesc
    ,MAX([05/02/2018]) AS [05/02/2018]
    ,MAX([06/02/2018]) AS [06/02/2018]
    ,MAX([07/02/2018]) AS [07/02/2018]
    ,MAX([08/02/2018]) AS [08/02/2018]
    ,MAX([09/02/2018]) AS [09/02/2018]
    ,MAX([12/02/2018]) AS [12/02/2018]

FROM
    pivoted_table

GROUP BY
    GtDesc, ErrDesc, date_line_num

ORDER BY
    GtDesc, ErrDesc, date_line_num

Upvotes: 0

Prabhath Amaradasa
Prabhath Amaradasa

Reputation: 503

You can try something like this

    WITH data_CTE(iid, COL0,COL1,RID,ErrDesc, GtDesc)
AS

(

SELECT id, DateTrans, HourTrans,
  ROW_NUMBER() OVER (PARTITION BY (DateTrans)ORDER BY  id) AS RID ,ErrDesc, GtDesc
  FROM Gates

)

SELECT ErrDesc, GtDesc, [05/22/2018], [06/02/2018], [07/02/2018], [08/02/2018]

FROM

(SELECT  COL0,COL1,RID ,ErrDesc, GtDesc

FROM data_CTE)C

PIVOT

(

max(COL1)

FOR COL0 IN ( [05/22/2018], [06/02/2018], [07/02/2018], [08/02/2018])

) AS PivotTable;

Here is the Fiddler link.This is not the exact order you want.

Upvotes: 1

Related Questions