SQL Pivot Query

Question

Hi Guys,

I need help to write in Pivot query. 

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

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

Answer 1

THERE is a data error at (9,2, 'xtz', 'Programe Aduit', 'Inprocess', null),  

I expect xtc to be xyz....

here is one way:

SELECT
	s.QID,
	s.LCNumber,
	s.[App Setting],
	d.[App Setting]			[App Setting Date],
	s.[Programe Select],
	d.[Programe Select]		[Programe Select Date],
	s.[Programe Review],
	d.[Programe Review]		[Programe Review Date],
	s.[Programe Aduit],
	d.[Programe Aduit]		[Programe Aduit Date],
	s.[Programe Approved],
	d.[Programe Approved]	[Programe Approved Date]
FROM (
	SELECT
		t.QID, t.LCNumber, t.Name, t.[Status]--, t.CompletedDate
	FROM #temp	t
	) u
	PIVOT (
		MAX(u.[Status]) FOR u.Name IN
		(
		[App Setting],
		[Programe Select],
		[Programe Review],
		[Programe Aduit],
		[Programe Approved]
		)
	) s
INNER JOIN (
	SELECT
		t.QID, t.LCNumber, t.Name, t.CompletedDate
	FROM #temp	t
	) u
	PIVOT (
		MAX(u.CompletedDate) FOR u.Name IN
		(
		[App Setting],
		[Programe Select],
		[Programe Review],
		[Programe Aduit],
		[Programe Approved]
		)
	) d
ON	d.QID = s.QID
AND	d.LCNumber = s.LCNumber

Answer 2

Thanks Rogge, 

Its running longer I just mentioned sample data actual 70 columns and around 500K rows. 

not sure whats the reason.

Thanks

Answer 3

Hi, Could you please show how we could do a dynamic multiple aggregate pivot for this problem?

Thanks in advance...........

Answer 4

Take a look at this blog post 

Dynamic PIVOT on multiple columns

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

 

I think there's a typo in your dataset, but anyway, a simple pivot should do the trick..

 

;WITH Cols AS
(
SELECT QID
		, LCNumber
		, Name
		, Status AS Value
FROM	#temp
UNION ALL
SELECT QID
		, LCNumber
		, Name + ' CompletedDate' AS Name
		, CONVERT(varchar(19),CompletedDate,120) AS Value
FROM	#temp
)
SELECT	*
FROM	Cols
PIVOT
(
	MIN(Value)
	FOR Name IN (
		[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]
		)
) p

 

 

 

---

Philippine SQL Server Users Group : www.phissug.org

Answer 6

Thanks Naomi. Here's another way of doing it. I used Naomi's blog as reference.

DECLARE @sql VARCHAR(MAX), @Cols VARCHAR(max)
SELECT @Cols = (SELECT ', ' + 'MAX(CASE WHEN [Name] = ''' + CONVERT(VARCHAR(20), [Name]) + ''' THEN [Status] ELSE NULL END) AS "' + CONVERT(VARCHAR(20), [Name]) + 
'", MAX(CASE WHEN [Name] = ''' + CONVERT(VARCHAR(20), [Name]) + ''' THEN CompletedDate ELSE NULL END) AS "' + CONVERT(VARCHAR(20), [Name]) + ' Date"'
FROM (SELECT DISTINCT [Name] FROM #temp) X ORDER BY [Name] 
FOR XML PATH(''))
SET @sql = 'SELECT QID, LCNumber' + @Cols + '
FROM #temp
GROUP BY QID, LCNumber
ORDER BY QID'
--PRINT @sql
EXECUTE(@sql)

Please correct the typo "9,2, 'xtz'" to xyz as Rogge pointed and this should work.