Transpose columns into rows in SQL Server?

Question

I have the following sales table (SQL Server 2017) (for each good is about 20 rows):

+---------+----------+-------------+-------+---------+------+
| good_id | store_id | week_number | promo | holiday | sale |
+---------+----------+-------------+-------+---------+------+
| 201712  | 255      | 1           | 1     | 0       | 2    |
+---------+----------+-------------+-------+---------+------+
| 201712  | 255      | 2           | 0     | 0       | 0    |
+---------+----------+-------------+-------+---------+------+

I want to prepare a dataset so that each good in a particular store corresponds to one row:

+---------+----------+---------------+---------------+---------+---------+-----------+-----------+--------+--------+
| good_id | store_id | week_number_1 | week_number_2 | promo_1 | promo_2 | holiday_1 | holiday_2 | sale_1 | sale_2 |
+---------+----------+---------------+---------------+---------+---------+-----------+-----------+--------+--------+
| 201712  | 255      | 1             | 2             | 1       | 0       | 0         | 0         | 2      | 0      |
+---------+----------+---------------+---------------+---------+---------+-----------+-----------+--------+--------+

I know how to do this for a single column with unique values:

SELECT *
FROM
(
    SELECT [good_id],
           [store_id],
           [week_number],
           [sale]
    FROM table
    WHERE good_id = 201712
    AND store_id = 255
) AS SourceTable PIVOT(AVG([sale]) FOR [week_number] IN([1],
                                                         [2])) AS PivotTable

But how can I write the code I need, I can’t understand yet

Gordon Linoff · Accepted Answer · 2019-08-23 11:25:53Z

2

You can use conditional aggregation:

select good_id, store_id,
       max(case when week_number = 1 then promo end) as promo_1,
       max(case when week_number = 1 then holiday end) as holiday_1,
       max(case when week_number = 1 then sale end) as sale_1,
       max(case when week_number = 2 then promo end) as promo_2,
       max(case when week_number = 2 then holiday end) as holiday_2,
       max(case when week_number = 2 then sale end) as sale_2,
       . . . 
from table
group by good_id, store_id;

answered Aug 23, 2019 at 11:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Salman Arshad Over a year ago

OP will have to repeat this around 20 times!

Collectives™ on Stack Overflow

Transpose columns into rows in SQL Server?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related