May 10, 2011 at 10:09 pm
Here is the table, mind you it's in a raw format and is for testing only so it not named properly.
CREATE TABLE [dbo].[TESTING](
[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
) ON [PRIMARY]
END
GO
Some Date
3Marketplace Order ID 1Request Item 2Marketplace Product 11Item Date Closed 10Date Due
WS849369 WPS.QA1 Wintel Package Request2010/03/29 03:54:50.000 2009/10/14 09:15:32.000
WS849369 WPS.QA2 Wintel Package Request2010/03/29 03:55:36.000 2009/10/15 09:15:33.000
WS849369 WPS.GenerateWrapID.Auto Wintel Package Request2010/03/29 03:55:38.000 2009/10/15 10:15:34.000
WS849369 WPS.PublishToProd.Auto Wintel Package Request2010/03/29 03:55:41.000 2009/10/15 11:15:34.000
WS849369 WPS.Prod Entitlement Wintel Package Request2010/03/30 18:57:50.000 2009/10/14 11:15:35.000
WS849369 WPS.GenerateWrapID.Auto Wintel Package Request2010/03/29 04:20:59.000 2009/10/15 11:00:00.000
WS849369 WPS.PublishToProd.Auto Wintel Package Request2010/03/29 04:21:01.000 2009/10/15 12:00:00.000
WS849369 WPS.Prod Entitlement Wintel Package Request2010/03/30 18:59:13.000 2009/10/14 12:00:00.000
WS849369 WPS.Notify.Auto Wintel Package Request2010/03/30 18:59:14.000 2009/10/16 13:00:00.000
I am trying to create a seperate date column for each of the request items so that I end up with
3Marketplace Order ID 2Marketplace Product wps.QA1Closed wps.QA2Closed WPS.GenerateWrapClosed
WS849369 Wintel Package Request 2010/03/29 03:55:36.000 2010/03/30 18:57:50.000 2010/03/30 18:59:14.000
All of the example of PIVOT use SUMS and COUNT and I do not need that just a reformat of the data.
Possible?
thanks
May 11, 2011 at 6:47 am
I think you want to pivot on the [11Item Date Closed] column. The column names shouldn't have spaces in them. Try this
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
)
insert into #t
values
('WS849369','WPS.QA1','Wintel Package Request','2010/03/29 03:54:50.000','2009/10/14 09:15:32.000'),
('WS849369','WPS.QA2','Wintel Package Request','2010/03/29 03:55:36.000','2009/10/15 09:15:33.000'),
('WS849369','WPS.GenerateWrapID.Auto','Wintel Package Request','2010/03/29 03:55:38.000','2009/10/15 10:15:34.000'),
('WS849369','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 03:55:41.000','2009/10/15 11:15:34.000'),
('WS849369','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:57:50.000','2009/10/14 11:15:35.000'),
('WS849369','WPS.GenerateWrapID.Auto','Wintel Package Request',' 2010/03/29 04:20:59.000','2009/10/15 11:00:00.000'),
('WS849369','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 04:21:01.000','2009/10/15 12:00:00.000'),
('WS849369','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:59:13.000','2009/10/14 12:00:00.000'),
('WS849369','WPS.Notify.Auto','Wintel Package Request','2010/03/30 18:59:14.000','2009/10/16 13:00:00.000')
--PIVOT
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT [1Request Item]
FROM #t
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
EXEC ('SELECT *
FROM
(
SELECT [3Marketplace Order ID], [1Request Item], [11Item Date Closed] from #t
) t
PIVOT (max([11Item Date Closed]) 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 11, 2011 at 7:22 am
Thank You!!!
I see I need to do some reading 🙂
3Marketplace Order ID,WPS.GenerateWrapID.Auto,WPS.Notify.Auto,WPS.Prod Entitlement,WPS.PublishToProd.Auto,WPS.QA1,WPS.QA2
WS849369,2010/03/29 04:20:59.000,2010/03/30 18:59:14.000,2010/03/30 18:59:13.000,2010/03/29 04:21:01.000,2010/03/29 03:54:50.000,2010/03/29 03:55:36.000
May 11, 2011 at 7:58 am
Somthing odd happens though
when running on one SQL 2008 Server everything works fine, when running on another,same version, I keep getting a
Line 3: Msg 102, Level 15, State 1:
Incorrect syntax near ','.
I do not see anything wrong though. Is there some other reason?
insert into #t
values
('WS849369','WPS.QA1','Wintel Package Request','2010/03/29 03:54:50.000','2009/10/14 09:15:32.000'),
('WS849369','WPS.QA2','Wintel Package Request','2010/03/29 03:55:36.000','2009/10/15 09:15:33.000'),
('WS849369','WPS.GenerateWrapID.Auto','Wintel Package Request','2010/03/29 03:55:38.000','2009/10/15 10:15:34.000'),
('WS849369','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 03:55:41.000','2009/10/15 11:15:34.000'),
('WS849369','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:57:50.000','2009/10/14 11:15:35.000'),
('WS849369','WPS.GenerateWrapID.Auto','Wintel Package Request',' 2010/03/29 04:20:59.000','2009/10/15 11:00:00.000'),
('WS849369','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 04:21:01.000','2009/10/15 12:00:00.000'),
('WS849369','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:59:13.000','2009/10/14 12:00:00.000'),
('WS849369','WPS.Notify.Auto','Wintel Package Request','2010/03/30 18:59:14.000','2009/10/16 13:00:00.000')
May 11, 2011 at 8:01 am
check the compatability mode of the database on the server you are getting the issue on. It might be set to 2005 or lower.
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 11, 2011 at 8:16 am
fixed it with
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.QA1','Wintel Package Request','2010/03/29 03:54:50.000','2009/10/14 09:15:32.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.QA2','Wintel Package Request','2010/03/29 03:55:36.000','2009/10/15 09:15:33.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.GenerateWrapID.Auto','Wintel Package Request','2010/03/29 03:55:38.000','2009/10/15 10:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 03:55:41.000','2009/10/15 11:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:57:50.000','2009/10/14 11:15:35.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.GenerateWrapID.Auto','Wintel Package Request',' 2010/03/29 04:20:59.000','2009/10/15 11:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 04:21:01.000','2009/10/15 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:59:13.000','2009/10/14 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.Notify.Auto','Wintel Package Request','2010/03/30 18:59:14.000','2009/10/16 13:00:00.000')
May 11, 2011 at 8:17 am
Yup it was forgot that this one was not in full 2008.. thanks again!!
May 11, 2011 at 9:05 am
It's eating through 300,000 rows like corn now 🙂
BUT
I need to understand more on
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT [1Request Item]
FROM #t
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
It's not cool to JUST USE some sample code without understand each line so if there is some helpfull links on the COLESCE I would appreciate it.
Normaly I leave any formating to the reporting tool and use CASE to do some minor changes etc. But there seems to be a PICNIC in Reporting right now 🙂
May 11, 2011 at 9:34 am
tictoc (5/11/2011)
It's eating through 300,000 rows like corn now 🙂BUT
I need to understand more on
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT [1Request Item]
FROM #t
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
It's not cool to JUST USE some sample code without understand each line so if there is some helpfull links on the COLESCE I would appreciate it.
I couldn't have said it better myself.... oh wait, I did (check out my signature).
Link for: Coalesce
What it's doing: it's running a select statement to make a comma-delimited list of the Head column from the @cols table variable. The first time through it, @ColsList is null, so it used the '[' by itself, then adds the Head column and the trailing ']'. Subsequent times through, it adds the ',[' to the string, then adds the Head column and the trailing ']'. (Coalesce gets the first non-null value.)
A (IMHO) better way to create the comma-delimited list can be found at this article: Creating a comma-separated list (SQL Spackle)[/url].
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 11, 2011 at 10:22 am
Thank You for the links and the schooling 🙂 Makes more sense now!
I try and not to be some copy pasta gangsta and learn what each item step does. I have 6 tables and I am push over 6 million rows of data and most of which is not anywhere near SQL Standards so I spends days building out CASE and temp table solutions for report jockey's.
The main reason I was trying to figure out the parts was to see if there was a way to put the items you pivot into some kind of order
now it's picking the values here right SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + '] just grabbing the first one it comes accross and filling in the blanks.
How could you put some order to the values in the comma list?
thanks
May 11, 2011 at 10:32 am
It's actually a dynamic pivot that I found somewhere on this site and use quite often. To sort the columns, I guess it depends on how you want them sorted. When creating the ColList variable, I put a sort order on the fields and that changed the order of the columns. You could also put an identity column in the table variable and then insert into the tagble in the order you want, then sort by that when populating the ColList variable. If you comment out the order by clause, you'll see the difference
--PIVOT
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT [1Request Item]
FROM #t
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
order by Head Desc
EXEC ('SELECT *
FROM
(
SELECT [3Marketplace Order ID], [1Request Item], [11Item Date Closed] from #t
) t
PIVOT (max([11Item Date Closed]) 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 11, 2011 at 11:21 am
tictoc (5/11/2011)
Thank You for the links and the schooling 🙂 Makes more sense now!I try and not to be some copy pasta gangsta and learn what each item step does. I have 6 tables and I am push over 6 million rows of data and most of which is not anywhere near SQL Standards so I spends days building out CASE and temp table solutions for report jockey's.
The main reason I was trying to figure out the parts was to see if there was a way to put the items you pivot into some kind of order
now it's picking the values here right SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + '] just grabbing the first one it comes accross and filling in the blanks.
How could you put some order to the values in the comma list?
thanks
NP - glad to help, and glad to help educate.
To put some order to the list, use the ORDER BY clause on that select statement. (Note that the FOR XML in the article I linked you to can use the ORDER BY also.)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 11, 2011 at 11:34 am
After I posted I had a DUH moment. DUH is dynamic not static. The Order by worked fine and since I also setup a filter to use
[2Marketplace Product] so they have to select the product and it filters things out just fine.
I am looking for some more reading on the naming the columns in the header though as I may have to do this as some point.
Now onto my next adeventure!
Pulling a Start Date out of no where!
Yup there is NO START Date in any of the items steps only a CLOSED date. So here is the Solution they thought up.
The Closed date should be the Start date of the next step
Example
Step A Closed Step B Start Step B Closed Step C Start
01/01/2011 2:00pm 01/01/2011 2:00pm 01/01/2011 2:30pm 01/01/2011 2:230pm
So I know all of the closed dates all of the steps etc and have used DATEDIFF to calculate times based on other dates - + * etc to create a new date fields and large chuncks of CASE to either Search or Compare data but never create another exact field renamed based on anothers valuse..
How in the heck do you take Step A Closed date/time and create Step B Start date/time?
I might add there is a Start date so to speak but it's a single date when the item came in not when it was started. This should be handled by user when they click Start Work in the app... problem is they do not hit Start Work :w00t:
May 11, 2011 at 12:11 pm
Are you looking to populate the StartDate on the nextrow with the EndDate from the previous row? You can use a CTE, but be careful as these can be huge performance hits on large volumes of data. Try this using the sample data provided earlier:
;with cte as
(select [3Marketplace Order ID], [1Request Item], [11Item Date Closed],
Row_Number() over (partition by [3Marketplace Order ID], [1Request Item] order by [3Marketplace Order ID], [1Request Item], [11Item Date Closed]) RowNum
from #t)
SELECT *
FROM
(
SELECT CurRow.[3Marketplace Order ID], CurRow.[1Request Item], NextRow.[11Item Date Closed] StartDate, CurRow.[11Item Date Closed] EndDate
from cte CurRow left outer join cte NextRow
on CurRow.[3Marketplace Order ID] = NextRow.[3Marketplace Order ID]
and CurRow.[1Request Item] = NextRow.[1Request Item]
and CurRow.RowNum = NextRow.RowNum + 1
) t
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 11, 2011 at 1:10 pm
Wow! Never heard of CTE until now, Common Table Expressions, it worked on the example date fine. The only thing is that I need it inline with the closed like the PIVOT does :w00t:
I am digging to see how you can piot this now if that is possible.
thanks
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply