How-to convert rows (for group) into columns.
Here is the source table that have:
update: added Id and IdGestion columns
Id IdGestion PeticionId Field Value
48 17 16 Type 1
49 17 16 SubType 1
50 17 16 Units 1
51 18 16 Type 1
52 18 16 SubType 2
53 18 16 Units 2
54 19 16 Type 3
55 19 16 SubType 6
56 19 16 Units 1
How-to get the Pivot table (target):
RequestId Type SubType Units
16 1 1 1
16 1 2 2
16 3 6 1
I try using PIVOT, and ROWNumber(), but get data wrong.
select g.PeticionId, Tipo,SubTipo,Unidades from [GestionSaliente] g
inner join (
SELECT PeticionId, Tipo,SubTipo,Unidades
FROM
(select gs.PeticionId, Campo, Valor
, ROW_NUMBER() OVER (PARTITION BY gs.IdGestion ORDER BY gsd.Id) as rn
FROM [GestionSaliente] gs
inner join [GestionSalienteDato] gsd
on gs.Id = gsd.GestionSalienteId --where PeticionId = 16
) AS source
PIVOT
(
MIN(Valor)
FOR Campo IN (Tipo,SubTipo,Unidades)
) as pvt
) a
on a.PeticionId = g.PeticionId
UPDATED: It works now, I think
select g.PeticionId, Tipo,SubTipo,Unidades from [GestionSaliente] g
inner join (
SELECT PeticionId, Tipo,SubTipo,Unidades
FROM
(select gs.Id IdGroup, gs.PeticionId, Campo, Valor
FROM [GestionSaliente] gs
inner join [GestionSalienteDato] gsd on gs.Id = gsd.GestionSalienteId
) AS source
PIVOT
(
MIN(Valor)
FOR Campo IN (Tipo,SubTipo,Unidades, Id, Peticion)
) as pvt
where PeticionId = 16
FOR Campo IN (Tipo,SubTipo,Unidades, Id or IdGroup, Peticion)
Type
,SubType
, andUnits
?ROW_NUMBER()
- you still have to specify the ordering columns, putting the cart before the horse.