Help to Normalize table!! - 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 2008 Forums

Transact-SQL (2008)

Help to Normalize table!!

Author

Topic

LyLy
Starting Member

1 Post

Posted - 2011-11-08 : 11:33:34

i have a big trouble.. i have this schema on a table


id year  var   jan   feb ...... etc.
1  2008  var1  123   NULL
2  2008  var2  678   87

and i need to change that schema to

id  year  month  var1  var2
1   2008  01     123   678
1   2008  02     NULL   87

i tried with unpivot..

Select id,year, month, var1
from (select id, year, jun, feb .. etc from X
where var='var1') p
UNPIVOT
(var1 for mont in(jun, feb...etc)) as unpvt

but it was 51 selects and 51 temp tables to store the data in order(i have 51 var) and then a big select~join in order to remake the table >.< and takes a rly big time to make that operation.. so any good ideas for my problem??..

btw sorry for my bad english

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 12:29:14

[code]
select id,year,[Month],[1] AS var1,[2] AS var2,...,[51] AS var51
from
(
select id, year,Val,
case MonthName
when 'jan' then '01'
when 'Feb' then '02'
..
when 'dec' then '12'
end AS [Month]
,row_number() over (partition by id, year,MonthName order by id asc) as rn
from table t
unpivot(val for MonthName in (jan,feb,...,dec))u
)m
pivot (sum(val) for rn in ([1],[2],....,[51]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources