Solved

SQL assist - hard to difficult - rows to columns

Posted on 2011-09-19
4
226 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

749 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