Several/Multiplie table to one new

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Several/Multiplie table to one new

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-03-17 : 04:07:24

Hi

I want to combine a several tables to a new table
So its look like below. The datetime field must be sync together
and the new table should have all these columns in it.

Table 1

DateTime | col 1 | col 2 | col 3
2007.01.01 00:00 | 4.4 | 9 | 5
2007.01.02 00:00 | 2.5 | 11 | 7.2

Table 2

DatumTid | col 4| col 5 | col 6
2007.01.01 00:00 | 1.3 | 9 | 5
2007.01.02 00:00 | 7.5 | 4 | 9

Table 3

DateTime | Col 7 | Col 8
2007.01.01 00:00 | 0 |4
2007.01.02 00:00 | null | null
2007.01.03 00:00 | 3 | 1

Where 'col n' is the columnname, n are the number.

New Table shold than look like this

DateTime | Col 1 |Col 2 |Col 3 |Col 4 |Col 5 |Col 6 |Col 7 |Col 8
2007.01.01 00:00 | 4.4 | 9 | 5 | 1.3 | 9 | 5| 0 |4
2007.01.02 00:00 | 2.5 | 11 | 7.2| 7.5 | 4 | 9 | null | null
2007.01.03 00:00 |null |null|null|null|null|null| 3 | 1

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-03-17 : 04:18:34

A full join between these three tables should suffice.

Something like this:

declare @t1 table
(
	dt datetime,
	col1 float ,
	col2 float,
	col3 float
)

declare @t2 table
(
	dt datetime,
	col4 float ,
	col5 float,
	col6 float
)

declare @t3 table
(
	dt datetime,
	col7 float ,
	col8 float

)

insert @t1
select '20070101', 4.4, 9, 5 union all
select '2007.01.02', 2.5, 11 , 7.2

insert @t2
select '20070101', 1.3 , 9 , 5 union all
select '20070102', 7.5, 4 , 9

insert @t3
select '20070101', 0,4 union all
select '20070102', null, null union all
select '20070103', 3, 1


select coalesce(t1.dt,t2.dt,t3.dt) as dt,
	col1, col2, col3, col4, col5, col6, col7, col8
from @t1 t1 full join @t2 t2 on t1.dt = t2.dt
full join @t3 t3 on t2.dt = t3.dt

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-03-17 : 04:52:29

Thanks!

Maby i got another idea also.
Beacuse i dont want to be dependent of the server that are using.
I really dont want to use T-SQL, even if its elegant.

Now, this should also work

declare @time table
(
dt datetime
)

declare @t1 table
(
dt datetime,
col1 float ,
col2 float,
col3 float
)

declare @t2 table
(
dt datetime,
col4 float ,
col5 float,
col6 float
)

declare @t3 table
(
dt datetime,
col7 float ,
col8 float

)

insert @time
select '20070101'union all
select '20070102' union all
select '20070103'

insert @t1
select '20070101', 4.4, 9, 5 union all
select '20070102', 2.5, 11 , 7.2

insert @t2
select '20070101', 1.3 , 9 , 5 union all
select '20070102', 7.5, 4 , 9

insert @t3
select '20070101', 0,4 union all
select '20070102', null, null union all
select '20070103', 3, 1

SELECT time1.dt,
t1.Col1, t1.Col2, t1.Col3,
t2.Col4, t2.Col5, t2.Col6,
t3.Col7, t3.Col8
FROM @time time1
LEFT OUTER JOIN @t1 t1 ON time1.dt = t1.dt
LEFT OUTER JOIN @t2 t2 ON time1.dt = t2.dt
LEFT OUTER JOIN @t3 t3 ON time1.dt = t3.dt

The result

dt Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8
2007-01-01 00:00:00.000 4,4 9 5 1,3 9 5 0 4
2007-01-02 00:00:00.000 2,5 11 7,2 7,5 4 9 NULL NULL
2007-01-03 00:00:00.000 NULL NULL NULL NULL NULL NULL 3 1

With these you also can control with datetime table
when/which dates we want to collect.
Easy to maintain in some point of view.

”Relationship is surely the mirror in which you discover yourself.” (KRISHNAMURTI)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources