0

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)

10
  • You need an additional column to distinguish the "groups". Other than by making an inference from the ordering and by the repetition of a sequence of 3 values in the "Field" column, there's nothing in the first table to indicate that there actually are three groups, or which rows relate to which group.
    – Steve
    Jul 6 at 17:54
  • 1
    What is the grouping based on?...the combination of each pivoted field, Type, SubType, and Units?
    – J.D.
    Jul 6 at 18:47
  • @J.D. Source: there are 9 rows. Target: 3 rows. Explain: three groups. Each 3 rows in source is 1 row in Target (with columns Type, SubType, Units). Finally in target: 3 rows.
    – Kiquenet
    Jul 6 at 19:54
  • 1
    @Kiquenet, how are you forcing the order of the table in the first place? In other words, how are you querying that data in that specific order? I can see that the order is intended to be meaningful, but there's no way that order could be derived purely from the values on show. You're either ordering on a column that is discarded, you're relying on the coincidental order of a heap, or you're getting the data from a non-SQL context (where ordering is fixed). This is your problem for using ROW_NUMBER() - you still have to specify the ordering columns, putting the cart before the horse.
    – Steve
    Jul 6 at 20:22
  • 1
    @Kiquenet, you don't need a rownumber. You just select RequestId, GroupId, Field, Value, and then pivot these on the contents of the Field column. You'll get 3 rows, with 5 columns: RequestId, GroupId, Type, Subtype, Units. It won't collapse to 1 row like before, because the GroupId is now present to distinguish.
    – Steve
    Jul 7 at 6:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.