none
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

    Tuesday, February 05, 2013 10:32 PM

Answers

  • 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


    Wednesday, February 06, 2013 2:06 AM
  • 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.
    Wednesday, February 06, 2013 4:53 PM

All replies

  • 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


    • Edited by Rogge Tuesday, February 05, 2013 10:45 PM code formatting
    • Proposed as answer by Naomi NModerator Tuesday, February 05, 2013 10:49 PM
    Tuesday, February 05, 2013 10:44 PM
  • Thanks Rogge, 

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

    not sure whats the reason.

    Thanks

    Tuesday, February 05, 2013 10:57 PM
  • Hi, Could you please show how we could do a dynamic multiple aggregate pivot for this problem?

    Thanks in advance...........
    Tuesday, February 05, 2013 11:59 PM
  • 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


    Wednesday, February 06, 2013 2:06 AM
  •  

    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

    Wednesday, February 06, 2013 10:20 AM
  • 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.
    Wednesday, February 06, 2013 4:53 PM