RE: SQL Collapse multiple Rows into one

Ron,

Carel shows one way that will work. Here a couple of articles that might
help you to understand and give you a tool to help do it.

http://www.itjungle.com/mpo/mpo032703-story01.html

http://www.itjungle.com/fhg/fhg052505-story01.html

The 032703 story does a nice job of laying out the general idea and
description of how to do what you need.

Mike


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Carel Teijgeler
Sent: Wednesday, June 27, 2007 12:26 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Fwd: SQL Collapse multiple Rows into one

Ron,

Something like

WITH tmp AS(
SELECT plant, itmid, yr, qtr, CASE WHEN mon = 1 THEN units ELSE 0
END AS Month1,
CASE WHEN mon = 2 THEN units ELSE 0 END AS Month2,
etc
FROM file
)
SELECT plant itmid, yr, qtr, SUM(month1) AS month1, SUM( month2) AS month2,
etc FROM tmp GROUP BY plant,itmid, yr, qtr ORDER BY plant, itmd, yr, qtr

This is untested, but should end up with something you would like.

Regards,
Carel Teijgeler

*********** BEGIN FORWARDED MESSAGE ***********

On 26-6-2007 at 15:29 Ron Adams <rondadams@xxxxxxxxx> wrote:

I have a table with multiple rows of the same data across months
ranging

from 2 or 3 months, up to 24, it varies.

I need to collapse this into a single row of data to insert into a table.

Here's an example of the data,

Plant ITMID YR QTR MON Units
CON 00001-0010 2007 Q02 06 1
CON 00001-0010 2007 Q03 07 1
CON 00001-0010 2007 Q03 08 1
CON 00001-0010 2007 Q03 09 1
CON 00001-0010 2007 Q04 10 1
CON 00189-M003 2007 Q02 06 3
CON 00189-M003 2007 Q03 07 3
CON 00189-M003 2007 Q03 08 3
CON 00189-M003 2007 Q03 09 3

Desired output
Plant ITMID YR QTR MON Units YR QTR

MON Units YR QTR MON Units, etc...

CON 00001-0010 2007 Q02 06 1 2007 Q03 07

1 2007
Q03 1...


--
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.