• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

SQL assist - hard to difficult - rows to columns

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
COBOLforever
Asked:
COBOLforever
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
AlokJain0412Commented:
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
 
COBOLforeverAuthor Commented:
That did it - - thanks much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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