select statement
-
hi,
I need help to write a select statement that take the upper table and select the lower table.
thanks
-
PIVOT should get you what you need. Have a look at this example and see whether it helps you.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Following is another way of doing this
Declare @tblPersonTask Table
(
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
select Person,
max(Case when DayNm = 'sun' then Task end) AS 'sun',
max(Case when DayNm = 'mon' then Task end) AS 'mon',
max(Case when DayNm = 'tue' then Task end) AS 'tue',
max(Case when DayNm = 'wed' then Task end) AS 'wed',
max(Case when DayNm = 'thu' then Task end) AS 'thu',
max(Case when DayNm = 'fri' then Task end) AS 'fri',
max(Case when DayNm = 'sat' then Task end) AS 'sat'
from @tblPersonTask
group by Person
-
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply