SQL Challenge

[noslo]

This maybe easy for some but I am fumbling with this. I have a table where a one time sale is broken into several categories. The itemization code identifies the part that is Tax, Doc Fees, Prep Fee, etc. Each category becomes it's own record. See below.

AAD_ID ACD_ID SEQ ITEMIZATION_CODE Amount
10267 5727 8 IUN_5 200
10267 5729 1 IUP_1 1800
10267 5734 6 IUP_6 15
10267 5735 7 IUP_7 15
10267 5736 4 IDC_1 1000
10267 5739 1 IUN_1 7150
10267 5740 2 IUN_2 580
10267 5742 7 IUN_4 69

Based on the AAD_ID, what is the best way to query the data to get each Itemization Code and Amount to show as one record pre AAD_ID for a report? As below:

AAD_ID Code Doc Fees Tax Prep Fee
10267 IUN_5 200 IUP_1 1800 IUP_6 15


Please enlighten me as I will be very grateful for your assistance.

 

[jbenson001]

What is the structure of the other table? You need to Join the 2 tables on something, i would guess the Itemization_code column.

 

[amrita418]

Use this:

select 		aad_id, 
		max(case when itemization_code = 'IUN_5' then itemization_code else ''  end) Code1,
		max(case when itemization_code = 'IUN_5' then amount else 0 end) 'Tax',
		max(case when itemization_code = 'IUP_1' then itemization_code else ''  end) Code2,
		max(case when itemization_code = 'IUP_1' then amount else 0 end) 'Doc Fee',
		max(case when itemization_code = 'IUP_6' then itemization_code else ''  end) Code3,
		max(case when itemization_code = 'IUP_6' then amount else 0 end) 'Prep Fee'
from 		#Table1
group by	aad_id

PS: Assumtion that each AAD has only one entry per code.

Regards,
AA