Not sure how to get all data on one line

Question

Not sure how to get all data on one line

blakkheartt12

Grasshopper

Points: 17
More actions
May 16, 2017 at 6:50 am

#321415

Hello everyone,
I'm still getting my feet wet in SQL but I'm not sure how to proceed to correct this query:
SELECT DISTINCT RTRIM(MAS.ID) AS "EMPLOYEE ID" --,RTRIM(MAS.SSN) AS "SSN" ,RTRIM(MAS.BDT) AS "DATE OF BIRTH" ,CASE WHEN PXD.PYX_NO = 2111 THEN RTRIM(PXD.PYX_TTD/100) ELSE NULL END AS "PD DEFERRED COMP" ,CASE WHEN PXD.PYX_NO = 2121 THEN RTRIM(PXD.PYX_TTD/100) ELSE NULL END AS "FR DEFERRED COMP" ,CASE WHEN PXD.PYX_NO = 2130 THEN RTRIM(PXD.PYX_TTD/100) ELSE NULL END AS "MASS MUTUAL" ,CASE WHEN PXD.PYX_NO = 2131 THEN RTRIM(PXD.PYX_TTD/100) ELSE NULL END AS "VLAC" ,CASE WHEN PXD.PYX_NO = 2010 THEN RTRIM(PXD.PYX_TTD/100) ELSE NULL END AS "ALT SSN CONTRIBUTION" ,CASE WHEN PXD.PYX_NO = 6003 THEN RTRIM(PXD.PYX_TTD/100) ELSE NULL END AS "W2 WAGES"
FROM HR_EMPMSTR MAS JOIN PYX_XTD_DTL PXD on mas.id = pxd.hr_pe_id
WHERE PXD.PYX_YY = '2016' and mas.hr_status in ('A','L') and mas.stat = 'A' AND PXD.PYX_TTD > 0 AND MAS.ID = 'E00745'
GROUP BY MAS.ID,MAS.SSN,MAS.BDT,PXD.PYX_TTD, pxd.pyx_no
It ends up pulling data, but separates it on separate lines:
How do I make it all appear on one line?
Thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99143 More actions · Answer 1

What are you trying to achieve here? The reason you're getting 3 rows is because there are 3 rows of data.

You have a GROUP BY in your query on every field, but no aggregate functions and a DISTINCT, so I would hazard a guess that you don't know how either GROUP BY and DISTINCT really work. Perhaps it would be easier if you explained from the start, with the data as it is before you query, and what you're expecting your output to look like. There's a link in my signature on how to include DDL and sample data, which will help us greatly in answering your question. 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Wild guess:
Use aggregate functions.
SELECT DISTINCT RTRIM(MAS.ID) AS "EMPLOYEE ID" --,RTRIM(MAS.SSN) AS "SSN" ,RTRIM(MAS.BDT) AS "DATE OF BIRTH" ,MAX(CASE WHEN PXD.PYX_NO = 2111 THEN RTRIM(PXD.PYX_TTD/100) END) AS "PD DEFERRED COMP" ,MAX(CASE WHEN PXD.PYX_NO = 2121 THEN RTRIM(PXD.PYX_TTD/100) END) AS "FR DEFERRED COMP" ,MAX(CASE WHEN PXD.PYX_NO = 2130 THEN RTRIM(PXD.PYX_TTD/100) END) AS "MASS MUTUAL" ,MAX(CASE WHEN PXD.PYX_NO = 2131 THEN RTRIM(PXD.PYX_TTD/100) END) AS "VLAC" ,MAX(CASE WHEN PXD.PYX_NO = 2010 THEN RTRIM(PXD.PYX_TTD/100) END) AS "ALT SSN CONTRIBUTION" ,MAX(CASE WHEN PXD.PYX_NO = 6003 THEN RTRIM(PXD.PYX_TTD/100) END) AS "W2 WAGES"

FROM HR_EMPMSTR MAS JOIN PYX_XTD_DTL PXD on mas.id = pxd.hr_pe_id WHERE PXD.PYX_YY = '2016' and mas.hr_status in ('A','L') and mas.stat = 'A' AND PXD.PYX_TTD > 0 AND MAS.ID = 'E00745'

GROUP BY MAS.ID,MAS.SSN,MAS.BDT

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

blakkheartt12 Grasshopper Points: 17 More actions · Answer 3

Thom A - Tuesday, May 16, 2017 6:58 AM
What are you trying to achieve here? The reason you're getting 3 rows is because there are 3 rows of data.
You have a GROUP BY in your query on every field, but no aggregate functions and a DISTINCT, so I would hazard a guess that you don't know how either GROUP BY and DISTINCT really work. Perhaps it would be easier if you explained from the start, with the data as it is before you query, and what you're expecting your output to look like. There's a link in my signature on how to include DDL and sample data, which will help us greatly in answering your question. 🙂

Hi Thom,

This is what I was expecting to have, just one row of data per person with the appropriate data where data is > 0:

The group by was there because originally I had created a statement that was erroring due to the group by was needed. I put distinct there because it was originally bring in multiple rows of duplicate data.

Thanks