Hi
Please find the needed Pivot query below
Create Table #tblPersonTask
(
DayNmchar(3),
Personvarchar(20),
Tasksmallint
)
insert into #tblPersonTask
select 'sun', 'Jhon', 1 union all
select 'sun', 'Smith', 1 union all
select 'sun', 'Dan', 2 union all
select 'mon', 'Smith', 2 union all
select 'mon', 'Jhon', 3 union all
select 'tue', 'Jhon', 2 union all
select 'tue', 'Smith', 3 union all
select 'tue', 'Dan', 1 union all
select 'wed', 'Smith', 1 union all
select 'thu', 'Jhon', 2 union all
select 'fri', 'Jhon', 2 union all
select 'sat', 'Smith', 3
-- Pivot table with three row and eight columns
SELECT Person , [sun],[mon], [tue],[wed], [thu],[fri], [sat]
FROM
(
-- Select All the Columns needed in the above select statement as well as the pivot
SELECT Person ,Task,DayNm
FROM #tblPersonTask) AS SourceTable
PIVOT
-- Pivot the values (before the FOR statement) for the columns in ()
(
sum(Task)
FOR DayNm IN ([sun],[mon], [tue],[wed], [thu],[fri], [sat])
) AS PivotTable
order by Person
Please also have a look at this nice explanation about Pivot tables.
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Please feel free to ask if you do not understand the code above.
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za