Solved

SQL assist - hard to difficult - rows to columns

Posted on 2011-09-19
4
231 Views
Last Modified: 2012-05-12
Hi experts - - is this enough info to help me convert two distinct rows that get returned to a single row with multiple columns. I particularly need each sum for each term, with the term listed next to its sum.

      select b.national_id, a.emplid, a.account_term,sum(a.line_amt) from ps_item_line_sf a, ps_pers_nid b
      where a.emplid = '10837633' and a.account_term in('1116','1119') and a.emplid = b.emplid
      group by b.national_id, a.emplid, a.account_term

result desired is: national_id, emplid, term 1116, sum for term 1116, term 1119, sum for term 1119
0
Comment
Question by:COBOLforever
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36563747
There is a ton of links out there on this very topic.  Have you not been able to locate any?

The catch is you need to have a fixed number of columns.  You cannot have 1116 and 1119 for one query then 1116, 1119, 2000 the next.  There has to be a defined limit.

That or move to a single delimited column like a CSV.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36563934
try this...

SELECT b.national_id,
       a.emplid,
       SUM(CASE WHEN a.account_term = '1116' THEN a.line_amt END) sum_1116,
       SUM(CASE WHEN a.account_term = '1119' THEN a.line_amt END) sum_1119
  FROM ps_item_line_sf a, ps_pers_nid b
 WHERE a.emplid = '10837633' AND a.account_term IN ('1116', '1119') AND a.emplid = b.emplid
GROUP BY b.national_id, a.emplid
0
 
LVL 5

Accepted Solution

by:
AlokJain0412 earned 500 total points
ID: 36565284
Hi Just try this
and let me know

SELECT national_id, emplid,[1116], [1119]
FROM
(select b.national_id, a.emplid, a.account_term,a.line_amt,a.account_term from ps_item_line_sf a, ps_pers_nid b
      where a.emplid = '10837633' and a.account_term in('1116','1119') and a.emplid = b.emplid
      group by b.national_id, a.emplid, a.account_term) as b

PIVOT
(
sum(line_amt)
FOR account_term IN ([1116], [1119])
) AS PivotTable
0
 

Author Closing Comment

by:COBOLforever
ID: 36921928
That did it - - thanks much.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question