sql - transponer - PIVOT en múltiples columnas

pivot sql w3schools

Question

Tengo datos como este:

Product Group Product Level Quatity Sold Trend
==============================================================
Group 1 L1 10 up
Group 1 L2 20 up
Group 1 L3 30 down
Group 2 L1 20 up
Group 2 L2 40 up
Group 2 L3 60 down
Group 2 L4 80 down

Necesito obtener los datos en este formato:

Product Group L1 L1Trend L2 L2Trend L3 L3Trend L4 L4Trend
======================================================================================
Group 1 10 up 20 up 30 down
Group 2 20 up 40 up 60 down 80 down

Pude pivotar en "Nivel de producto" usando algo como:

PIVOT (MAX(quatity) FOR productlevel IN([L1],[L2],[L3],[L4]) AS p

pero se perdió cuando se trata de la tendencia.

Gracias.

Answer 1

Puede hacer esto usando subconsultas correlacionadas:

select productGroup as [Product Group]
, (select sum(quantitySold) from myTable where productGroup = a.productGroup and productLevel = ''L1'') as L1
, (select max(trend) from myTable where productGroup = a.productGroup and productLevel = ''L1'') as L1Trend
, (select sum(quantitySold) from myTable where productGroup = a.productGroup and productLevel = ''L2'') as L2
, (select max(trend) from myTable where productGroup = a.productGroup and productLevel = ''L2'') as L2Trend
-- etc.
from myTable a
group by productGroup
order by productGroup

Aquí hay un ejemplo SqlFiddle .

Puede ayudarlo a verlo de esta manera antes de usar la palabra clave PIVOT .

Si no sabe cuántos valores de ProductLevel tiene, necesitaría una solución dinámica, sin embargo.

Answer 2

Puede obtener el resultado deseado implementando la función PIVOT, pero primero DESVÍE sus múltiples columnas de Quantity Sold y Trend . El proceso unpivot los convertirá de múltiples columnas en múltiples filas de datos.

Dado que está utilizando SQL Server 2008+, puede utilizar CROSS APPLY Apply con VALUES para desvincular los datos:

select [Product Group],
col, value
from yourtable
cross apply
(
values
([Product Level], cast([Quatity Sold] as varchar(10))),
([Product Level]+''trend'', [trend])
) c (col, value);

Ver SQL Fiddle con demostración Esto convierte los datos de su tabla en el formato:

| PRODUCT GROUP | COL | VALUE |
|---------------|---------|-------|
| Group 1 | L1 | 10 |
| Group 1 | L1trend | up |
| Group 1 | L2 | 20 |
| Group 1 | L2trend | up |
| Group 1 | L3 | 30 |
| Group 1 | L3trend | down |

Ahora puede aplicar fácilmente la función PIVOT:

select [Product Group],
L1, L1trend,
L2, L2trend,
L3, L3trend,
L4, L4trend
from
(
select [Product Group],
col, value
from yourtable
cross apply
(
values
([Product Level], cast([Quatity Sold] as varchar(10))),
([Product Level]+''trend'', [trend])
) c (col, value)
) d
pivot
(
max(value)
for col in (L1, L1trend, L2, L2trend,
L3, L3trend, L4, L4trend)
) piv;

Ver SQL Fiddle con demostración . Esto le da un resultado final de:

| PRODUCT GROUP | L1 | L1TREND | L2 | L2TREND | L3 | L3TREND | L4 | L4TREND |
|---------------|----|---------|----|---------|----|---------|--------|---------|
| Group 1 | 10 | up | 20 | up | 30 | down | (null) | (null) |
| Group 2 | 20 | up | 40 | up | 60 | down | 80 | down |

Answer 3

si prefiere usar pivote, puede probar este:

select productgroup,
coalesce(L1up,L1down,'''') L1, case when L1up is not null then ''up'' when L1down is not null then ''down'' else '''' end L1trend,
coalesce(L2up,L2down,'''') L2, case when L2up is not null then ''up'' when L2down is not null then ''down'' else '''' end L2trend,
coalesce(L3up,L3down,'''') L3, case when L3up is not null then ''up'' when L3down is not null then ''down'' else '''' end L3trend,
coalesce(L4up,L4down,'''') L4, case when L4up is not null then ''up'' when L4down is not null then ''down'' else '''' end L4trend
from
(
select productgroup, [L1up],[L2up],[L3up],[L4up],[L1down],[L2down],[L3down],[L4down]
from (select productgroup, productlevel+trend pt, quantity from mytable) t
PIVOT (MAX(quantity)
FOR pt IN([L1up],[L2up],[L3up],[L4up],[L1down],[L2down],[L3down],[L4down] )) as p
) t