czwartek, 6 lutego 2014

Tabele przestawne na wielu kolumnach

Spotkałem się dzisiaj z ciekawym problemem. Otóż zadanie wymagało stworzenia tabeli przestawnej na wielu kolumnach.

Rozwiązanie problemu na pierwszy rzut oka niekoniecznie jest sprawą trywialną, ale z pomoca row_number i cte nie takie zagadki idzie rozwiązać.

Struktura tabel wygląda następująco:

Jako wynik oczekiwany jest następujący zbiór danych. Ilość osób na raporcie jest ściśle określona i wynosi 6. Jeżeli ktoś jest zainteresowany dynamicznymi tabelami przestawnymi to zapraszam do lektury mojego starszego wpisu.


Poniżej skrypt który tworzy i wypełnia tabelę. Tak na wszelki wypadek jak ktoś by sobie chciał to przetestować.
use tempdb
go
 
if not exists(select * from sys.tables where name = 'Driver')
create table Driver (
    DriverId int,
    Forename varchar(50),
    Surname varchar(50),
    DOB date
)
 
if not exists(select * from sys.tables where name = 'PolicyDriverLink')
create table PolicyDriverLink (
    DriverId int,
    PolicyId int
)
 
if not exists(select * from sys.tables where name = 'Policy')
create table Policy (
    PolicyId Int,
    PolicyNumber varchar(100)
)
 
if not exists(select * from Policy)
insert into Policy (PolicyId, PolicyNumber) values 
(1, 'POL1/2014'), 
(2, 'POL2/2014'),
(3, 'POL3/2014')
 
if not exists(select * from Driver)
insert into Driver (DriverId, Forename, Surname, DOB) values 
(1, 'John', 'Smith', '01 Jan 1980'), 
(2, 'Alan', 'Banks', '04 Aug 1960'), 
(3, 'Jacob', 'Brown', '10 Dec 1977'),
(4, 'Jessica', 'Anderson', '05 Sep 1984'),
(5, 'Monica', 'Johnson', '22 Oct 1979'),
(6, 'Samantha', 'Adler', '03 Feb 1980')
 
if not exists(select * from PolicyDriverLink)
insert into PolicyDriverLink (DriverId, PolicyId) values
(1,1), (2,1), (3, 1), (4, 1), (5, 1), (6,2), (4,3), (6,3)
Cała magia polega na skorzystaniu z row_number i cte.

Poniżej skrypt umożliwiający uzyskanie porządanego rezultatu.
;with cte as (
    select
    p.PolicyId,
    p.PolicyNumber,
    d.Forename,
    d.Surname,
    d.DOB,
    ROW_NUMBER() over(PARTITION by pdl.PolicyId order by p.PolicyNumber) as rn
    from Policy p
        inner join PolicyDriverLink pdl
            on p.PolicyId = pdl.PolicyId
         inner join Driver d
            on pdl.DriverId = d.DriverId
            
), cte2 as (
    select
        c1.PolicyNumber,
        max(case when c1.rn = 1 then c1.Forename else null end) as D1_Forename,
        max(case when c1.rn = 1 then c1.Surname else null end) as D1_Surname,
        max(case when c1.rn = 1 then c1.DOB else null end) as D1_DOB,
        
        max(case when c2.rn = 2 then c2.Forename else null end) as D2_Forename,
        max(case when c2.rn = 2 then c2.Surname else null end) as D2_Surname,
        max(case when c2.rn = 2 then c2.DOB else null end) as D2_DOB,
        
        max(case when c3.rn = 3 then c3.Forename else null end) as D3_Forename,
        max(case when c3.rn = 3 then c3.Surname else null end) as D3_Surname,
        max(case when c3.rn = 3 then c3.DOB else null end) as D3_DOB,
        
        max(case when c4.rn = 4 then c4.Forename else null end) as D4_Forename,
        max(case when c4.rn = 4 then c4.Surname else null end) as D4_Surname,
        max(case when c4.rn = 4 then c4.DOB else null end) as D4_DOB,
        
        max(case when c5.rn = 5 then c5.Forename else null end) as D5_Forename,
        max(case when c5.rn = 5 then c5.Surname else null end) as D5_Surname,
        max(case when c5.rn = 5 then c5.DOB else null end) as D5_DOB,
        
        max(case when c6.rn = 6 then c6.Forename else null end) as D6_Forename,
        max(case when c6.rn = 6 then c6.Surname else null end) as D6_Surname,
        max(case when c6.rn = 6 then c6.DOB else null end) as D6_DOB
    from cte c1
        left join cte c2
            on c1.PolicyId = c2.PolicyId and c1.rn = 2
        left join cte c3
            on c1.PolicyId = c3.PolicyId and c1.rn = 3
        left join cte c4
            on c1.PolicyId = c4.PolicyId and c1.rn = 4
        left join cte c5 
            on c1.PolicyId = c5.PolicyId and c1.rn = 5
        left join cte c6
            on c1.PolicyId = c6.PolicyId and c1.rn = 6
    group by c1.PolicyId, c1.PolicyNumber
)
 
select
    PolicyNumber, 
    D1_Forename, D1_Surname, D1_DOB,
    D2_Forename, D2_Surname, D2_DOB,
    D3_Forename, D3_Surname, D3_DOB,
    D4_Forename, D4_Surname, D4_DOB,
    D5_Forename, D5_Surname, D5_DOB,
    D6_Forename, D6_Surname, D6_DOB
from cte2

Prześlij komentarz