Kris Nobels Kris Nobels - 1 month ago 7
SQL Question

How can I create a pivot table from 3 tables?

I have a problem to visualize adress information.

Example


  • I have 1 person or relation that has more then 1 adress.

  • I store each adress in adress table and define what kind of adress it is.

  • I have 3 adress types as example.



What I need to do is visualize all adress columns next to each other.

If I have only one adress, then the other fields have to show
NULL
because there is no adress.

Here I created a small table setup.

Create table relation(
PKid int identity(1,1) primary key,
Name varchar(255)
)

--Create table Adrestype(
PKid int identity(1,1) primary key,
TypeDescription varchar(255)
)

Create table adres(
PKid int identity(1,1) primary key,
Street varchar(255),
Number varchar(255),
zipcode varchar(255),
Location varchar(255),
AdresTypeId int
)

Create table RelationXAdres(
PKid int identity(1,1) primary key,
RelationID int not null,
adresID int not null
)

Insert into Relation values('Peter');
Insert into Relation values('Nico');
Insert into Relation values('Bart');
Insert into Relation values('Werner');

Insert into Adrestype values('Work');
Insert into Adrestype values('Home');
Insert into Adrestype values('Extra');

Insert into adres values ('Streetname', '125', '5520', 'Gent', 1)
Insert into adres values ('StreetLane', '15', '5550', 'Rome', 2)
Insert into adres values ('Street', '12', '5120', 'Paris', 3)
Insert into RelationXAdres values( 1,1);
Insert into RelationXAdres values( 1,2);
Insert into RelationXAdres values( 1,3);

Insert into adres values ('againstraat', '5', '4420', 'Oslo', 1)
Insert into adres values ('some Street', '12', '2220', 'Praag', 2)
Insert into RelationXAdres values( 2,4);
Insert into RelationXAdres values( 2,5);

Insert into adres values ('SoloStreet', '5', '4420', 'Oslo', 1)
Insert into RelationXAdres values( 3,6);

Insert into adres values ('MainStreet', '25', '1120', 'Berlin', 3)
Insert into RelationXAdres values( 4,7);

-- show all tabel's data
select * from relation
Select * from adres
select * from RelationXAdres
select * from Adrestype

-- Show all data in 1 statement
select * from relation r
left join RelationXAdres ra on ra.RelationID = r.PKid
left join adres a on a.PKid = ra.adresId
left join adrestype at on at.PKid = a.AdresTypeId


This is what the result have to look like:

enter image description here

Answer

Since you are using SQL Server there are several ways that you can pivot the rows of data into columns.

You can use an aggregate function with a CASE expression:

select r.pkid,
  r.name,
  max(case when at.typedescription = 'home' then a.street end) homestreet,
  max(case when at.typedescription = 'home' then a.number end) homeNumber,
  max(case when at.typedescription = 'home' then a.zipcode end) homezipcode,
  max(case when at.typedescription = 'home' then a.location end) homelocation,
  max(case when at.typedescription = 'work' then a.street end) workstreet,
  max(case when at.typedescription = 'work' then a.number end) workNumber,
  max(case when at.typedescription = 'work' then a.zipcode end) workzipcode,
  max(case when at.typedescription = 'work' then a.location end) worklocation,
  max(case when at.typedescription = 'extra' then a.street end) extrastreet,
  max(case when at.typedescription = 'extra' then a.number end) extraNumber,
  max(case when at.typedescription = 'extra' then a.zipcode end) extrazipcode,
  max(case when at.typedescription = 'extra' then a.location end) extralocation
from relation r
left join RelationXAdres ra
  on r.pkid = ra.RelationID
left join adres a
  on ra.adresid = a.pkid
left join adrestype at
  on a.AdresTypeId = at.PKid
group by r.pkid, r.name;

See SQL Fiddle with Demo.

You could apply both the UNPIVOT and the PIVOT function. The UNPIVOT function will take your multiple columns of street, number, zipcode and location and convert them into multiple rows.

select pkid, name,
  col = typeDescription+col,
  value
from
(
  select r.pkid,
    r.name,
    at.typedescription,
    a.street,
    a.number,
    a.zipcode,
    a.location
  from relation r
  left join RelationXAdres ra
    on r.pkid = ra.RelationID
  left join adres a
    on ra.adresid = a.pkid
  left join adrestype at
    on a.AdresTypeId = at.PKid
) d
unpivot
(
  value
  for col in (street, number, zipcode, location)
) unpiv;

See SQL Fiddle with Demo. This gives a result with multiple rows:

| PKID |   NAME |           COL |       VALUE |
-----------------------------------------------
|    1 |  Peter |    Workstreet |  Streetname |
|    1 |  Peter |    Worknumber |         125 |
|    1 |  Peter |   Workzipcode |        5520 |
|    1 |  Peter |  Worklocation |        Gent |
|    1 |  Peter |    Homestreet |  StreetLane |
|    1 |  Peter |    Homenumber |          15 |

Once the data is in the multiple rows, then you can apply the PIVOT function:

;with cte as
(
  select pkid, name,
    col = typeDescription+col,
    value
  from
  (
    select r.pkid,
      r.name,
      at.typedescription,
      a.street,
      a.number,
      a.zipcode,
      a.location
    from relation r
    left join RelationXAdres ra
      on r.pkid = ra.RelationID
    left join adres a
      on ra.adresid = a.pkid
    left join adrestype at
      on a.AdresTypeId = at.PKid
  ) d
  unpivot
  (
    value
    for col in (street, number, zipcode, location)
  ) unpiv
) 
select pkid, name,
  homestreet, homenumber, homezipcode, homelocation,
  workstreet, worknumber, workzipcode, worklocation,
  extrastreet, extranumber, extrazipcode, extralocation
from cte
pivot
(
  max(value)
  for col in (homestreet, homenumber, homezipcode, homelocation,
              workstreet, worknumber, workzipcode, worklocation,
              extrastreet, extranumber, extrazipcode, extralocation)
) p;

See SQL Fiddle with Demo.

The above versions will work great if you have a known number of columns, but if you have an unknown number of values (address types), then you will want to use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(a.TypeDescription+c.col) 
                    from Adrestype a
                    cross apply
                    (
                      select 'street', 1 union all
                      select 'number', 2 union all
                      select 'zipcode', 3 union all
                      select 'location', 4 
                    ) c (col, so)
                    group by a.TypeDescription, c.col, c.so
                    order by case a.TypeDescription
                              when 'home' then 1
                              when 'work' then 2
                              when 'extra' then 3 end, c.so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT pkid, name, ' + @cols + ' 
            from 
            (
                select pkid, name,
                  col = typeDescription+col,
                  value
                from
                (
                  select r.pkid,
                    r.name,
                    at.typedescription,
                    a.street,
                    a.number,
                    a.zipcode,
                    a.location
                  from relation r
                  left join RelationXAdres ra
                    on r.pkid = ra.RelationID
                  left join adres a
                    on ra.adresid = a.pkid
                  left join adrestype at
                    on a.AdresTypeId = at.PKid
                ) d
                unpivot
                (
                  value
                  for col in (street, number, zipcode, location)
                ) unpiv
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo. All of these queries give a result:

| PKID |   NAME |  HOMESTREET | HOMENUMBER | HOMEZIPCODE | HOMELOCATION |  WORKSTREET | WORKNUMBER | WORKZIPCODE | WORKLOCATION | EXTRASTREET | EXTRANUMBER | EXTRAZIPCODE | EXTRALOCATION |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|    1 |  Peter |  StreetLane |         15 |        5550 |         Rome |  Streetname |        125 |        5520 |         Gent |      Street |          12 |         5120 |         Paris |
|    2 |   Nico | some Street |         12 |        2220 |        Praag | againstraat |          5 |        4420 |         Oslo |      (null) |      (null) |       (null) |        (null) |
|    3 |   Bart |      (null) |     (null) |      (null) |       (null) |  SoloStreet |          5 |        4420 |         Oslo |      (null) |      (null) |       (null) |        (null) |
|    4 | Werner |      (null) |     (null) |      (null) |       (null) |      (null) |     (null) |      (null) |       (null) |  MainStreet |          25 |         1120 |        Berlin |