using a subquery to create columns in a specified order

Question

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.

I assume you can't have more than 3 frostings?

Thom A
– Thom A ♦

2019-03-05 18:53:36 +00:00
Commented Mar 5, 2019 at 18:53 — Thom A
– Thom A ♦, Commented Mar 5, 2019 at 18:53
That is correct, no more than 3 frostings.

ShellyD7
– ShellyD7

2019-03-05 19:08:05 +00:00
Commented Mar 5, 2019 at 19:08 — ShellyD7
– ShellyD7, Commented Mar 5, 2019 at 19:08

Gordon Linoff · Accepted Answer · 2019-03-05 18:46:48Z

3

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.

answered Mar 5, 2019 at 18:46

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

ShellyD7 Over a year ago

Thank you so much for your quick response!

John Cappelletti · Accepted Answer · 2019-03-05 18:55:46Z

1

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

answered Mar 5, 2019 at 18:55

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Collectives™ on Stack Overflow

using a subquery to create columns in a specified order

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related