Solved

SQL assist - hard to difficult - rows to columns

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

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
This video shows how to recover a database from a user managed backup

770 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