CODEWITHSUNDEEP
sqlsql-serverselectreport
Jacek
Jacek

Reputation: 12053

Report with two independent columns

I have 3 tables with data, and I would like to create a report which shows me people with their books and furniture. The biggest problem is to get an independent list with two columns: books and furniture.

create table people (id int primary key)
create table books (id int primary key
   , personId int foreign key references people(id))
create table furnitures (id int primary key
   , personId int foreign key references people(id))
go

insert into people(id)
values (1),(2),(3)
insert into books(id,personId)
values (1,1),(2,1),(3,1),(4,1),(5,1),(6,3),(7,3)
insert into furnitures(id,personId)
values (1,2),(2,2),(3,2),(4,3),(5,3),(6,3),(7,3),(8,3)

I want to get a report in this form:

enter image description here

Upvotes: 1

Views: 104

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You need to do a join but you don't have a key. So, let's create one using row_number(). The rest is just a full outer join to combine the data:

select coalesce(b.personId, f.personId) as personId, b.id as bookid, f.id as furnitureid
from (select b.*, row_number() over (partition by personId order by id) as seqnum
      from books b
     ) b
     full join
     (select f.*, row_number() over (partition by personId order by id) as seqnum
      from furnitures f
     ) f
     on f.personId = b.personId and b.seqnum = f.seqnum;

Upvotes: 2

Related Questions