CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivot
Steve
Steve

Reputation: 5

Convert Rows into Columns in Sql query

I am having tough time to figure out this please help me I have time in/out sql query I have a table looks like below. there are four columns display time in/out info such as...

Date Day    Day  TimeStamp CheckType   
10/11/2014  Sat  8:30 am    in
10/11/2014  Sat  11:30am    out
10/11/2014  Sat  1:30pm     in
10/11/2014  Sat             out
10/12/2014  Sun  9:00am     in
10/12/2014  Sun  11:20pm    out
10/12/2014  Sun  5:20pm     out
10/13/2014  Mon  8:00am     in
10/13/2014  Mon  6:10pm     in

so whoever checkin or checkout then the record will display the result in order and if someone is supposed to check out but accidently pressed in button then this will display as it is (in) or if someone forget to check out then that space will show blank

I am trying to convert rows into column and display such information in below

Date       Day   Time  Type  Time   Type Time  Type  Time  Type etc-----
10/11/2014 Sat  8:30am in  11:30am  out 1:30pm  in     
10/12/2014 Sun  9:00am in  11:20am  out 1:20pm  in  6:20pm in
10/13/2014 Mon  8:00am in  6:10pm   out
10/14/2014 Tus  8:20am in
etc

I have tried to use pivot 

select Date, Day, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] etc---
from
(
select Date, Day, Stamptime, CheckTime, userID
from a table
)
pivot
(
    max(StampTime)
    for stamptime in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10] etc---)
) as PivotTable

can anyone explain how to convert the rows into columns I have spent many days already.

Upvotes: 0

Views: 11932

Answers (1)

JohnLBevan
JohnLBevan

Reputation: 24410

Here's something close to what you're after, making use of XML to get the variable number of columns. As mentioned in my comment above though, I don't recommend this approach. SQL Fiddle: http://sqlfiddle.com/#!3/e5b325/2

select [Date]
, [Day]
, (
    select [TimeStamp] [Time]
    , [CheckType] [Type]
    from aTable b
    where b.[Date] = a.[Date]
    order by [TimeStamp], [CheckType]
    for xml path ('')
) CheckInAndOutInfo
from aTable a
group by [Date], [Day]
order by [Date]

Output:

DATE        DAY CHECKINANDOUTINFO
2014-10-11  Sat <Type>Out</Type><Time>08:30:00</Time><Type>In</Type><Time>11:30:00</Time><Type>Out</Type><Time>13:30:00</Time><Type>In</Type>
2014-10-12  Sun <Time>09:00:00</Time><Type>In</Type><Time>17:20:00</Time><Type>Out</Type><Time>23:20:00</Time><Type>Out</Type>
2014-10-13  Mon <Time>08:00:00</Time><Type>In</Type><Time>18:10:00</Time><Type>In</Type>

Alternatively, if you can guarantee you'll never have more than a certain number of check-ins/outs per day, you could do the following (this assumes no more than 5 per day): SQL Fiddle: http://sqlfiddle.com/#!3/e5b325/4

select *
from
(
    select [Date], [Day]
    , 'T' + CAST(ROW_NUMBER() over (partition by [Date] order by [TimeStamp], [CheckType]) as nvarchar) r
    ,  cast([TimeStamp] as nvarchar) pvtVal
    from aTable
    where [TimeStamp] is not null


    union all

    select [Date], [Day]
    , 'C' + CAST(ROW_NUMBER() over (partition by [Date] order by [TimeStamp], [CheckType]) as nvarchar) r
    ,  cast([CheckType] as nvarchar) pvtVal
    from aTable
    where [TimeStamp] is not null
) x
pivot
(
    min(pvtVal)
    for r in ([T1], [C1], [T2], [C2], [T3], [C3], [T4], [C4], [T5], [C5])
) y
order by [Date]

Output:

DATE        DAY  T1                C1  T2                C2   T3                C3      T4      C4      T5      C5
2014-10-11  Sat  08:30:00.0000000  In  11:30:00.0000000  Out  13:30:00.0000000  In      (null)  (null)  (null)  (null)
2014-10-12  Sun  09:00:00.0000000  In  17:20:00.0000000  Out  23:20:00.0000000  Out     (null)  (null)  (null)  (null)
2014-10-13  Mon  08:00:00.0000000  In  18:10:00.0000000  In   (null)            (null)  (null)  (null)  (null)  (null)

...Or if you wanted to use dynamic SQL, you could do this: SQL Fiddle: http://sqlfiddle.com/#!3/e5b325/6

declare @sql nvarchar(max)
select @sql = coalesce(@sql+',','') + QUOTENAME('T' + CAST(x as nvarchar)) + ',' + QUOTENAME('C' + CAST(x as nvarchar))
from
(
    select distinct row_number() over (partition by [Date] order by [Date]) x 
    from aTable 
    where [TimeStamp] is not null
) y
order by x

set @sql =
    'select *
    from
    (
        select [Date], [Day]
        , ''T'' + CAST(ROW_NUMBER() over (partition by [Date] order by [TimeStamp], [CheckType]) as nvarchar) r
        ,  cast([TimeStamp] as nvarchar) pvtVal
        from aTable
        where [TimeStamp] is not null

        union all

        select [Date], [Day]
        , ''C'' + CAST(ROW_NUMBER() over (partition by [Date] order by [TimeStamp], [CheckType]) as nvarchar) r
        ,  cast([CheckType] as nvarchar) pvtVal
        from aTable
        where [TimeStamp] is not null
    ) x
    pivot
    (
        min(pvtVal)
        for r in (' + @sql + ')
    ) y
    order by [Date]'

exec (@sql)

Output:

DATE        DAY  T1                C1  T2                C2   T3                C3
2014-10-11  Sat  08:30:00.0000000  In  11:30:00.0000000  Out  13:30:00.0000000  In
2014-10-12  Sun  09:00:00.0000000  In  17:20:00.0000000  Out  23:20:00.0000000  Out
2014-10-13  Mon  08:00:00.0000000  In  18:10:00.0000000  In   (null)            (null)

Upvotes: 3

Related Questions