Create more than one column in pivot table with one sql statement
I need to sort by date in pivot table project data for the same project number
The project looks like this:
"Project nr" "Task" "Task deadline" "Task Type Production"
123 pack 1 april 2013 Pack
123 Leave production 3 april 2013 Leave Production
123 Flight date 9 april 2013 Flight Date
The "issue type" is created to ensure that the field content is always constant. I can only create one column in a pivot table. Is there any way to display information in 3 columns it would look like this:
Project nr ; Pack ; leave production ; flightdate
SELECT [TaskDeadline] AS Packed
FROM MSP_EpmTask_UserView where [Task Type Production] = 'Packed'
SELECT [TaskDeadline] AS LeaveProduction
FROM MSP_EpmTask_UserView where [Task Type Production] = 'Leave Production'
SELECT [TaskDeadline] AS FlightDate
FROM MSP_EpmTask_UserView where [Task Type Production] = 'Flight Date'
Thanks Ann
source to share
This can be easily done using an aggregate function and an expression CASE
:
select [Project nr],
MAX(case when [Task Type Production] = 'Pack' then [Task deadline] end) as Pack,
MAX(case when [Task Type Production] = 'Leave Production' then [Task deadline] end) as [Leave Production],
MAX(case when [Task Type Production] = 'Flight Date' then [Task deadline] end) as [Flight Date]
from MSP_EpmTask_UserView
group by [Project nr]
If you want to use a function PIVOT
in SQL Server, the query would look like this:
select *
from
(
select [Project nr],[Task deadline], [Task Type Production]
from MSP_EpmTask_UserView
) src
pivot
(
max([Task deadline])
for [Task Type Production] in ([Pack], [Leave Production],
[Flight Date])
) piv
See SQL Fiddle with Demo .
Finally, this can be done using multiple joins in your table:
select t1.[Project nr],
t1.[Task deadline] pack,
t2.[Task deadline] [Leave Production],
t3.[Task deadline] [Flight Date]
from MSP_EpmTask_UserView t1
left join MSP_EpmTask_UserView t2
on t1.[Project nr] = t2.[Project nr]
and t2.[Task Type Production] = 'Leave Production'
left join MSP_EpmTask_UserView t3
on t1.[Project nr] = t3.[Project nr]
and t3.[Task Type Production] = 'Flight Date'
where t1.[Task Type Production] = 'Pack'
The result of all requests is:
| PROJECT NR | PACK | LEAVE PRODUCTION | FLIGHT DATE |
------------------------------------------------------------
| 123 | 2013-04-01 | 2013-04-03 | 2013-04-09 |
source to share
You can try to create a cross tab or pivot using the traditional correlated subquery method:
select [Project nr]
, (select [Task deadline] from MSP_EpmTask_UserView where [Project nr] = t.[Project nr] and [Task Type Production] = 'Pack') as Pack
, (select [Task deadline] from MSP_EpmTask_UserView where [Project nr] = t.[Project nr] and [Task Type Production] = 'Leave Production') as [Leave Production]
, (select [Task deadline] from MSP_EpmTask_UserView where [Project nr] = t.[Project nr] and [Task Type Production] = 'Flight Date') as [Flight Date]
from MSP_EpmTask_UserView t
group by [Project nr]
order by [Project nr]
/*
Project nr Pack Leave Production Flight Date
----------- ---------- ---------------- -----------
123 2013-04-01 2013-04-03 2013-04-09
*/
I did this in SQL Server 2008 which has a nice datatype date
, although you can certainly do it with bites or datetime. It also assumes that there is only one of the dates for a specific project nr.
Also, if possible, try not to put spaces or other special characters in database object names that force you to delimit them with parentheses.
source to share