Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

cross tab query Expand / Collapse
Author
Message
Posted Wednesday, August 28, 2013 1:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 30, 2014 1:52 AM
Points: 22, Visits: 59
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
Post #1489045
Posted Wednesday, August 28, 2013 3:49 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:46 AM
Points: 316, Visits: 910
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

Post #1489093
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse