1

I have the following view in SQL Server 2008.

DEPT | EMP_ID | EMP_NAME | P_DATE  | HOURS_WORKED

I want the view to be this way:

DEPT | EMP_ID | EMP_NAME | 2012-09-28 | 2012-09-29 | 2012-09-30 | 2012-10-01 ...

where the above date column header is P_DATE below which is "Hours_Worked" values of that employee on that particular date.

Like

2012-09-28

09:00:00

10:00:00

I am not sure whether I could achieve it using Pivot.

Please go to this link for clear understanding : SQL Server View Snapshots

Vikdor
  • 23,934
  • 10
  • 61
  • 84
Mohd Mohiuddin
  • 23
  • 1
  • 1
  • 3
  • 3
    Did you get a chance to look at any of the questions displayed under "Related" section on RHS of this page, e.g. http://stackoverflow.com/questions/1677645/using-pivot-in-sql-server-2008?rq=1? – Vikdor Oct 20 '12 at 14:27
  • Please post the code you have so far, and tell us where you are tuck – Pete Carter Oct 21 '12 at 18:22
  • Was busy with some other stuff. It was all that I required but also looking for SQL Server 2000. Thanks anyway. – Mohd Mohiuddin Oct 22 '12 at 09:48

1 Answers1

7

You can perform this with the PIVOT function. If you know the values that you want to turn into columns than you can hard code then using a static pivot:

select *
from 
(
  select dept, emp_id, emp_name, p_date, hours_worked
  from table1
) x
pivot
(
  max(hours_worked)
  for p_date in ([2012-10-19], [2012-10-20], [2012-10-21])
) p

See SQL Fiddle with Demo

If you have an unknown number of values, then you can use dynamic sql to PIVOT the data:

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

select @cols = STUFF((SELECT distinct ',' 
                        + QUOTENAME(convert(char(10), p_date, 120)) 
                    from table1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT dept, emp_id, emp_name,' + @cols + ' from 
             (
                select dept, emp_id, emp_name, p_date, hours_worked
                from table1
            ) x
            pivot 
            (
                max(hours_worked)
                for p_date in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • Thanks a lot for the solution. Does this code work with SQL Server 2000 as well? – Mohd Mohiuddin Oct 21 '12 at 10:15
  • No the PIVOT function was not available until 2005. You have this tagged as 2008, do you need a 2000 solution? – Taryn Oct 21 '12 at 14:03
  • Was busy with some other stuff. It was all that I required but also looking for SQL Server 2000. Thanks anyway. – Mohd Mohiuddin Oct 22 '12 at 09:50
  • I got this Msg 170, Level 15, State 1 Line 13: Incorrect syntax near 'XML'. – Mohd Mohiuddin Nov 17 '12 at 10:09
  • @MohdMohiuddin what version of SQL server are you running this in? – Taryn Nov 17 '12 at 13:00
  • @MohdMohiuddin can you recreate this sql fiddle (http://sqlfiddle.com/#!3/b0dda/4) with some of your sample data and the query you are using? Without seeing your query it is difficult to determine your error. – Taryn Nov 18 '12 at 13:02
  • It's a view. The view is similar to your table1. I shall create sql fiddle later. Please don't mind. I have multiple tasks to perform. I appreciate your patience. – Mohd Mohiuddin Nov 18 '12 at 14:05