Rotate Database Table

Imagine I have a database table that has the following format:

╔══════════════════════╗
║ Name   Quarter Sales ║
╠══════════════════════╣
║ Joe    Q1      700   ║
║ Joe    Q2      650   ║
║ Joe    Q3      660   ║
║ Bill   Q1      500   ║
║ Bill   Q2      520   ║
║ Bill   Q3      550   ║
║ Bob    Q2      200   ║
║ Bob    Q3      250   ║
╚══════════════════════╝

I need to pivot it in the format as below. Here each distinct value of quarter becomes a column, and each quarterly sales is the value of the corresponding column for each sales person.

╔══════════════════════╗
║ Name   Q1   Q2   Q3  ║
╠══════════════════════╣
║ Joe    700  650  660 ║
║ Bill   500  520  550 ║
║ Bob         200  250 ║
╚══════════════════════╝

Is this possible with SQL? I am using SQL Server, but prefer a plain SQL if possible.

We do not know the values that go into Quarter column up front i.e. we are not limited to only Q1/Q2/Q3 etc. Let’s say the users can enter Q1, Quarter 1, or anything else they like.

Answer

You can try to use aggregation condition SUM with CASE WHEN

SELECT Name,
       SUM(CASE WHEN Quarter = 'Q1' THEN Sales ELSE 0 END) 'Q1',
       SUM(CASE WHEN Quarter = 'Q2' THEN Sales ELSE 0 END) 'Q2',
       SUM(CASE WHEN Quarter = 'Q3' THEN Sales ELSE 0 END) 'Q3'
FROM T
GROUP BY Name

Advertisement

Answer