1

I am creating a sql for checkin time for employees.

My table looks like this

enter image description here

I want to display checkin time for employees.

So for example jason timesheet will look something like this

name   | 1st feb  | 2nd feb  | 3rd feb
----------------------------------------------------    
jason  |  9:00:00 |  9:00:00 | ...
clark  |  ...     |  ...     | ...

I have found a similar question here Efficiently convert rows to columns in sql server

But it's not quite working

output that I am gettingenter image description here

**Updated:**following is my sql

     DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

 select @cols = STUFF((SELECT ',' + QUOTENAME(checkin)                    
                from cico
                group by checkin, id
                order by id
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

  set @query = N'SELECT ' + @cols + N' from 
         (
            select checkin, name
            from cico
        ) x
        pivot 
        (
            max(checkin)
            for name in (' + @cols + N')
        ) p '

        exec sp_executesql @query;
     END
Community
  • 1
  • 1
user786
  • 3,902
  • 4
  • 40
  • 72

2 Answers2

2

Try this..

I have edited your query...

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

 select @cols = STUFF((SELECT ',' + QUOTENAME(DateT)                    
                from cico
                group by DateT
                order by DateT
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

  set @query = N'SELECT name,' + @cols + N' from 
         (
            select name,DateT,checkin
            from cico
        ) x
        pivot 
        (
            max(checkin)
            for DateT in (' + @cols + N')
        ) p '

        exec sp_executesql @query;
     END
Sankar
  • 6,908
  • 2
  • 30
  • 53
  • now it give two error:Msg 8114, Level 16, State 1, Line 9 Error converting data type nvarchar to datetime. Msg 473, Level 16, State 1, Line 9 The incorrect value "09:00:10.0000000" is supplied in the PIVOT operator. – user786 Feb 24 '16 at 09:35
  • hi @SankarRaj, if I am working on sql server ssrs report. can I use `Matrix` to achieve this outcome that you answered? – user786 Mar 01 '16 at 11:57
  • I want the same result on presentation layer. so i am asking can I achieve the above with `matrix`? – user786 Mar 01 '16 at 14:37
1

try this

DECLARE @cols AS NVARCHAR(MAX)
    ,@query AS NVARCHAR(MAX)

-- Get all dates and format it in CSV string
SELECT @cols = STUFF((
            SELECT ',' + QUOTENAME(DateT)
            FROM cico
            GROUP BY DateT
            ORDER BY DateT
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- The pivot query 
SET @query = N'SELECT name, ' + @cols + N' from 
         (
            select checkin, name, DateT
            from cico
        ) x
        pivot 
        (
            max(checkin)
            for DateT in (' + @cols + N')
        ) p '

PRINT @query  -- print out the query

EXEC sp_executesql @query;
Squirrel
  • 23,507
  • 4
  • 34
  • 32