Sample Header Ad - 728x90

PIVOT for "groups" - rows to columns

0 votes
0 answers
236 views
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)***
Asked by Kiquenet (155 rep)
Jul 6, 2023, 04:36 PM
Last activity: Jul 7, 2023, 05:16 PM