?
Solved

SQL assist - hard to difficult - rows to columns

Posted on 2011-09-19
4
Medium Priority
?
269 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
4 Comments
 
LVL 78

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

571 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