SQL Server : transpose multiple columns [duplicate]

Question

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

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
– JonH, Commented Oct 29, 2015 at 19:14
although in this case i have multiple columns.. not just 1 value column — DigitalCattle
– DigitalCattle, Commented Oct 29, 2015 at 19:16
Look up either dynamic crosstab or dynamic pivot. This has been answered literally thousands and thousands of times. — Sean Lange
– Sean Lange, Commented Oct 29, 2015 at 19:23

Giorgi Nakeuri · Accepted Answer · 2015-10-29 19:19:11Z

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

answered Oct 29, 2015 at 19:19

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

SQL Server : transpose multiple columns [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related