-2

How to pivot vertical rows record of SQL Server table into column data. I have tried few queries but not able to achieve the result.

I have a table called TRANSACTIONLOG which has three columns 

CREATEDATE, TRANSACTION, COUNTS

Table record

I need to GROUP the record on CREATEDATE and show transaction types as a column and COUNTS as its value.

Below is what the result should look like -

Output Result

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sks
  • 612
  • 7
  • 23

1 Answers1

0

This is easy with some conditional aggregation.

SELECT CREATEDATE
    , [INSERT] = MAX(case when [TRANSACTION] = 'INSERT' then COUNTS end)
    , [UPDATE] = MAX(case when [TRANSACTION] = 'UPDATE' then COUNTS end)
    , [DELETE] = MAX(case when [TRANSACTION] = 'DELETE' then COUNTS end)
FROM TRANSACTIONLOG
GROUP BY CREATEDATE
ORDER BY CREATEDATE
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • Above query will not print the COUNTS value. – Sks Jun 13 '19 at 07:26
  • This will work - SELECT CREATEDATE , [INSERT] = MAX(case when [TRANSACTION] = 'INSERT' then COUNTS end) , [UPDATE] =MAX(case when [TRANSACTION] = 'UPDATE' then COUNTS end) , [DELETE] = MAX(case when [TRANSACTION] = 'DELETE' then COUNTS end) FROM TRANSACTIONLOG GROUP BY CREATEDATE ORDER BY CREATEDATE – Sks Jun 13 '19 at 07:59
  • @Sks I was assuming you needed to count the rows (the column name confused me). I will adjust my answer. ;) – Sean Lange Jun 13 '19 at 13:34