Re: SQL to create one row from multiple records - Revisited

Jim,

Sounds like you want to pivot your weekly counts into week columns?

The only strictly SQL way I know to do this will require a series of CASE
statements for each "week" column you populate. I'll show you the pattern,
and you can fill in the rest...

SELECT DBITEM, DBYEAR,
sum(case when week=1 then shipped end) as week01,
sum(case when week=2 then shipped end) as week02,
sum(case when week=3 then shipped end) as week03,
sum(case when week=4 then shipped end) as week04,
sum(case when week=5 then shipped end) as week05,
...
FROM DBTable
GROUP BY DBITEM, DBYEAR

Hth,
-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Essinger
Sent: Thursday, March 31, 2011 4:32 PM
To: Midrange Systems Technical Discussion
Subject: SQL to create one row from multiple records - Revisited

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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.