Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pivot table with two columns to pivot

Author  Topic 

subsoft
Starting Member

2 Posts

Posted - 2014-08-20 : 22:48:18
Hi

I have a table with this data structure (Before section)



I have the query below to generate date as in (After section)

But I would like to have (Desired section) view where start and end tasks are next to each other

Any help will be great

Here is the query
-----------------------------


SELECT 
ID
,[Final] AS [Final Start]
,[Edit] as [Edit Start]
,[Proof] as [Proof Start]
,[Stage] as [Stage Start]
,[Marketing] as [Marketing Start]
,[Developer] as [Developer Start]
,[Promotion] as [Promotion Start]

FROM
(SELECT ID,TaskName,convert(varchar,[TaskStartDate],103) AS [TaskStartDate] FROM #TempTask) a
PIVOT (max(TaskStartDate) FOR TaskName IN (

[Final]
,[Edit]
,[Proof]
,[Stage]
,[Marketing]
,[Developer]
,[Promotion]

)) AS pvt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-08-20 : 23:01:16
you can use the CASE WHEN method to do the pivoting

SELECT ID,
MAX(CASE WHEN TaskName = 'Final' THEN TaskStartDate END) as [Final Start],
MAX(CASE WHEN TaskName = 'Final' THEN TaskEndDate END) as [Final End],
MAX(CASE WHEN TaskName = 'Edit' THEN TaskStartDate END) as [Edit Start],
MAX(CASE WHEN TaskName = 'Edit' THEN TaskEndDate END) as [Edit End],
. . .
FROM #TempTask
GROUP BY ID



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

subsoft
Starting Member

2 Posts

Posted - 2014-08-21 : 05:00:19
Thank you Khtan that worked .... perfect
Go to Top of Page
   

- Advertisement -