sponsored links

Collapse

Announcement

Collapse
No announcement yet.

Cross Join Lateral...?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Cross Join Lateral...?

    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:

    Code:
    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
    Any help is appreciated. Thanks in advance.

  • #2
    Could you use UNION ALL ?

    Last edited by mjhaston; 1 week ago.
    Your friends list is empty!

    Comment


    • #3
      Try to change your query as follows:
      Code:
      select aastr as Store, aayy as Year, aamm as Month, sum(aaamt) as Total,
      Sum(Case When bbtyp = 'AD' Then aaamt Else 0) AD,
      Sum(Case When bbtyp = 'CC' Then aaamt Else 0) CC,
      sum(Case When bbtyp = 'PF' Then aaamt Else 0) PF,
      Sum(Case When bbTyp = 'SP' Then aaamt Else 0) 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

      Comment

      Working...
      X