May 23, 2011 at 2:58 pm
After the first select does the Order of the items stays the same in the Head?
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
order by Head
using this
SELECT [2Marketplace Product],
-- SUBSTRING([1Request Item], 5, 25) AS [1Request Item],
[1Request Item],
[3Marketplace Order ID],
[8Request Status],
[9Item Status],
CASE
WHEN [1Request Item] = 'WPS.PrePackageFileUpload.Auto' THEN 'A'
WHEN [1Request Item] = 'WPS.PrePackageValidation' THEN 'B'
WHEN [1Request Item] = 'WPS.UserConfirmation' THEN 'C'
WHEN [1Request Item] = 'WPS.SystemCheck.Auto' THEN 'D'
WHEN [1Request Item] = 'WPS.DeviationQA.Auto' THEN 'E'
WHEN [1Request Item] = 'WPS.PrePackageQA.Auto' THEN 'F'
WHEN [1Request Item] = 'WPS.AdvancedParameter.Auto' THEN 'G'
WHEN [1Request Item] = 'WPS.PublishToDev.Auto' THEN 'H'
WHEN [1Request Item] = 'WPS.PublishToUAT.Auto' THEN 'I'
WHEN [1Request Item] = 'WPS.PrePackageUATApproval' THEN 'J'
WHEN [1Request Item] = 'WPS.IntegrationTesting.Auto' THEN 'K'
WHEN [1Request Item] = 'WPS.PublishToProd.Auto' THEN 'L'
WHEN [1Request Item] = 'WPS.Prod Entitlement' THEN 'M'
WHEN [1Request Item] = 'WPS.Notify.Auto' THEN 'N' ELSE 'shit'
END AS [Order Of Item Steps]
FROM [dbo].[RPT_CMP_WF_RAW]
WHERE [2Marketplace Product] = 'Wintel Pre Packaged Request for DSI/OVCM Packages(Desktops)'
AND [8Request Status] = 'CLOSED'
AND [9Item Status] = 'Work Completed'
ORDER BY [3Marketplace Order ID] ASC,[Order Of Item Steps]
My order comes out correct so I added it into the rest of the COALESCE and PIVOT
like so
DROP TABLE #t
SELECT
YEAR([6Date Logged]) AS [Year Of Request],
-- [1Request Item],
SUBSTRING([1Request Item], 5, 25) AS [1Request Item],
[2Marketplace Product] AS [MarketPlace Product],
[3Marketplace Order ID],
[4RequestID] AS [Request ID],
[5Requestor],
[6Date Logged],
[7Date Closed],
[8Request Status],
[9Item Status],
[10Date Due],
-- CASE WHEN StartDate IS NULL THEN 'Awaiting' ELSE 'Approved' END AS StartDateStatus
CASE WHEN [11Item Date Closed] IS NULL THEN 'NO INPUT' ELSE [11Item Date Closed] END AS [11Item Date Closed],
CASE
WHEN [1Request Item] = 'WPS.PrePackageFileUpload.Auto' THEN 'A'
WHEN [1Request Item] = 'WPS.PrePackageValidation' THEN 'B'
WHEN [1Request Item] = 'WPS.UserConfirmation' THEN 'C'
WHEN [1Request Item] = 'WPS.SystemCheck.Auto' THEN 'D'
WHEN [1Request Item] = 'WPS.DeviationQA.Auto' THEN 'E'
WHEN [1Request Item] = 'WPS.PrePackageQA.Auto' THEN 'F'
WHEN [1Request Item] = 'WPS.AdvancedParameter.Auto' THEN 'G'
WHEN [1Request Item] = 'WPS.PublishToDev.Auto' THEN 'H'
WHEN [1Request Item] = 'WPS.PublishToUAT.Auto' THEN 'I'
WHEN [1Request Item] = 'WPS.PrePackageUATApproval' THEN 'J'
WHEN [1Request Item] = 'WPS.IntegrationTesting.Auto' THEN 'K'
WHEN [1Request Item] = 'WPS.PublishToProd.Auto' THEN 'L'
WHEN [1Request Item] = 'WPS.Prod Entitlement' THEN 'M'
WHEN [1Request Item] = 'WPS.Notify.Auto' THEN 'N' ELSE 'shit'
END AS [Order Of Item Steps]
-- [11Item Date Closed]
INTO #t
FROM [dbo].[RPT_CMP_WF_RAW]
where [8Request Status] = 'CLOSED'
--/*AND [1Request Item] = 'WPS.Notify.AUTO'*/
AND [9Item Status] ='Work Completed'
AND [2Marketplace Product]='Wintel Pre Packaged Request for DSI/OVCM Packages(Desktops)'
ORDER BY [3Marketplace Order ID] ASC,[Order Of Item Steps]
--PIVOT
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT [1Request Item]+'_EndDate'
FROM #t
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
order by Head
EXEC (';with cte as
(select [Marketplace Product],[Request ID],[3Marketplace Order ID], [1Request Item], [11Item Date Closed],
Row_Number() over (partition by [Request ID], [1Request Item] order by [Request ID], [1Request Item], [11Item Date Closed]) RowNum
from #t)
SELECT *
FROM
(
SELECT CurRow.[Marketplace Product],CurRow.[Request ID], CurRow.[1Request Item] + ''_EndDate'' [1Request Item], CurRow.[11Item Date Closed] EndDate
from cte CurRow left outer join cte NextRow
on CurRow.[Request ID] = NextRow.[Request ID]
and CurRow.[1Request Item] = NextRow.[1Request Item]
and CurRow.RowNum = NextRow.RowNum + 1
) t
PIVOT (max([EndDate]) FOR [1Request Item] IN (' + @ColsList + ')) PVT')
thinking that the by order in the select first the head would order the items out. Wrong thinking or?
May 24, 2011 at 6:05 am
It really doesn't matter the order of how you put them into the @Cols table. You just need to be able to order them when populating the @ColsList variable. That's what will determine the order of the columns. Just so happens, that when you put the records into the @Cols table, you ordered them according to your sort order list. You could have just as easily populated the @ColsList using the same variable
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
order by SortOrderList
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 24, 2011 at 7:02 am
Thank You! My sort order is not coming through is the issue that I am trying to fix. The CASE works but once in @Cols table it loses the sort.
When I try using the
You could have just as easily populated the @ColsList using the same variable
So I could have used + CASE and code in the the select?
May 24, 2011 at 7:29 am
This looks right based on the data provided. To check, uncomment the code around select * from @Cols.
Drop Table #t
Create TABLE #t
([3Marketplace Order ID] [nvarchar](138) NULL,
[1Request Item] [nvarchar](127) NULL,
[2Marketplace Product] [nvarchar](138) NULL,
[11Item Date Closed] [datetime] NULL,
[10Date Due] [datetime] NULL,
[4Request ID] int
)
INSERT INTO #T([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed], [10Date Due])
SELECT N'WS849369', N'WPS.QA1', N'Wintel Package Request', '20100329 03:57:50.000', '20091014 09:15:32.000' UNION ALL
SELECT N'WS849369', N'WPS.QA2', N'Wintel Package Request', '20100329 03:55:36.000', '20091015 09:15:33.000' UNION ALL
SELECT N'WS849369', N'WPS.GenerateWrapID.Auto', N'Wintel Package Request', '20100329 03:55:38.000', '20091015 10:15:34.000' UNION ALL
SELECT N'WS849369', N'WPS.PublishToProd.Auto', N'Wintel Package Request', '20100329 03:55:41.000', '20091015 11:15:34.000' UNION ALL
SELECT N'WS849369', N'WPS.Prod Entitlement', N'Wintel Package Request', '20100330 18:57:50.000', '20091014 11:15:35.000' UNION ALL
SELECT N'WS849369', N'WPS.GenerateWrapID.Auto', N'Wintel Package Request', '20100329 04:20:59.000', '20091015 11:00:00.000' UNION ALL
SELECT N'WS849369', N'WPS.PublishToProd.Auto', N'Wintel Package Request', '20100329 04:21:01.000', '20091015 12:00:00.000' UNION ALL
SELECT N'WS849369', N'WPS.Prod Entitlement', N'Wintel Package Request', '20100330 18:59:13.000', '20091014 12:00:00.000' UNION ALL
SELECT N'WS849369', N'WPS.Notify.Auto', N'Wintel Package Request', '20100330 18:59:14.000', '20091016 13:00:00.000' UNION ALL
SELECT N'WS849370', N'WPS.QA1', N'Wintel Package Request', '20100329 03:54:50.000', '20091014 09:15:32.000' UNION ALL
SELECT N'WS849370', N'WPS.QA2', N'Wintel Package Request', '20100329 03:55:36.000', '20091015 09:15:33.000' UNION ALL
SELECT N'WS849370', N'WPS.GenerateWrapID.Auto', N'Wintel Package Request', '20100329 03:55:38.000', '20091015 10:15:34.000' UNION ALL
SELECT N'WS849370', N'WPS.PublishToProd.Auto', N'Wintel Package Request', '20100329 03:55:41.000', '20091015 11:15:34.000' UNION ALL
SELECT N'WS849370', N'WPS.Prod Entitlement', N'Wintel Package Request', '20100330 18:57:50.000', '20091014 11:15:35.000' UNION ALL
SELECT N'WS849370', N'WPS.GenerateWrapID.Auto', N'Wintel Package Request', '20100329 04:20:59.000', '20091015 11:00:00.000' UNION ALL
SELECT N'WS849370', N'WPS.PublishToProd.Auto', N'Wintel Package Request', '20100329 04:21:01.000', '20091015 12:00:00.000' UNION ALL
SELECT N'WS849370', N'WPS.Prod Entitlement', N'Wintel Package Request', '20100330 18:59:13.000', '20091014 12:00:00.000' UNION ALL
SELECT N'WS849370', N'WPS.Notify.Auto', N'Wintel Package Request', '20100330 18:59:14.000', '20091016 13:00:00.000' UNION ALL
SELECT N'WS849371', N'WPS.QA1', N'Wintel Package Request', '20100329 03:58:50.000', '20091014 09:25:32.000' UNION ALL
SELECT N'WS849371', N'WPS.QA2', N'Wintel Package Request', '20100329 03:58:36.000', '20091015 09:15:43.000' UNION ALL
SELECT N'WS849371', N'WPS.GenerateWrapID.Auto', N'Wintel Package Request', '20100329 03:55:38.000', '20091015 10:15:34.000' UNION ALL
SELECT N'WS849371', N'WPS.PublishToProd.Auto', N'Wintel Package Request', '20100329 03:55:41.000', '20091015 11:15:34.000' UNION ALL
SELECT N'WS849371', N'WPS.Prod Entitlement', N'Wintel Package Request', '20100330 18:57:50.000', '20091014 11:15:35.000' UNION ALL
SELECT N'WS849371', N'WPS.GenerateWrapID.Auto', N'Wintel Package Request', '20100329 04:20:59.000', '20091015 11:00:00.000' UNION ALL
SELECT N'WS849371', N'WPS.PublishToProd.Auto', N'Wintel Package Request', '20100329 04:21:01.000', '20091015 12:00:00.000' UNION ALL
SELECT N'WS849371', N'WPS.Prod Entitlement', N'Wintel Package Request', '20100330 18:59:13.000', '20091014 12:00:00.000' UNION ALL
SELECT N'WS849371', N'WPS.Notify.Auto', N'Wintel Package Request', '20100330 18:59:14.000', '20091016 13:00:00.000'
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX), SortOrder varchar(5))
INSERT @Cols (Head, SortOrder)
SELECT DISTINCT [1Request Item]+'_EndDate',
CASE
WHEN [1Request Item] = 'WPS.PrePackageFileUpload.Auto' THEN 'A'
WHEN [1Request Item] = 'WPS.PrePackageValidation' THEN 'B'
WHEN [1Request Item] = 'WPS.UserConfirmation' THEN 'C'
WHEN [1Request Item] = 'WPS.SystemCheck.Auto' THEN 'D'
WHEN [1Request Item] = 'WPS.DeviationQA.Auto' THEN 'E'
WHEN [1Request Item] = 'WPS.PrePackageQA.Auto' THEN 'F'
WHEN [1Request Item] = 'WPS.AdvancedParameter.Auto' THEN 'G'
WHEN [1Request Item] = 'WPS.PublishToDev.Auto' THEN 'H'
WHEN [1Request Item] = 'WPS.PublishToUAT.Auto' THEN 'I'
WHEN [1Request Item] = 'WPS.PrePackageUATApproval' THEN 'J'
WHEN [1Request Item] = 'WPS.IntegrationTesting.Auto' THEN 'K'
WHEN [1Request Item] = 'WPS.PublishToProd.Auto' THEN 'L'
WHEN [1Request Item] = 'WPS.Prod Entitlement' THEN 'M'
WHEN [1Request Item] = 'WPS.Notify.Auto' THEN 'N' ELSE 'shit'
end
FROM #t
/*
select *
FROM @Cols t
order by SortOrder
*/
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
order by SortOrder
select @Colslist
EXEC (';with cte as
(select [2Marketplace Product],[4Request ID],[3Marketplace Order ID], [1Request Item], [11Item Date Closed],
Row_Number() over (partition by [4Request ID], [1Request Item]
order by [4Request ID], [1Request Item], [11Item Date Closed]) RowNum
from #t)
SELECT *
FROM
(
SELECT CurRow.[2Marketplace Product],CurRow.[4Request ID], CurRow.[1Request Item] + ''_EndDate'' [1Request Item], CurRow.[11Item Date Closed] EndDate
from cte CurRow left outer join cte NextRow
on CurRow.[4Request ID] = NextRow.[4Request ID]
and CurRow.[1Request Item] = NextRow.[1Request Item]
and CurRow.RowNum = NextRow.RowNum + 1
) t
PIVOT (max([EndDate]) FOR [1Request Item] IN (' + @ColsList + ')) PVT')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 24, 2011 at 8:14 am
order comes through oddly but I think it's the _enddate or lack of data and a filter. Much appreciated and this gives me alot to ponder with on the sortOrder.
thanks!!
May 24, 2011 at 9:55 am
Using the changes I am now getting a soted order but I have lost the PIVOT and below is all that is returned. I dug through line by line and just dont see why the CASE would change this.
Head SortOrder
WPS.PrePackageFileUpload.AutoA
WPS.PrePackageValidation B
WPS.UserConfirmation C
WPS.SystemCheck.Auto D
WPS.DeviationQA.Auto E
WPS.PrePackageQA.Auto F
WPS.AdvancedParameter.AutoG
WPS.PublishToDev.Auto H
WPS.PublishToUAT.Auto I
WPS.PrePackageUATApprovalJ
WPS.IntegrationTesting.AutoK
WPS.PublishToProd.Auto L
WPS.Prod Entitlement M
WPS.Notify.Auto N
Viewing 6 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply