| View previous topic :: View next topic |
| Author |
Message |
lacoe1
Beginner
Joined: 24 Feb 2005
Posts: 33
Topics: 17
|
 Posted: Mon May 08, 2006 4:36 pm Post subject: SQL to form Columns from Rows |
 |
|
Hello, I have the following situation.
If I select the Pay information of a person for a period (10/1 -10/31) from one of the tables the results are like this
| Code: |
SSN PY_END_DT Gross-to-net Amt
---- ----------- ----------- -----
999 10/31/2005 SWT 50.00
999 10/31/2005 FWT 1200.00
999 10/31/2005 GPY 700.00
|
But I want it display as follows
| Code: |
SSN PY_END_DT SWT FWT GPY
---- -------------- ----- ---- ----
999 10/31/2005 50.00 1200.00 700.00
|
How do I convert the output rows and display as columns in SPUFI? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Mon May 08, 2006 7:01 pm Post subject: |
|
|
lacoe1,
Assuming that the AMT column is defined as Decimal, the following sql will give you the desired results.
| Code: |
SELECT SSN
,PY_END_DT
,SUM(CASE GROSS_TO_NET
WHEN 'SWT' THEN AMT
ELSE DECIMAL(0)
END) AS SWT
,SUM(CASE GROSS_TO_NET
WHEN 'FWT' THEN AMT
ELSE DECIMAL(0)
END) AS FWT
,SUM(CASE GROSS_TO_NET
WHEN 'GPY' THEN AMT
ELSE DECIMAL(0)
END) AS GPY
FROM Table
GROUP BY SSN
,PY_END_DT
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
lacoe1
Beginner
Joined: 24 Feb 2005
Posts: 33
Topics: 17
|
| Posted: Mon May 08, 2006 8:24 pm Post subject: |
|
|
| Thanks Kolusu |
|
| Back to top |
|
|
astro
Beginner
Joined: 05 Oct 2005
Posts: 31
Topics: 7
|
| Posted: Fri May 12, 2006 4:44 am Post subject: |
|
|
Hi Kolusu,
Just a doubt.
Do we really need SUM function here ?
I think without SUM function also we can get the desired result of lacoe1.
Pl. explain |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri May 12, 2006 7:32 am Post subject: |
|
|
| Quote: |
I think without SUM function also we can get the desired result of lacoe1.
|
Astro,
The objective here is to get one 1 row from 3 rows. If I did not use the sum then you will have 3 rows as shown below. The case statement is used to pad zero.
| Code: |
-+---------+---------+---------+---------+---------+---------+-----
SSN PY_END_DT SWT FWT GPY
-+---------+---------+---------+---------+---------+---------+-----
999 2005-10-31 50.00 .00 .00
999 2005-10-31 .00 .00 700.00
999 2005-10-31 .00 1200.00 .00
|
Once you use the sum grouping on the ssn and py_end_dt you will have the results as follows
| Code: |
-+---------+---------+---------+---------+---------+---------+---------+
SSN PY_END_DT SWT FWT GPY
-+---------+---------+---------+---------+---------+---------+---------+
999 2005-10-31 50.00 1200.00 700.00
|
Hope this clears your doubts
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
astro
Beginner
Joined: 05 Oct 2005
Posts: 31
Topics: 7
|
| Posted: Fri May 12, 2006 7:50 am Post subject: |
|
|
Hi Kolusu,
Thanks for your nice explanation |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
