Re: SQL Pivot

[Pete Hall <pbhall@xxxxxxxxxx>]

At 10:15 02/01/2000 , you wrote:

>Somewhere, a long time ago, I read a message detailing how to write an SQL 
>statement that would do something on the order of an Excel Pivot 
>function.  For example, I would like to print a year's worth of 
>transactional data in 12 monthly columns - across the page, not down.

Here's a view that pivots cost type records based on a record type code. As 
SQL processes each row, the cost gets added to the appropriate cost type 
bucket. Zero is added to all other buckets.

CREATE VIEW %LIB%/"Costs"
    (BUSINESS_UNIT FOR ACOMCU,
     CATALOG_ID FOR ACOAITM,
     SHORT_ITEM_ID FOR ACOITM,
     LONG_ITEM_ID FOR ACOLITM,
     COST_METHOD FOR ACOLEDG,
     COST_LABOR_DIRECT           FOR B1IECSL,
     COST_LABOR_SCRAP            FOR XBIECSL,
     COST_LABOR_INDIRECT         FOR B2IECSL,
     COST_MATERIAL_METAL         FOR A1IECSL,
     COST_MATERIAL_SCRAP         FOR XAIECSL,
     COST_MATERIAL_YIELD         FOR XEIECSL,
     COST_VARIABLE_OH_OUTSOURCE  FOR C1IECSL,
     COST_VARIABLE_OH_MACHINE    FOR C2IECSL,
     COST_VARIABLE_OH_LABOR      FOR C3IECSL,
     COST_VARIABLE_OH_SCRAP      FOR XCIECSL,
     COST_VARIABLE_OH_OUTSOURCE2 FOR D1IECSL,
     COST_FIXED_OH_LABOR         FOR C4IECSL,
     COST_FIXED_OH_SCRAP         FOR XDIECSL)
  AS SELECT
     A.COMCU,
     A.COAITM,
     A.COITM,
     A.COLITM,
     A.COLEDG,
     DECIMAL(SUM(
        CASE B.IECOST WHEN 'B1 ' THEN IFNULL(B.IECSL,0) ELSE 0 END
        )*.0001,15,4),
     DECIMAL(SUM(
        CASE B.IECOST WHEN 'XB ' THEN IFNULL(B.IECSL,0) ELSE 0 END
        )*.0001,15,4),
     DECIMAL(SUM(
        CASE B.IECOST WHEN 'B2 ' THEN IFNULL(B.IECSL,0) ELSE 0 END
        )*.0001,15,4),
     DECIMAL(SUM(
        CASE B.IECOST WHEN 'A1 ' THEN IFNULL(B.IECSL,0) ELSE 0 END
        )*.0001,15,4),
     DECIMAL(SUM(
        CASE B.IECOST WHEN 'XA ' THEN IFNULL(B.IECSL,0) ELSE 0 END
        )*.0001,15,4),
     DECIMAL(SUM(
        CASE B.IECOST WHEN 'XE ' THEN IFNULL(B.IECSL,0) ELSE 0 END
        )*.0001,15,4),
     DECIMAL(SUM(
        CASE B.IECOST WHEN 'C1 ' THEN IFNULL(B.IECSL,0) ELSE 0 END
        )*.0001,15,4),
     DECIMAL(SUM(
        CASE B.IECOST WHEN 'C2 ' THEN IFNULL(B.IECSL,0) ELSE 0 END
        )*.0001,15,4),
     DECIMAL(SUM(
        CASE B.IECOST WHEN 'C3 ' THEN IFNULL(B.IECSL,0) ELSE 0 END
        )*.0001,15,4),
     DECIMAL(SUM(
        CASE B.IECOST WHEN 'XC ' THEN IFNULL(B.IECSL,0) ELSE 0 END
        )*.0001,15,4),
     DECIMAL(SUM(
        CASE B.IECOST WHEN 'D1 ' THEN IFNULL(B.IECSL,0) ELSE 0 END
        )*.0001,15,4),
     DECIMAL(SUM(
        CASE B.IECOST WHEN 'C4 ' THEN IFNULL(B.IECSL,0) ELSE 0 END
        )*.0001,15,4),
     DECIMAL(SUM(
        CASE B.IECOST WHEN 'XD ' THEN IFNULL(B.IECSL,0) ELSE 0 END
        )*.0001,15,4)
  FROM F4105 A
    JOIN F30026 B ON
      A.COITM = B.IEITM
      AND A.COMCU = B.IEMMCU
      AND A.COLEDG = B.IELEDG
  GROUP BY
     A.COMCU,
     A.COLEDG,
     A.COITM,
     A.COAITM,
     A.COLITM

hth
Pete

Pete Hall
pbhall@execpc.com
http://www.execpc.com/~pbhall
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---