SQL gurus!
Archive reference:
http://archive.midrange.com/midrange-l/201004/msg00143.html
I am always looking to expand my SQL knowledge and experience. I saw in the
above archive that there were some suggestions for taking data such as below
(described in CSV format, but actually an IBM i table) and converting it to
a one line record for each key pair.
Item,YEAR,WEEK,Shipped
1,2008,26,80
1,2008,27,78
1,2008,28,43
1,2008,29,64
1,2008,30,84
1,2008,31,41
1,2009,2,50
1,2009,5,9
1,2009,7,13
1,2009,8,35
1,2009,48,46
1,2009,49,76
1,2009,51,52
1,2009,52,60
1,2009,53,30
2,2009,1,76
2,2009,2,96
2,2009,13,77
2,2009,24,91
2,2009,25,90
3,2009,16,86
3,2009,17,144
3,2009,18,47
4,2009,19,77
4,2009,20,21
5,2009,21,105
5,2009,22,100
5,2009,23,75
5,2009,24,94
5,2009,25,73
1,2010,32,55
1,2010,33,69
1,2010,34,126
The archived thread talked about concatenating into a variable field. I need
to keep the numbers separate.
I want to use an SQL statement to create one record for each item and year,
then fill in the shipped number for the appropriate weeks, 1 through 54. SQL
translates a date to a week number between 1 and 54. I have summerized the
existing file down to item, year, week, and shipped numbers. If a week is
missing a record, the amount should be zero for that week bucket.
Again described as CSV but actually an IBM i table.
item,year,week01,week02,wek03, ..... ,week51,week52,week53,week54
1,2008,0,0,0, ... ,80,78,43, 64,84,41, ....
1,2009,0,2,0,0,9,0,13,35, ...... ,46,76,52,60,30,0
1,2010,0,0,0, ... ,55.69.126, ....
2,2009,76,96, ... ,77,0, ... ,91,90, ...,
3,2009, ... ,86,144,47, ...
4,2009, ... ,77,21, ...
5,2009, ... ,105,100,75,94,73, ...
Is there a somewhat easy way to do this with an SQL statement or UDF, or am
I going to have to write a program to do this?
Thanks!
Jim
midrange.com
