CODEWITHSUNDEEP
sqlsql-server
ShellyD7
ShellyD7

Reputation: 11

using a subquery to create columns in a specified order

I'm transitioning from oracle-based Crystal Reports to SQL Server/Management Studio and I'm having issues recreating the output below within a larger query. I report on patient data so I created a sample table which I hope explains what I need. For the output I need a row for each customer id and 3 frosting columns with the frosting in order of the line number but only those frostings that have a batter_code in (450,360).

Orders: 

id      line    batter_code   frosting 
234     1       450           chocolate
101     1       111           Strawberry
101     2       450           Orange
101     3       360           Cherry

Customer: 

id    first_name  last_name
234   Jon         Smith
101   Jane        Smith

Output: 

id   Last Name  First Name  Frosting 1  Frosting 2  Frosting 3
101  Smith      Jane        Orange      Cherry
234  Smith      Jon         Chocolate

I'm sure this situation has been asked and answered but I could not find it. If there is a solution would you care to redirect me? If I need to provide clarification please let me know. Thank you again for your help.

Upvotes: 1

Views: 50

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

Just another option is to use PIVOT in concert with Row_Number()

Example

Select *
 From  (
        Select A.ID
              ,A.[first_name]
              ,A.[last_name]
              ,Item = concat('Frosting ',Row_Number() over (Partition By B.ID Order by Line) )
              ,Value = frosting
         From Customer A
         Join Orders B on A.id=B.id
         Where batter_code in (450,360)
       ) src
 Pivot (max(Value) for Item in ([Frosting 1],[Frosting 2],[Frosting 3]) )pvt

Returns

ID  first_name  last_name   Frosting 1  Frosting 2  Frosting 3
101 Jane        Smith       Orange      Cherry      NULL
234 Jon         Smith       chocolate   NULL        NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can use conditional aggregation:

select c.id, c.first_name, c.last_name,
       max(case when o.new_line = 1 then o.frosting end) as frosting_1,
       max(case when o.new_line = 2 then o.frosting end) as frosting_2,
       max(case when o.new_line = 3 then o.frosting end) as frosting_3
from customer c join
     (select o.*,
             row_number() over (partition by o.id order by o.line) as new_line
      from orders o
      where o.batter_code in (450, 360)
     ) o
     on c.id = o.id    
group by c.id, c.first_name, c.last_name;

The key is re-enumerating the "lines" before you do the aggregation.

Upvotes: 3

Related Questions