Forum Discussion

Abdel1978's avatar
Abdel1978
Copper Contributor
Nov 13, 2023

Pivot and group table.

Hello Evevryone,

I have table Zipo with the following colomns:

RdvId int, WorkLineNumber int , WorkDescription nvarchar(250):

Example of data

RdvIdWorkLineNumberWorkDescription
11770Distribution - Remplcement kit Diesel
11771Pneus - Forfait montage + équilibrage x 2
11772Freinage - Remplacement plaquettes AV
11810Entretien des 180 000 km /Long Life Classique
11811Pneus - Réparation x 1

 

I need to have data transformation as:

RdvIdQ1Q2Q3
1177Distribution - Remplcement kit DieselPneus - Forfait montage + équilibrage x 2Freinage - Remplacement plaquettes AV
1181Entretien des 180 000 km /Long Life ClassiquePneus - Réparation x 1 

For the same RdvId

IF WorkLineNumber=0 THEN Q1 =WorkDescription

IF WorkLineNumber=1 THEN Q2 =WorkDescription

IF WorkLineNumber=2 THEN Q3 =WorkDescription

Thank you for help

 

 

  • Abdel1978's avatar
    Abdel1978
    Copper Contributor
    Nov 13, 2023

    SELECT RdvId

    ,MAX(CASE WHEN WorkLineNumber = 0 THEN WorkDescription END) AS Q1

    ,MAX(CASE WHEN WorkLineNumber = 1 THEN WorkDescription END) AS Q2

    ,MAX(CASE WHEN WorkLineNumber = 2 THEN WorkDescription END) AS Q3 F

    ROM #t

    ROUP BY RdvId;

Resources