Author |
Topic |
IK1972
56 Posts |
Posted - 2013-02-05 : 17:29:01
|
Hi Guys,I'm trying to create one report and need help in Pivot query. let me describe my issue.create table #temp(UID bigint, QID bigint, LCNumber varchar(50), Name varchar(50), Status varchar(10), CompletedDate datetime)insert into #temp values(1,1, 'abc', 'App Setting', 'Created', null),(2,1, 'abc', 'Programe Select', 'Completed', '01/01/2013 12:23:00'),(3,1, 'abc', 'Programe Review', 'Inprocess', null),(4,1, 'abc', 'Programe Aduit', 'Created', null),(5,1, 'abc', 'Programe Approved', 'Completed', '01/05/2013 12:23:00'),(6,2, 'xyz', 'App Setting', 'Created', null),(7,2, 'xyz', 'Programe Select', 'Completed', '01/03/2013 11:23:00'),(8,2, 'xyz', 'Programe Review', 'Completed', '01/08/2013 16:23:00'),(9,2, 'xtz', 'Programe Aduit', 'Inprocess', null),(10,2, 'xyz', 'Programe Approved', 'Completed', '01/05/2013 12:23:00'),(11,3, 'sad', 'App Setting', 'Completed', '02/01/2013 07:00:00'),(12,3, 'sad', 'Programe Select', 'Created',null),(13,3, 'sad', 'Programe Review', 'Completed', '01/08/2013 16:23:00'),(14,3, 'sad', 'Programe Aduit', 'Inprocess', null),(15,3, 'sad', 'Programe Approved', 'Completed', '01/05/2013 12:23:00')select * from #tempMy Expected Report output is in below.-- Report OutputQID -- LCNumber -- [App Setting] -- [App Setting CompletedDate] -- [Programe Select] -- [Programe Select CompletedDate] -- [Programe Review] -- [Programe Review CompletedDate] -- [Programe Aduit] -- [Programe Aduit CompletedDate] -- [Programe Approved] -- [Programe Approved CompletedDate] 1 -- abc -- Created -- null -- Completed -- 01/01/2013 12:23:00 -- Inprocess -- null -- Created -- null -- Completed -- 01/05/2013 12:23:00 2 -- xyz -- Created -- null -- Completed -- 01/03/2013 11:23:00 -- Completed -- 01/08/2013 16:23:00 -- Inprocess -- null -- Completed -- 01/05/2013 12:23:00 3 -- sad -- Completed -- 02/01/2013 07:00:00 -- Created -- null -- Completed -- 01/08/2013 16:23:00 -- Inprocess -- null -- Completed -- 01/05/2013 12:23:00 Any help appreciated.Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-06 : 00:54:24
|
[code]SELECT QID,LCNumber,MAX(CASE WHEN Name ='App Setting' THEN Status END) AS [App Setting],MAX(CASE WHEN Name ='App Setting' THEN CompletedDate END) AS [App Setting CompletedDate],MAX(CASE WHEN Name ='Programe Select' THEN Status END) AS [Programme Select],MAX(CASE WHEN Name ='Programe Select' THEN CompletedDate END) AS [Programme Select CompletedDate],...,repeat the code changing name with other items like program review,...MAX(CASE WHEN Name ='Programe Approved' THEN Status END) AS [Programme Approved],MAX(CASE WHEN Name ='Programe Approved' THEN CompletedDate END) AS [Programme Approved CompletedDate]FROM tableGROUP BY QID,LCNumber[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
IK1972
56 Posts |
Posted - 2013-02-06 : 11:14:58
|
Thanks visakh16I have 35 column in this way and its more then 1000K rows.I think its good if we are able to get this through Pivot I will try this also and check how much time it takes.Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|