Need help for SQL server 2012 query

Transact-SQL
1

Hello Evevryone,

I have table "Zipo" with the following colomns:

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

Example of data

|RdvId|WorkLineNumber|WorkDescription|
|1177|0|Distribution - Remplcement kit Diesel|
|1177|1|Pneus - Forfait montage + équilibrage x 2|
|1177|2|Freinage - Remplacement plaquettes AV|
|1181|0|Entretien des 180 000 km Long Life Classique|
|1181|1|Pneus - Réparation x 1|

I need to have data transformation as:

|RdvId|Q1|Q2|Q3|
|1177|Distribution - Remplcement kit Diesel|Pneus - Forfait montage + équilibrage x 2|Freinage - Remplacement plaquettes AV|
|1181|Entretien des 180 000 km /Long Life Classique|Pneus - 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

2

In future, please provide consumable test data:

CREATE TABLE #t
(
	RdvId int NOT NULL
	,WorkLineNumber int NOT NULL
	,WorkDescription nvarchar(250)
);
INSERT INTO #t
VALUES (1177, 0, 'Distribution - Remplcement kit Diesel')
	,(1177, 1, 'Pneus - Forfait montage + équilibrage x 2')
	,(1177, 2, 'Freinage - Remplacement plaquettes AV')
	,(1181, 0, 'Entretien des 180 000 km Long Life Classique')
	,(1181, 1, 'Pneus - Réparation x 1');

There are two common approaches:
Option 1

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
FROM #t
GROUP BY RdvId;

Option 2

SELECT RdvId, [0] AS Q1, [1] AS Q2, [2] AS Q3
FROM  
(
	SELECT RdvId, WorkLineNumber, WorkDescription
	FROM #t
) S
PIVOT  
(  
	MAX(WorkDescription)  
	FOR  WorkLineNumber IN ([0], [1], [2])  
) AS P;
1 Like
3

hi

hope this helps

another way of doing it

SELECT 
     RdvId
	 , STRING_AGG(WorkDescription,'|') WITHIN GROUP ( ORDER BY RdvId )   
FROM 
    #t
GROUP BY
       RdvId