Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

T-SQL using Case syntax but want to get rid of Null value and multi line return

Hi, i am trying to create a ranking system with a table i have.
This is fine. Once i have created the table i get the following results for example (customerid being the PK)

AMount, PostDate, Ranking, Customerid
 12.00    2009-02     1                12
 10.00     2009-03    2                12
 11.50    2009-04     3                 12

So now i want to be able to query this table and say
Case when ranking = 1 then amount End ColumnName ,
Case when ranking = 2 then amoount End ColumnName.
Case when ranking = 3 then amount end columnname

So that my results would look something like this:

12,10,11.50 (All in one row rather then 3 rows)

However i still get back 3 rows but with Null in the fields where it doesn't apply so
New Col,        New col2,    New col 3
NULL             NULL            12
NULL             10.00           NULL
11.50              NULL            NULL

Can any one advise on what i am doing wrong in my select perhaps?


Create table ar_trans(
amount int, 
postdate varchar(20), 
customerid varchar(20))
 
insert into ar_trans values (12.00,'2009-02','12')
insert into ar_trans values (10.00,'2009-03','12')
insert into ar_trans values (11.50,'2009-04','12')
 
--To get my Ranking on this table. 
Select sum(a.amount)amount, a.post_date,a.customer_id, Rank() over (order by a.post_date desc) as 'Ranking'
		From ar_trans  a
		Where a.customer_id = 326081
		And a.amount not like '%-%' 
		Group by a.customer_id,a.post_date  
 
 
 
 
SELECT Case when AR_TRAN.ranking = 1 then AR_TRAN.amount End as Over120,
Case when AR_TRAN.ranking = 2 then AR_TRAN.amount End as Over150,
Case when AR_TRAN.ranking = 3 then AR_TRAN.amount End as Over180 
FROM (Select sum(a.amount)amount, a.post_date,a.customer_id, Rank() over (order by a.post_date desc) as 'Ranking'
		From ar_trans  a
		Where a.customer_id = 326081
		And a.amount not like '%-%' 
		Group by a.customer_id,a.post_date  )AR_TRAN

Open in new window

0
Putoch
Asked:
Putoch
1 Solution
 
BrandonGalderisiCommented:
If you want 3 simple columns...
;with Trans_CTE as (
Select sum(a.amount)amount, a.post_date,a.customer_id, Rank() over (partition by customer_id order by a.post_date desc) as 'Ranking'
		From ar_trans  a
		Where a.customer_id = 12--326081
		And a.amount >0--not like '%-%' 
		Group by a.customer_id,a.post_date  
)
select (select amount from trans_CTE where ranking=1)
     ,(select amount from trans_CTE where ranking=2)
     ,(select amount from trans_CTE where ranking=3)

Open in new window

0
 
pcelbaCommented:
If you need just one row and three columns:
SELECT SUM(Case when AR_TRAN.ranking = 1 then AR_TRAN.amount Else 0 End) as Over120,
SUM(Case when AR_TRAN.ranking = 2 then AR_TRAN.amount Else 0 End) as Over150,
SUM(Case when AR_TRAN.ranking = 3 then AR_TRAN.amount Else 0 End) as Over180 
FROM ...

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need this:

 
SELECT max(Case when AR_TRAN.ranking = 1 then AR_TRAN.amount End) as Over120
, max(Case when AR_TRAN.ranking = 2 then AR_TRAN.amount End) as Over150
, max(Case when AR_TRAN.ranking = 3 then AR_TRAN.amount End) as Over180 
FROM (Select sum(a.amount)amount, a.post_date,a.customer_id, Rank() over (order by a.post_date desc) as 'Ranking'
            From ar_trans  a
            Where a.customer_id = 326081
            And a.amount not like '%-%' 
            Group by a.customer_id,a.post_date  ) AR_TRAN

Open in new window

0
 
PutochAuthor Commented:
Thanks a million
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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