CODEWITHSUNDEEP
sqlsql-serverpivot-table
Arif YILMAZ
Arif YILMAZ

Reputation: 5876

How to create a SQL Server Pivot Table

I am trying to create a SQL Server PIVOT table but I having trouble.

Here is the result I want to get:

enter image description here

Here is the SQL query I have prepared but I am lost. How can I make this to work.

select *
from
    (select DESCRIPTION_, DISPLAYNAME, SUCCESFULRECORDCOUNT, FINISHDATETIME
     from LOGTABLE (NOLOCK)
    ) as X
pivot
     (sum(SUCCESFULRECORDCOUNT) for FINISHDATETIME in (GETDATE()-4)) as DAY1
pivot
     (sum(SUCCESFULRECORDCOUNT) for FINISHDATETIME in (GETDATE()-3)) as DAY2
pivot
     (sum(SUCCESFULRECORDCOUNT) for FINISHDATETIME in (GETDATE()-2)) as DAY2
pivot
     (sum(SUCCESFULRECORDCOUNT) for FINISHDATETIME in (GETDATE()-1)) as DAY3

Upvotes: 0

Views: 52

Answers (2)

Kostya
Kostya

Reputation: 1605

i think you can just use group by like 

SELECT DESCRIPTION_,DISPLAYNAME
,SUM(IIF(FINISHDATETIME IN (GETDATE()-4),SUCCESFULRECORDCOUNT,0)) AS 'DAY1'
,SUM(IIF(FINISHDATETIME IN (GETDATE()-3),SUCCESFULRECORDCOUNT,0)) AS 'DAY2'
,SUM(IIF(FINISHDATETIME IN (GETDATE()-2),SUCCESFULRECORDCOUNT,0)) AS 'DAY3'
,SUM(IIF(FINISHDATETIME IN (GETDATE()-1),SUCCESFULRECORDCOUNT,0)) AS 'DAY4'
FROM LOGTABLE  (NOLOCK)
GROUP BY 
DESCRIPTION_,
DISPLAYNAME

Upvotes: 1

MatBailie
MatBailie

Reputation: 86775

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=915122598b6f8f350127f79e21b3c12c

You need to change your dates into literal values to use in the pivot.

SELECT
  *
FROM
(
  SELECT
    DESCRIPTION_,
    DISPLAYNAME,
    CAST(DATEDIFF(DAY, FINISHDATETIME, GETDATE()) AS VARCHAR(1))  AS dateNum,
    SUCCESFULRECORDCOUNT
  FROM
    test
  WHERE
    FINISHDATETIME >= CAST(DATEADD(DAY, -4, GETDATE()) AS DATE)
)
  data
PIVOT
(
  SUM(SUCCESFULRECORDCOUNT) FOR dateNum IN ([4], [3], [2], [1])
)
  pvt
;

Upvotes: 1

Related Questions