How to make rows as headers on SQL Server

Question

I have a table like below

    -------------------------------------
   | Date      | EventName | Occurrences|
   --------------------------------------
   | 4/30/2019 | Party     | 20         |
   | 4/30/2019 | Reunion   | 10         |
   | 4/30/2019 | Auction   | 5          |
   | 4/30/2019 | Party     | 10         |
   | 4/30/2019 | Reunion   | 10         |
   --------------------------------------

If I run a query like this

SELECT Date, EventName, SUM(Occurrences)
FROM [dbo].[Mytable]
GROUP BY Date, EventName
ORDER BY Date DESC

I get a result similar to this...

   | Date      | EventName | Occurrences|
   --------------------------------------
   | 4/30/2019 | Party     | 30         |
   | 4/30/2019 | Reunion   | 20         |
   | 4/30/2019 | Auction   | 5          |

How can I transform the rows into Columns so my result looks more like this?

   | Date      | Party     | Reunion    | Auction |
   ------------------------------------------------
   | 4/30/2019 | 30        | 20         |    5    |

I don't know how to transform rows into columns but what I have tried so far is to query for each EventName and then join them. I think this is not optimal at all but I haven't been able to figure out a better way.

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017 — Dale K, May 01 '19 at 02:52
Search this site for `[sql-server] rows to columns` or `[sql-server] pivot table`. This has been asked (and answered) many times before. — Ken White, May 01 '19 at 02:53
Thank you both! I did not know about the pivot functionality :) — user3587624, May 01 '19 at 02:53
Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Zack, May 01 '19 at 18:09

score 1 · Answer 1 · answered May 01 '19 at 02:55

You can try to use condition aggregate function, CASE WHEN with SUM

SELECT Date, 
    SUM(CASE WHEN EventName = 'Party' THEN Occurrences ELSE 0 END) Party,
    SUM(CASE WHEN EventName = 'Reunion' THEN Occurrences ELSE 0 END) Reunion,
    SUM(CASE WHEN EventName = 'Auction' THEN Occurrences ELSE 0 END) Auction
FROM [dbo].[Mytable]
GROUP BY Date

Joel Coehoorn · Accepted Answer · 2019-05-01T17:38:17.020

The SQL language has a very strict rule that you must know the number and types of columns in the results based only on the schema, before a query starts to execute. What you're asking for is not known until after the query begins to inspect the data.

This is still possible if you know the values you expect as columns. In that case, you can use conditional aggregration, or sometimes a PIVOT statement... but in both cases you must specify every column you need along with the calculation required for that column by hand. It can quickly become unwieldy to write and maintain if you have a lot of result columns.

This is also possible using dynamic SQL, whereby you run the query in three steps: first, run a query to get the column names you'll need. Second, use that information to build a new query string with the needed columns. Third, execute the new query string. Dynamic is sql is both dangerous and slow, and should only be used when you absolutely have to.

In most situations, the correct response here is option 3: pivot the data in the client program or reporting tool. Run the query you already have, and let the data consumer worry about the rest. The reason this is difficult boils down to formal relational set theory. What you're asking to do isn't only outside of traditional set theory, but in direct opposition to how to best use relations, and therefore database engine language designers are reluctant to build in a simple syntax for those operations.

Using the PIVOT statement worked well for my case where the number of columns will be fixed. Thank you for your help and the comprehensive response. — user3587624, May 01 '19 at 03:18

How to make rows as headers on SQL Server

2 Answers2