need to colapse dup rows to one row in sql qry

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

need to colapse dup rows to one row in sql qry

Author

Topic

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2011-02-28 : 15:53:48

I am having a little difficulty creating a query the shows one row for different time periods. How do I colapse the duplicate rows into one row but retain the correct data???

Any help or direction would be appreciated.

Thanks, Don

_-------------------------------
here's my results:

PAY_TYPE................FY07-08........FY08-09
Check.....................NULL...........221760439.9
Check.....................1408330.55.....NULL
IVR - Discover, Visa...NULL...........67056639.7
Master Card.............NULL...........29492736.21
Master Card............251114.58......NULL
Unknown pay type....NULL...........28140522.24
Visa Card................NULL...........48721255.49
Visa Card................363249.49......NULL

Here's my sql script:

SELECT PAY_TYPE,

CASE
WHEN FISCAL_YEAR = '2008' THEN SUM(PAYMENT_AMOUNT)
END as 'FY07-08',

CASE
WHEN FISCAL_YEAR = '2009' THEN SUM(PAYMENT_AMOUNT)
END as 'FY08-09'

FROM dbo.vw_PAYMENTS_STUBS

GROUP BY PAY_TYPE, FISCAL_YEAR

shaunhendrix
Starting Member

1 Post

Posted - 2011-02-28 : 17:09:04

As a quick response you might want to look into the PIVOT statement on BOL. This should give you what you need.

If you're struggling let me know and i'll try and post the full code (bit pushed for time now).

ShaunHendrix

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2011-02-28 : 17:31:46

Pivot's??? YIKES!!! i have spent millions of hours trying to get a pivot to run, only to cut&paste into Excel and pivot there. I'd love for more of your input, I can patiently wait when you have more time.

thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-28 : 17:48:42

Don't GROUP BY Fiscal_Year.

PIVOT does take some getting used to, but once you get it it's really handy. UNPIVOT is da bomb, much easier than other techniques.

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2011-02-28 : 17:53:05

BTW: I tried to create a PIVOT, but i get Msg 207, Level 16, State 1, Line 1
Invalid column name 'PAY_TYPE' - I can never get these PIVOT's to work.
...again, all help is appreciated!
here's my script:

SELECT PAY_TYPE as METHOD, [TAX_COLLECTED] as TAX_COLLECTED
FROM
(SELECT PAY_TYPE, PAYMENT_AMOUNT
FROM dbo.vw_PAYMENTS_STUBS) ps
PIVOT
(
SUM (PAYMENT_AMOUNT)
FOR PAY_TYPE IN
( [TAX_COLLECTED])
) AS pvt

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2011-02-28 : 17:55:32

I removed FISCAL_YEAR from my group by and recieved the following error msg:

Msg 8120, Level 16, State 1, Line 4
Column 'dbo.vw_PAYMENTS_STUBS.FISCAL_YEAR' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-28 : 17:56:55

SELECT PAY_TYPE,
SUM(CASE WHEN FISCAL_YEAR = '2008' THEN PAYMENT_AMOUNT
END) as 'FY07-08',
SUM(CASE WHEN FISCAL_YEAR = '2009' THEN PAYMENT_AMOUNT
END) as 'FY08-09'
FROM dbo.vw_PAYMENTS_STUBS
GROUP BY PAY_TYPE

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2011-02-28 : 18:02:27

To Robvolk: Seriously!! that was it? you are awesome! thanks!!!

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2011-02-28 : 19:11:57

IVR - Discover, Visa NULL NULL
Check NULL NULL
Master Card NULL NULL
Visa Card NULL NULL
Unknown pay type NULL NULL

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2011-02-28 : 19:11:57

IVR - Discover, Visa NULL NULL
Check NULL NULL
Master Card NULL NULL
Visa Card NULL NULL
Unknown pay type NULL NULL

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2011-02-28 : 19:11:57

IVR - Discover, Visa NULL NULL
Check NULL NULL
Master Card NULL NULL
Visa Card NULL NULL
Unknown pay type NULL NULL

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2011-02-28 : 19:11:58

IVR - Discover, Visa NULL NULL
Check NULL NULL
Master Card NULL NULL
Visa Card NULL NULL
Unknown pay type NULL NULL

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2011-02-28 : 19:11:58

IVR - Discover, Visa NULL NULL
Check NULL NULL
Master Card NULL NULL
Visa Card NULL NULL
Unknown pay type NULL NULL

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2011-02-28 : 19:16:47

getting closer- my script:

SELECT PAY_TYPE, [0] as 'FY07-08', [1] as 'FY08-09'
FROM
(SELECT PAY_TYPE, PAYMENT_AMOUNT, PAY_TYPE_CODE
FROM dbo.vw_PAYMENTS_STUBS ) ps
PIVOT
(
SUM(ps.PAYMENT_AMOUNT)
FOR PAY_TYPE_CODE IN
([0],[1])
) AS pvt
---------------------
However getting NULLS instead of sum(Payment_Amounts) - my results:
PAY_TYPE FY07-08 FY08-09
IVR - Discover, Visa NULL NULL
Check NULL NULL
Master Card NULL NULL
Visa Card NULL NULL
Unknown pay type NULL NULL

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-28 : 21:23:12

SELECT PAY_TYPE, [2008] as 'FY07-08', [2009] as 'FY08-09'
FROM
(SELECT PAY_TYPE, PAYMENT_AMOUNT, PAY_TYPE_CODE, FISCAL_YEAR
FROM dbo.vw_PAYMENTS_STUBS ) ps
PIVOT
(
SUM(ps.PAYMENT_AMOUNT)
FOR FISCAL_YEAR IN
([2008],[2009])
) AS pvt

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2011-03-02 : 19:08:06

Thanks robvolk: your help has helped me understand pivots and how they work...

thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources