Cross Join Lateral...?

[Viking]

Jim_IT posted a solution in a previous post that included Cross Join Lateral, and I'm wondering if something like that is the answer here too...

I have the following result from my current sample SQL:

Store	Year	Month	Total	Type
01	19	11	28.38	AD
01	19	11	51.00	CC
01	19	11	0.61	PF
01	19	11	68.36	SP

I would like to see this instead:

Store	Year	Month	Total	AD	CC	PF	SP
01	19	11	148.35	28.38	51.00	0.61	68.36

There can be a total of 9 different Type Codes, but I'm only showing 4 in this example.

So basically, I'd like a total of all the types added up in the Total column, and then also show the individual totals by Type in separate columns.

Possible?

Here is my current sample SQL:

[SIZE=14px][FONT=courier new]select aastr as Store, aayy as Year, aamm as Month, sum(aaamt) as Total, bbtyp as Type
from myFile1 left join myFile2 on aayy = bbyy and aamm = bbmm and aadd = bbdd and aapos = bbpos
where aayy = 19 and aamm = 11 and aastr = '01'
group by aastr, aayy, aamm, bbtyp
order by aastr, aayy, aamm, bbtyp[/FONT][/SIZE]

Any help is appreciated. Thanks in advance.

[mjhaston]

Could you use UNION ALL ?

[B.Hauser]

Try to change your query as follows:

select aastr as Store, aayy as Year, aamm as Month, sum(aaamt) as Total,
Sum(Case When bbtyp = 'AD' Then aaamt Else 0 End) AD,
Sum(Case When bbtyp = 'CC' Then aaamt Else 0 End) CC,
sum(Case When bbtyp = 'PF' Then aaamt Else 0 End) PF,
Sum(Case When bbTyp = 'SP' Then aaamt Else 0 End) SP
from myFile1 left join myFile2 on aayy = bbyy and aamm = bbmm and aadd = bbdd and aapos = bbpos
where aayy = 19 and aamm = 11 and aastr = '01'
group by aastr, aayy, aamm
order by aastr, aayy, aamm

Birgitta

[Viking]

Birgitta, this worked great (with minor syntax tweak - see below) - thank you!

Sum(Case When bbtyp = 'AD' Then aaamt Else 0 [COLOR=#FF0000]end[/COLOR]) [COLOR=#FF0000]as [/COLOR]AD,

etc.