Need result in one line

• Maverick100877

  SSCertifiable

  Points: 5240

  More actions

  November 15, 2005 at 7:57 am

  #166211

  Hi

  I have two tables.

  Table A

  I3_rowid  Envelopeno  

  1    1234

  4    1342 

  Table B

  I3_rowid   Querytype

  1    Escalation 

  1    Rejection

  1    Illegible 

  1    Escalation

  4    Rejection 

  4    Rejection

  4    Illegible

  I3Roid is the primary key in the table A.

  Now I want the result like this I3Rowid,count of each query type

  I3_rowid  Escalation   Rejection   Illegible

  1           2                  1                1

  4           0                   2                1

  Thx a lot

   

   

• Adam Angelini

  SSCrazy Eights

  Points: 8548

  More actions

  November 15, 2005 at 8:05 am

  #603925

  select I3_rowid,sum(case when querytype = 'escalation' then 1 else o end) as escalation,sum(case when querytype = 'rejection' then 1 else 0 end) as rejection,sum(case when querytype = 'illegible' then 1 else 0 end) as illegible group by I3_rowid

   

  Not sure if you need the envelopeno included, but you would just join the tables and add that column to the select list and the group by clause

• Adam Angelini

  SSCrazy Eights

  Points: 8548

  More actions

  November 15, 2005 at 8:12 am

  #603931

  I guess a from clause would help.....

  select I3_rowid,sum(case when querytype = 'escalation' then 1 else o end) as escalation,sum(case when querytype = 'rejection' then 1 else 0 end) as rejection,sum(case when querytype = 'illegible' then 1 else 0 end) as illegible from group by I3_rowid