cross tab query

• imfairozkhan

  SSC-Addicted

  Points: 422

  More actions

  August 28, 2013 at 1:33 am

  #281730

  Hi All,

  I have a below table structure

  cpr attendance_Date attendance_Time Trans_Type

  123 20-Aug-13 8:8:10 I

  123 20-Aug-13 6:10:10 O

  123 21-Aug-13 8:10:10 I

  123 21-Aug-13 7:8:10 O

  123 22-Aug-13 5:5:10 O

  expected output

  cpr attendance_date trans_type

  123 20-Aug-13 In Out

  123 21-Aug-13 In Out

  123 22-Aug-13 Null Out

  any help is highly appreciated.

  regards

• Stefan_G

  SSCertifiable

  Points: 6609

  More actions

  August 28, 2013 at 3:49 am

  #1645465

  Something like this perhaps:

  select

  cpr,

  attendance_date,

  trans_in = max(case when trans_type='I' then 'In' end),

  trans_out = max(case when trans_type='O' then 'Out' end),

  from Table

  group by cpr, attendance_date