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?
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
| 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) |
CodePudding user response：
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