Multiple rows (but I need only one!)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple rows (but I need only one!)

Author

Topic

verkley
Starting Member

3 Posts

Posted - 2012-11-04 : 09:50:52

I have a query that includes a sum. I get multiple rows back, but I only need one row! Can the query be done with only one row and all of the vallues back?

This is the query, and below is the resultset:

select DISTINCT
DPIC300.PERS_NR
, DPIC300.DV_VLGNR
, SUM(CASE WHEN DPID027.vrlfr_mut_kd = 'FIET' THEN DPID027.VRLFR_MUT_UREN ELSE 0 END) OVER (PARTITION BY DPID027.vrlfr_mut_kd) as AG
, SUM(CASE WHEN DPID027.vrlfr_mut_kd = 'PENS' THEN DPID027.VRLFR_MUT_UREN ELSE 0 END) OVER (PARTITION BY DPID027.vrlfr_mut_kd) as AH
from DPIC300
LEFT OUTER JOIN DPID027
on DPIC300.PERS_NR = DPID027.PERS_NR
and DPIC300.DV_VLGNR = DPID027.DV_VLGNR
WHERE dpic300.pers_nr = 1365

GROUP BY DPIC300.pers_nr, DPIC300.dv_vlgnr, DPID027.vrlfr_mut_kd, DPID027.vrlfsrt_kd, DPID027.VRLFR_MUT_UREN

PERS_NR DV_VLGNR AG AH
---------- -------- --------------------------------------------------- ---------------------------------------------------
1.365 1 -10,0000 0,0000
1.365 1 0,0000 -12,0000
1.365 1 0,0000 0,0000

[3 row(s) returned]

[Execution completed]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-04 : 11:31:38

do you mean this?

if not post some sample data and show your output


select DISTINCT
DPIC300.PERS_NR
, DPIC300.DV_VLGNR
, SUM(CASE WHEN DPID027.vrlfr_mut_kd = 'FIET' THEN DPID027.VRLFR_MUT_UREN ELSE 0 END) OVER (PARTITION BY DPID027.vrlfr_mut_kd) as AG
, SUM(CASE WHEN DPID027.vrlfr_mut_kd = 'PENS' THEN DPID027.VRLFR_MUT_UREN ELSE 0 END) OVER (PARTITION BY DPID027.vrlfr_mut_kd) as AH
from DPIC300 
LEFT OUTER JOIN DPID027 
on DPIC300.PERS_NR = DPID027.PERS_NR 
and DPIC300.DV_VLGNR = DPID027.DV_VLGNR 
WHERE dpic300.pers_nr = 1365

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

verkley
Starting Member

3 Posts

Posted - 2012-11-04 : 12:37:56

The output on the above query from visakh16 is: (I don't know how to put it nicely in a readable table for the forum)
Three rowes and there all from the same person (Pers_nr & DV_vlgnr)
PERS_NR DV_VLGNR AG AH
1.365 1 -10,0000 0,0000
1.365 1 0,0000 -12,0000
1.365 1 0,0000 0,0000

The result I am looking for is this:
PERS_NR DV_VLGNR AG AH
1.365 1 -10,0000 -12,0000
The results are all into one row. And there is no NULL or 0. My intention is to add more than just the two collums (AI, AJ and AK must also be added, but has a different DPID027.vrlfr_mut_kd.

A person has only one Pers_nr, but can have more then one DV_vlgnr due to several different Job's at the same employer. In this case there is only one DV_vlgnr.
The table (DPIC300 outer Join table) has a 3371 rows (different Pers_nr and DV_vlgnr).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-04 : 12:49:19

sorry cant make out much from your explanation

please post required info in below format and explain your output

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources