Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

SQL PIVOT

Almost there... Oracle database. I have two tables, each with an amount field. Each table will have multiple rows for a single Institution and ID and the rows will vary the the TERM field. I can get the top have working so that I get results like this:
ID                  "1112"           "1116"          "1119"
10837862      85428.5            6468
 but what I am trying to get is this:
ID                  "1112"           "1116"          "1119"       "1112"           "1116"          "1119"
10837862      85428.5            6468          72500           1600             5500

where the additional three columns come from the second table. Below I have given the SQL that provides the first example and the SQL that attempts to add the second table. Thank you!

WITH pivot_data AS
  (SELECT EMPLID,
    STRM ,
    NET_AWARD_AMT
  FROM PS_ANTICIPATED_AID
  WHERE institution ='QNS01'
  AND STRM         >='1112'
  AND STRM         <='1119'
    -- GROUP BY EMPLID,
    -- STRM
  )
SELECT                                               *
FROM pivot_data PIVOT ( SUM(NET_AWARD_AMT) FOR STRM IN ('1112','1116','1119'));


WITH pivot_data AS
  (SELECT A.EMPLID,
    A.STRM ,
    A.NET_AWARD_AMT,
    B.ITEM_AMT
  FROM PS_ANTICIPATED_AID A,
    PS_ITEM_SF B
  WHERE A.institution='QNS01'
  AND A.STRM        >='1112'
  AND A.STRM        <='1119'
  AND a.institution  =b.business_unit
  AND A.EMPLID       =b.common_id
  AND A.STRM         =B.ITEM_TERM
  AND a.acad_career  =b.acad_career
    --and a.aid_year=b.aid_year
  AND A.AS_OF_DTTM =
    (SELECT MAX(AS_OF_DTTM)
    FROM PS_ANTICIPATED_AID C
    WHERE C.EMPLID    =A.EMPLID
    AND C.INSTITUTION =A.INSTITUTION
    AND C.ITEM_TYPE   =a.ITEM_TYPE
    AND C.strm        =a.strm
      /*AND C.disb_expire_dt > SYSDATE AND A.AS_OF_DTTM <=SYSTIMESTAMP*/
    )
  )
SELECT *
FROM pivot_data PIVOT ( SUM(NET_AWARD_AMT) FOR STRM IN ('1112','1116','1119')),
 ( SUM(ITEM_AMT) FOR ITEM_TERM IN ('1112','1116','1119'));


0
COBOLforever
Asked:
COBOLforever
  • 3
  • 2
1 Solution
 
Swadhin RaySenior Technical Engineer Commented:
Can you provide some sample data for your two tables ?
0
 
COBOLforeverAuthor Commented:
here ya go - - thank you!
EE-TABLE.xlsx
0
 
COBOLforeverAuthor Commented:
out for the night - - --
0
 
Swadhin RaySenior Technical Engineer Commented:
Try this and check :
SELECT *
FROM
  (SELECT *
  FROM
    (SELECT A.EMPLID,
      A.STRM ,
      a.net_award_amt,
      b.item_amt,
      b.ITEM_TERM
    FROM PS_ANTICIPATED_AID A,
      ITEM_SF B
    WHERE A.institution='QCC01'
    AND A.STRM        >='1112'
    AND A.STRM        <='1119'
    AND a.institution  =b.business_unit
    AND A.EMPLID       =b.common_id
    AND a.strm         =b.item_term
      --AND a.acad_career  =b.acad_career
      --and a.aid_year=b.aid_year
    AND A.AS_OF_DTTM =
      (SELECT MAX(AS_OF_DTTM)
      FROM PS_ANTICIPATED_AID C
      WHERE C.EMPLID    =A.EMPLID
      AND C.INSTITUTION =A.INSTITUTION
      AND C.ITEM_TYPE   =a.ITEM_TYPE
      AND c.strm        =a.strm
      )
    ) pivot (SUM(net_award_amt) FOR (strm) IN (1112, 1116, 1119 ))
  ) t1 PIVOT ( SUM(ITEM_AMT) FOR ITEM_TERM IN ('1112','1116','1119')) ;

Open in new window

0
 
COBOLforeverAuthor Commented:
Perfect. Thanks much!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now