[SOLVED] Can this pivot be done more efficiently?

Issue

I managed to find a solution for formatting the shown driver table. The result is exactly what i need: One row for every QuoteID with the columns Birthdate and DriverType seperated by DriverIndex. My real drivertable has millions of rows.

Yet i am not convinced that this is the way to go. It just seems odd to me. But i am not a SQL expert. My Question: Can this be done in a more efficent way?

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE driver
([QuoteID] int, [DriverIndex] int,[Birthdate] date,[DriverType] int)
;
INSERT INTO driver
([QuoteID], [DriverIndex],[Birthdate], [DriverType])
VALUES
('72', '1','2022/01/01','11'),
('72', '2','2022/02/01','12'),
('73', '1','2022/03/01','13'),
('74', '1','2022/04/01','13'),
('73', '2','2022/05/01','10'),
('73', '3','2022/06/01','11');

Driver Table:

| QuoteID | DriverIndex |  Birthdate | DriverType |
|---------|-------------|------------|------------|
|      72 |           1 | 2022-01-01 |         11 |
|      72 |           2 | 2022-02-01 |         12 |
|      73 |           1 | 2022-03-01 |         13 |
|      74 |           1 | 2022-04-01 |         13 |
|      73 |           2 | 2022-05-01 |         10 |
|      73 |           3 | 2022-06-01 |         11 |

Query:

with sq as(select QuoteID AS QuoteID_sq, [1] AS DriverIndex_1_DriverType , [2] AS DriverIndex_2_DriverType , [3] as DriverIndex_3_DriverType
from
( select [QuoteID], [DriverIndex],[Birthdate], [DriverType] from driver) src
pivot
( max([DriverType]) for DriverIndex in ([1], [2], [3]) ) piv),
sq2 as(select QuoteID as QuoteID_sq2, [1] AS DriverIndex_1_Birthdate , [2] AS DriverIndex_2_Birthdate , [3] as DriverIndex_3_Birthdate
from
( select [QuoteID], [DriverIndex],[Birthdate], [DriverType] from driver) src
pivot
( max([Birthdate]) for DriverIndex in ([1], [2], [3]) ) piv),
sq3 as(Select * from sq,sq2 Where sq.QuoteID_sq=sq2.QuoteID_sq2)
Select QuoteID_sq as QuoteID, max([DriverIndex_1_DriverType]) AS DriverIndex_1_DriverType,MAX([DriverIndex_2_DriverType]) AS DriverIndex_2_DriverType,Max([DriverIndex_3_DriverType]) AS DriverIndex_3_DriverType ,
max([DriverIndex_1_Birthdate]) AS DriverIndex_1_Birthdate , max([DriverIndex_2_Birthdate]) AS DriverIndex_2_Birthdate , max([DriverIndex_3_Birthdate]) as DriverIndex_3_Birthdate
from sq3
group by QuoteID_sq

Results:

| QuoteID | DriverIndex_1_DriverType | DriverIndex_2_DriverType | DriverIndex_3_DriverType | DriverIndex_1_Birthdate | DriverIndex_2_Birthdate | DriverIndex_3_Birthdate |
|---------|--------------------------|--------------------------|--------------------------|-------------------------|-------------------------|-------------------------|
|      72 |                       11 |                       12 |                   (null) |              2022-01-01 |              2022-02-01 |                  (null) |
|      73 |                       13 |                       10 |                       11 |              2022-03-01 |              2022-05-01 |              2022-06-01 |
|      74 |                       13 |                   (null) |                   (null) |              2022-04-01 |                  (null) |                  (null) |

Solution

You can simplify this by using only conditional aggregation.

SELECT QuoteID
, MAX(CASE WHEN DriverIndex = 1 THEN DriverType END) AS DriverIndex_1_DriverType
, MAX(CASE WHEN DriverIndex = 2 THEN DriverType END) AS DriverIndex_2_DriverType
, MAX(CASE WHEN DriverIndex = 3 THEN DriverType END) AS DriverIndex_3_DriverType
, MAX(CASE WHEN DriverIndex = 1 THEN Birthdate END) AS DriverIndex_1_Birthdate
, MAX(CASE WHEN DriverIndex = 2 THEN Birthdate END) AS DriverIndex_2_Birthdate
, MAX(CASE WHEN DriverIndex = 3 THEN Birthdate END) AS DriverIndex_3_Birthdate
FROM driver
GROUP BY QuoteID
ORDER BY QuoteID;
QuoteID DriverIndex_1_DriverType DriverIndex_2_DriverType DriverIndex_3_DriverType DriverIndex_1_Birthdate DriverIndex_2_Birthdate DriverIndex_3_Birthdate
72 11 12 null 2022-01-01 2022-02-01 null
73 13 10 11 2022-03-01 2022-05-01 2022-06-01
74 13 null null 2022-04-01 null null

Demo on db<>fiddle here

Answered By – LukStorms

Answer Checked By – Gilberto Lyons (BugsFixing Admin)

Leave a Reply

Your email address will not be published. Required fields are marked *