| Author |
 Topic  |
|
|
IK1972
Starting Member
41 Posts |
 Posted - 02/05/2013 : 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 #temp
My Expected Report output is in below.
-- Report Output
QID -- 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
India
47173 Posts |
Posted - 02/06/2013 : 00:54:24
|
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 table
GROUP BY QID,LCNumber
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
IK1972
Starting Member
41 Posts |
Posted - 02/06/2013 : 11:14:58
|
Thanks visakh16
I 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
India
47173 Posts |
|
| |
Topic |
|
|
|
SQL Pivot Query
