Re: SQL to create one row from multiple records

Jim

That code does all that you want - it's a little tough to wrap your head around it at first. You will need to add the remaining weeks, but it sums for each item and year the number of each item shipped by each week. It's amazing to see it work, actually!

Maybe take your sample data and run it just for weeks 1 to 5 and see what happens on a small scale.

No summarizing needed - one of the secrets is the GROUP BY, which is already a summarizing, aggregate technique.

I'm thinking of a different way to say it - it adds up the shipped counts for week 1 only into week01 - the "case when week=1" uses those values only for that column.

HTH
Vern

On 3/31/2011 5:07 PM, Jim Essinger wrote:

Eric,

I wish I could be that succinct and clear! Yes, that is what I want to do.
It looks like the code you provided would give me a one for one record, then
I would have summarize the resulting table?

Thanks,

Jim

On Thu, Mar 31, 2011 at 3:51 PM, DeLong, Eric<EDeLong@xxxxxxxxxxxxxxx>wrote:

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.

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