sql server - Create more then one column in a pivot table with one sql statement -

sql server - Create more then one column in a pivot table with one sql statement -

i need sort on date in pivot table project info same project number

the project looks this:

"project nr" "task" "task deadline" "task type production" 123 pack 1 apr 2013 pack 123 leave production 3 apr 2013 leave production 123 flight date 9 apr 2013 flight date

the "task type production" made ensure contents of field consistant can create 1 column in pivot table. there way display thes info in 3 columns this:

project nr ; pack ; leave production ; flightdate select [taskdeadline] packed msp_epmtask_userview [task type production] = 'packed' select [taskdeadline] leaveproduction msp_epmtask_userview [task type production] = 'leave production' select [taskdeadline] flightdate msp_epmtask_userview [task type production] = 'flight date'

thanks anne

this can done using aggregate function , case expression:

select [project nr], max(case when [task type production] = 'pack' [task deadline] end) pack, max(case when [task type production] = 'leave production' [task deadline] end) [leave production], max(case when [task type production] = 'flight date' [task deadline] end) [flight date] msp_epmtask_userview grouping [project nr]

see sql fiddle demo

if want utilize pivot function in sql server, query be:

select * ( select [project nr],[task deadline], [task type production] msp_epmtask_userview ) src pivot ( max([task deadline]) [task type production] in ([pack], [leave production], [flight date]) ) piv

see sql fiddle demo.

finally can done using multiple joins table:

select t1.[project nr], t1.[task deadline] pack, t2.[task deadline] [leave production], t3.[task deadline] [flight date] msp_epmtask_userview t1 left bring together msp_epmtask_userview t2 on t1.[project nr] = t2.[project nr] , t2.[task type production] = 'leave production' left bring together msp_epmtask_userview t3 on t1.[project nr] = t3.[project nr] , t3.[task type production] = 'flight date' t1.[task type production] = 'pack'

see sql fiddle demo

the result of queries is:

| project nr | pack | leave production | flight date | ------------------------------------------------------------ | 123 | 2013-04-01 | 2013-04-03 | 2013-04-09 |

sql-server pivot