0

What's the best way to get from this input to output?

Input:

date        id      name    info    price   qty
-----------------------------------------------
20140523    10036   ABC     B       12      100
20140523    10036   ABC     S       13      75
20140523    10034   XYZ     B       22      56
20140523    10034   XYZ     S       24      41
20151023    10037   PQR     B       30      45
20151023    10037   PQR     S       5       20

Output: 

date        id      name    b_price b_qty   s_price s_qty
---------------------------------------------------------
20140523    10036   ABC     12      100     13      75
20140523    10034   XYZ     22      56      24      41
20140523    10037   PQR     30      45      5       20
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DigitalCattle
  • 1
  • 1
  • 1
  • 1
  • This question has been asked over all the internets. You can pivot this data or if the types don't change much you can case this all out. Either way look into "Transposing rows to columns". – JonH Oct 29 '15 at 19:14
  • although in this case i have multiple columns.. not just 1 value column – DigitalCattle Oct 29 '15 at 19:16
  • It's the same thing you need to pivot this data. – JonH Oct 29 '15 at 19:17
  • 1
    Look up either dynamic crosstab or dynamic pivot. This has been answered literally thousands and thousands of times. – Sean Lange Oct 29 '15 at 19:23

1 Answers1

1

With conditional aggregation:

select date, 
       id, 
       name,
       sum(case info = 'b' then price end) as bprice,
       sum(case info = 'b' then qty end) as bqty,
       sum(case info = 's' then price end) as sprice,
       sum(case info = 's' then qty end) as sqty
from tablename
group by date, id, name
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75