Last post Nov 14, 2017 03:36 PM by eralper

• gunderj

Member

203 Points

419 Posts

### need query to flatten out three simple tables

How can I flatten out data from three tables into one result set that would include all the data?

This sets up three simple tables with data for the purpose of working this problem.

USE [db1]

DROP TABLE [dbo].[tbl1]
DROP TABLE [dbo].[tbl2]
DROP TABLE [dbo].[tbl3]

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl1](
[id] [smallint] IDENTITY(1,1) NOT NULL,
[col1] [nchar](10) NULL
) ON [PRIMARY]
GO
insert into tbl1(col1) values ('a')
insert into tbl1(col1) values ('b')
insert into tbl1(col1) values ('c')
insert into tbl1(col1) values ('d')
insert into tbl1(col1) values ('e')

CREATE TABLE [dbo].[tbl2](
[id] [smallint] IDENTITY(1,1) NOT NULL,
[col2] [nchar](10) NULL
) ON [PRIMARY]
GO
insert into tbl2(col2) values ('aa')
insert into tbl2(col2) values ('bb')
insert into tbl2(col2) values ('cc')

CREATE TABLE [dbo].[tbl3](
[id] [smallint] IDENTITY(1,1) NOT NULL,
[col3] [nchar](10) NULL
) ON [PRIMARY]
GO
insert into tbl3(col3) values ('apple')
insert into tbl3(col3) values ('banana')

The id's all have the same value for their respective row number so this query will flatten the data by using left outer joins

select a.col1,
b.col2,
c.col3
from tbl1 a left outer join
tbl2 b
on (a.id = b.id) left outer join
tbl3 c
on a.id = c.id

The problem with this query is that the table with the highest number of rows must appear first, then the next hiherst and next highest, etc.
I need all the data to appear in the flattened result but if tbl3 had 6 fruits, then the sixth fruit would not be included.

I do not want to get row count for each table and dynamically build the query which is the only way I can see would solve this problem.
How can I flatten in a way that included all the data? Thanks.

• eralper

Contributor

5981 Points

1413 Posts

### Re: need query to flatten out three simple tables

Hello gunderj,

Please check following SQL CTE Select statement where I had to use a SQL numbers table function to eliminate the problem that occurs when tbl1 has less rows than tbl3 or tbl2 as you have mentioned in your post

The main table in FROM clause is the numbers table, so I an guarantee that it is a match for every  row in other 3 tables

Please check the numbers table function I had referenced as a sample. You can use any other you can find on the web

```;with maxids as (
select max(id) id from tbl1
union all
select max(id) id from tbl2
union all
select max(id) id from tbl3
), numberstbl as (
SELECT * FROM dbo.NumbersTable(1,(select max(id) from maxids),1)
)
select
a.col1,
b.col2,
c.col3
from numberstbl
left outer join tbl1 a
on i = a.id
left outer join tbl2 b
on i = b.id
left outer join tbl3 c
on i = c.id
```

Output of the above query will be as follows

col1    col2    col3
a             aa            apple
b             bb            banana
c             cc            pear
d             NULL    watermelon
e             NULL    cherry
NULL    NULL    apricot

I hope this is what you want

Best Regards,