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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Pivot Query

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 #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

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 table
GROUP BY QID,LCNumber
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

IK1972

56 Posts

Posted - 2013-02-06 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 11:18:21
you can make it dynamic

see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -