SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Pivot Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IK1972
Starting Member

48 Posts

Posted - 02/05/2013 :  17:29:01  Show Profile  Reply with Quote

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
52317 Posts

Posted - 02/06/2013 :  00:54:24  Show Profile  Reply with Quote

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/

Go to Top of Page

IK1972
Starting Member

48 Posts

Posted - 02/06/2013 :  11:14:58  Show Profile  Reply with Quote

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

India
52317 Posts

Posted - 02/06/2013 :  11:18:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000