Combine multiple rows data in a single row

Question

I am trying to pivot a table into a view which would be more easily manipulable with Excel. I am creating views in Sql Server that can be read by the accounting department in order to create stats and other cross-table pivot in Excel.

The table in its current format

OccupationId | ProductId | IsGroup | IsAdditionalNight | Price
-------------+-----------+---------+-------------------+------
1            | 47        | 0       | 0                 | 50
2            | 47        | 0       | 1                 | 45
3            | 47        | 1       | 0                 | 30
4            | 47        | 1       | 1                 | 28

The View format that I want to expose

ProductId | Price | AdditionalNight | GroupPrice | GroupAdditionalNight
----------+-------+-----------------+------------+---------------------
47        | 50    | 45              | 30         | 28

How can I achieve that in Sql Server 2008 R2 ? I need the query to be fast since it will be part of a bigger Query (using CTE).

Currently the only way that I am thinking is to include 4 sub queries to get all 4 prices individually, but I don't like the approach and it scan the table 4 times.

a1ex07 · Accepted Answer · 2012-09-17 14:37:04Z

3

SELECT ProductId,
MAX(CASE 
 WHEN IsGroup=0 AND IsAdditionalNight =0 then Price
 END) as [Price],
MAX(CASE 
 WHEN IsGroup=0 AND IsAdditionalNight =1 then Price
 END) as [AdditionalNight],
MAX(CASE 
 WHEN IsGroup=1 AND IsAdditionalNight =0 then Price
 END) as [GroupPrice],
MAX(CASE 
 WHEN IsGroup=1 AND IsAdditionalNight =1 then Price
 END) as [GroupAdditionalNight] 

 FROM table1     
GROUP BY ProductId

Or you can do the same using PIVOT (Personally I found the syntax of PIVOT more complex than approach above, but there is no performance difference, and some people prefer PIVOT).

answered Sep 17, 2012 at 14:37

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

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

2 Comments

Taryn Over a year ago

much cleaner than doing a PIVOT

Pierre-Alain Vigeant Over a year ago

As simple as that? Demn, I was effectively trying to learn that PIVOT method. Sometime you forget the simple method. Thank you.

Collectives™ on Stack Overflow

Combine multiple rows data in a single row

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related