Explode 1 Record to 12 months - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Explode 1 Record to 12 months

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-02-22 : 11:46:58

I have a file that contains the following fields for months Jan thru Dec:

I am attempting to update a Table that has 1 record per month (Period).
Just wondering if the best way to handle this would be to use the UNION statement and then use the Temp file for the update .

SELECT 1, Qty1, Dol1
FROM tblTest
INTO #Temp
UNION ALL
SELECT 2, Qty2, Dol2
FROM tblTest ..... etc.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-22 : 16:37:11

I hope this is to do away with that denormalized table

But I'd think this is more efficient. Of course you'll have to extend out the remaining columns (4-12) in the 2 case statements.


set nocount on
set ansi_warnings off

create table #tblTest (Qty1 int, Qty2 int, Qty3 int, Dol1 money, Dol2 money, Dol3 money)
insert #tblTest 
select 1,2,3,10,20,30

create table #test (period int, Qty int, Dol money)
insert #test (period)
select 1 union
select 2 union
select 3

select * from #tblTest
select * from #test

update a set
	a.Qty = b.Qty
	,a.Dol = b.Dol
from	#test a
join	(--derived table using tblTest cross joined with a numbers table (12 months)
	select	m as period
		,Qty = case
			when m=1 then Qty1
			when m=2 then Qty2
			when m=3 then Qty3
			end
	
		,Dol = case
			when m=1 then Dol1
			when m=2 then Dol2
			when m=3 then Dol3
			end
	from	#tblTest
	cross join
		(
		select 1 m union select 2 union select 3 union
		select 4 union select 5 union select 6 union
		select 7 union select 8 union select 9 union
		select 10 union select 11 union select 12
		) months
	) b
		on b.period = a.period

print '
After update'
select * from #test
go

drop table #tblTest
drop table #test

OUTPUT:
Qty1        Qty2        Qty3        Dol1                  Dol2                  Dol3                  
----------- ----------- ----------- --------------------- --------------------- --------------------- 
1           2           3           10.0000               20.0000               30.0000

period      Qty         Dol                   
----------- ----------- --------------------- 
1           NULL        NULL
2           NULL        NULL
3           NULL        NULL


After update
period      Qty         Dol                   
----------- ----------- --------------------- 
1           1           10.0000
2           2           20.0000
3           3           30.0000

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources