CODEWITHSUNDEEP
sqlsql-server
user3587624
user3587624

Reputation: 1471

How to make rows as headers on SQL Server

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.

Upvotes: 1

Views: 4361

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415840

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.

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 1

Related Questions