CASE expression? 1

[Freemo]

Hi,

I am trying to create a temp table in SQL Server 2000 from an existing table. The existing table has the following structure:
Transaction_ID Tran_Breakdown_ID Amount
0001 Net £25
0001 Tax_IPT £1.50
0001 Fee £15

The Net, tax_IPT and fee entries all relate to the same transaction. In a new table i want to display as follows:
Transaction_ID Net Fee Tax_IPT
0001 £25 £15 £1.50

I thought i would need to use a CASE expression to acheive this but i cannot get it to work. Is anyone able to advise?

Many thanks
Justin

 

[memdiscount]

Hi ;

Transaction_ID Net Fee Tax_IPT
0001 £25 £15 £1.50


SELECT
Transaction_ID ,
CASE WHEN Tran_Breakdown_ID = 'Net' THEN Amount END AS 'Net' ,

CASE WHEN Tran_Breakdown_ID = 'Fee' THEN Amount END AS 'Fee' ,

CASE WHEN Tran_Breakdown_ID = 'Tax_IPT' THEN Amount END AS 'Tax_IPT'

FROM TABLE1

I hope it will work. I can't test it here ! But, I hope ! Let me know if it works.

Thanks

 

[Freemo]

Thanks for the sppedy repsonse.
That very nearly does what i want but i end up with
Transaction_ID Net Fee Tax_IPT
0001 Null Null £1.50
0001 £25 Null Null
0001 Null £15 Null

Just to complicate matters further i have just realised i need to join TABLE1 to TABLE2 via transaction_id because TABLE2 contains a created date field and i need to input a date range against that field.

 

[philhege]

This gets you the result set:

SELECT DISTINCT 
     Transaction_ID , 
     (SELECT SUM(COALESCE(amount,0)) FROM transactions b WHERE b.transaction_id = a.transaction_id AND 
		tran_breakdown_ID = 'Net' )AS    'Net' , 

     (SELECT SUM(COALESCE(amount,0)) FROM transactions b WHERE b.transaction_id = a.transaction_id AND
		tran_breakdown_ID = 'Fee' )AS    'Fee' ,  

     (SELECT SUM(COALESCE(amount,0)) FROM transactions b WHERE b.transaction_id = a.transaction_id AND
		tran_breakdown_ID = 'Tax_IPT' )AS    'Tax_IPT'   

FROM transactions a

I'm using derived tables and joining them implictly to the outer SELECT using alii.

Let us know if you want more explanation.

You can join your date information to this statement.

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[eerlee]

hi, this should do it.

SELECT
Transaction_ID
,SUM(CASE WHEN Tran_Breakdown_ID = 'Net' THEN Amount ELSE 0 END) AS Net
,SUM(CASE WHEN Tran_Breakdown_ID = 'Fee' THEN Amount ELSE 0 END) AS Fee
,SUM(CASE WHEN Tran_Breakdown_ID = 'Tax_IPT' THEN Amount ELSE 0 END) AS Tax_IPT
FROM TableName
GROUP BY Transaction_ID

 

[philhege]

Thanks eerlee. Once again I took the long way home.

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.