Pivot function in MS SQL Server

Question

I have a database with two tables called cards and details

cards:

persnr  | id | lastchecked | lastcheckedby_user| 

00123   |1061| 2020-10-21  | me

00124   |1062| 2020-10-21  | you

details:

ID_Card  | License | Valid_till | Valid_from | Details_description

1061     | A       | 2022-01-01 | 2018-01-01 | 95389 

1061     | B       | 2025-02-19 | 2018-04-01 | 79064

1061     | C       | 2024-03-16 | 2018-09-11 | 84532

1062     | A       | 2021-03-15 | 2019-11-11 | 83499

What i want to achieve:

details:

ID_Card  | LicenseA | Valid_tillA | Valid_fromA | Details_descriptionA|LicenseB | Valid_tillB | Valid_fromB | Details_descriptionB|Details_descriptionC|LicenseC | Valid_tillBC| Valid_fromC | Details_descriptionC

So my SQL Statement at the moment to have the list above is like

with cte as
(
select c.persnr,
       c.lastchecked,
       d.ID_card,
       d.License     as License1, -- License
       d.License+'2' as License2, -- FSGUELTIGBIS
       d.License+'3' as License3, -- FSGUELTIGAB
       d.License+'4' as License4, -- FSZUSATZANGABEN
       d.valid_till,
       d.valid_from,
       d.details_description
from cards c
join details d
  on d.ID_card = c.id
)
select p4.persnr,
     p4.lastchecked,
     p4.ID_card,
     max(p4.A ) as License_B,
     max(p4.A2) as Valid_tillB,
     max(p4.A3) as Valid_fromB,
     max(p4.A4) as Description_B,
     max(p4.B ) as License_BE,
     max(p4.B2) as Valid_tillBE,
     max(p4.B3) as Valid_fromBE,
     max(p4.B4) as Description_BE,
     max(p4.C ) as License_C,
     max(p4.C2) as Valid_tillC,
     max(p4.C3) as Valid_fromC,
     max(p4.C4) as Description_C,
     max(p4.D)  as License_C1,
     max(p4.D2) as Valid_tillC1,
     max(p4.D3) as Valid_fromC1,
     max(p4.D4) as Description_C1,
     max(p4.E)  as License_C1E,
     max(p4.E2) as Valid_tillC1E,
     max(p4.E3) as Valid_fromC1E,
     max(p4.E4) as Description_C1E,
     max(p4.F)  as License_CE,
     max(p4.F2) as Valid_tillCE,
     max(p4.F3) as Valid_fromCE,
     max(p4.F4) as Description_CE,
     max(p4.G)  as License_D,
     max(p4.G2) as Valid_tillD,
     max(p4.G3) as Valid_fromD,
     max(p4.G4) as Description_D,
     max(p4.H)  as License_D1,
     max(p4.H2) as Valid_tillD1,
     max(p4.H3) as Valid_fromD1,
     max(p4.H4) as Description_D1,
     max(p4.I)  as License_D1E,
     max(p4.I2) as Valid_tillD1E,
     max(p4.I3) as Valid_fromD1E,
     max(p4.I4) as Description_D1E,
     max(p4.J)  as License_DE,
     max(p4.J2) as Valid_tillDE,
     max(p4.J3) as Valid_fromDE,
     max(p4.J4) as Description_DE

from cte
pivot (max(cte.License1)           for cte.License1 in ([A],  [B],  [C],  [D],  [E],  [F],  [G],   [H],   [I],  [J])) p1
pivot (max(p1.Valid_till)         for p1.License2  in ([A2], [B2], [C2], [D2], [E2], [F2], [G2],  [H2],  [I2], [J2])) p2
pivot (max(p2.Valid_from)          for p2.License3  in ([A3], [B3], [C3], [D3], [E3], [F3], [G3],  [H3],  [I3], [J3])) p3
pivot (max(p3.details_description)      for p3.License4  in ([A4], [B4], [C4], [D4], [E4], [F4], [G4],  [H4],  [I4], [J4])) p4
group by p4.persnr,
       p4.lastchecked,
       p4.id_card;

Now, I wonder, which pivot function to use. In most of the examples, AVG, COUNT, MAX is used. I think it would be necessary to use CASE, right?

One person can have several licenses with different values (All licenses are known, goes from A -E) concerning valid from, valid till and description. I want all these information in one row in which I can see all the licenses and their validation dates. I think it would be a function like pivot, but I don't know how to start. Could someone try to help me?

Thanks in advance, Christian

Answer 1

You want to pivot multiple times over the same column: License. When that column is pivoted once, it is no longer available in the resulting data set. A solution could be to duplicate the values of the License column (inspiration from this question/answer). I used a common table expression (CTE) for this.

Also, for your current expected result, you do not need table cards. None of its fields are part of your expected result. So a join with that table is not needed... I did add a join in order to show some fields from table cards in my result.

Sample data

Edit: added license CE with description NewLicense.

create table cards
(
  persnr varchar(5),
  id int,
  lastchecked date,
  lastcheckedby_user nvarchar(5)
);
insert into cards (persnr, id, lastchecked, lastcheckedby_user) values
('00123', 1061, '2020-10-21', 'me' ),
('00124', 1062, '2020-10-21', 'you');

create table details
(
  ID_Card int,
  License nvarchar(1),
  Valid_till date,
  Valid_from date,
  Details_description nvarchar(10)
);
insert into details (ID_Card, License, Valid_till, Valid_from, Details_description) values
(1061, 'A',  '2022-01-01', '2018-01-01', '95389'),
(1061, 'B',  '2025-02-19', '2018-04-01', '79064'),
(1061, 'C',  '2024-03-16', '2018-09-11', '84532'),
(1062, 'A',  '2021-03-15', '2019-11-11', '83499'),
(1062, 'CE', '2022-04-01', '2020-03-31', 'NewLicense');

Solution

I did license A, B and C. You can add D and E yourself the same way.
Edit: added license CE as example for solution extension.

with cte as
(
  select c.persnr,
         c.lastchecked,
         d.ID_Card,
         d.License     as License1, -- license
         d.License+'2' as License2, -- valid_till
         d.License+'3' as License3, -- valid_from
         d.License+'4' as License4, -- details_description
         d.Valid_till,
         d.Valid_from,
         d.Details_description
  from cards c
  join details d
    on d.ID_Card = c.id
)
select p4.persnr,
       p4.lastchecked,
       p4.ID_Card,
       p4.A   as LicenseA,
       p4.A2  as Valid_tillA,
       p4.A3  as Valid_fromA,
       p4.A4  as DescriptionA,
       p4.B   as LicenseB,
       p4.B2  as Valid_tillB,
       p4.B3  as Valid_fromB,
       p4.B4  as DescriptionB,
       p4.C   as LicenseC,
       p4.C2  as Valid_tillC,
       p4.C3  as Valid_fromC,
       p4.C4  as DescriptionC,
       p4.CE  as LicenseCE,
       p4.CE2 as Valid_tillCE,
       p4.CE3 as Valid_fromCE,
       p4.CE4 as DescriptionCE
from cte
pivot (max(cte.License1)           for cte.License1 in ([A],  [B],  [C] , [CE])) p1
pivot (max(p1.Valid_till)          for p1.License2  in ([A2], [B2], [C2], [CE2])) p2
pivot (max(p2.Valid_from)          for p2.License3  in ([A3], [B3], [C3], [CE3])) p3
pivot (max(p3.Details_description) for p3.License4  in ([A4], [B4], [C4], [CE4])) p4

Edit: added extended solution with extra aggregation.

with cte as
(
  select c.persnr,
         c.lastchecked,
         d.ID_Card,
         d.License     as License1, -- license
         d.License+'2' as License2, -- valid_till
         d.License+'3' as License3, -- valid_from
         d.License+'4' as License4, -- details_description
         d.Valid_till,
         d.Valid_from,
         d.Details_description
  from cards c
  join details d
    on d.ID_Card = c.id
)
select p4.persnr,
       p4.lastchecked,
       p4.ID_Card,
       max(p4.A )  as LicenseA,
       max(p4.A2)  as Valid_tillA,
       max(p4.A3)  as Valid_fromA,
       max(p4.A4)  as DescriptionA,
       max(p4.B )  as LicenseB,
       max(p4.B2)  as Valid_tillB,
       max(p4.B3)  as Valid_fromB,
       max(p4.B4)  as DescriptionB,
       max(p4.C )  as LicenseC,
       max(p4.C2)  as Valid_tillC,
       max(p4.C3)  as Valid_fromC,
       max(p4.C4)  as DescriptionC,
       max(p4.CE ) as LicenseCE,
       max(p4.CE2) as Valid_tillCE,
       max(p4.CE3) as Valid_fromCE,
       max(p4.CE4) as DescriptionCE
from cte
pivot (max(cte.License1)           for cte.License1 in ([A],  [B],  [C] , [CE])) p1
pivot (max(p1.Valid_till)          for p1.License2  in ([A2], [B2], [C2], [CE2])) p2
pivot (max(p2.Valid_from)          for p2.License3  in ([A3], [B3], [C3], [CE3])) p3
pivot (max(p3.Details_description) for p3.License4  in ([A4], [B4], [C4], [CE4])) p4
group by p4.persnr,
         p4.lastchecked,
         p4.ID_Card;

Result

Edit: Original result for the first 4 data samples.

persnr lastchecked ID_Card LicenseA Valid_tillA Valid_fromA DescriptionA LicenseB Valid_tillB Valid_fromB DescriptionB LicenseC Valid_tillC Valid_fromC DescriptionC
------ ----------- ------- -------- ----------- ----------- ------------ -------- ----------- ----------- ------------ -------- ----------- ----------- ------------
00123  2020-10-21  1061    null     null        null        null         null     null        null        null         C        2024-03-16  2018-09-11  84532
00123  2020-10-21  1061    null     null        null        null         B        2025-02-19  2018-04-01  79064        null     null        null        null
00123  2020-10-21  1061    A        2022-01-01  2018-01-01  95389        null     null        null        null         null     null        null        null
00124  2020-10-21  1062    A        2021-03-15  2019-11-11  83499        null     null        null        null         null     null        null        null

Edit: Current result with expanded sample data and solution.

persnr lastchecked ID_Card LicenseA Valid_tillA Valid_fromA DescriptionA LicenseB Valid_tillB Valid_fromB DescriptionB LicenseC Valid_tillC Valid_fromC DescriptionC LicenseCE Valid_tillCE Valid_fromCE DescriptionCE
------ ----------- ------- -------- ----------- ----------- ------------ -------- ----------- ----------- ------------ -------- ----------- ----------- ------------ --------- ------------ ------------ -------------
00123  2020-10-21  1061    A        2022-01-01  2018-01-01  95389        B        2025-02-19  2018-04-01  79064        C        2024-03-16  2018-09-11  84532        null      null         null         null
00124  2020-10-21  1062    A        2021-03-15  2019-11-11  83499        null     null        null        null         null     null        null        null         CE        2022-04-01   2020-03-31   NewLicense

Fiddle to see it in action.
Edit: updated fiddle for license CE.