SQLServerCentral.com / SQL Server 2008 / T-SQL (SS2K8) / Cross Join Help / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 16:48:36 GMT20RE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspx[quote][b]dwilliscp (7/30/2014)[/b][hr]Do you have a good link for "Triangular" join, never heard of it.[/quote]The author :-D of the article at the following link has put it pretty simply although it's for a correlated sub-query instead of being in the FROM clause. The same effect is realized, though.[url]http://www.sqlservercentral.com/articles/T-SQL/61539/[/url][quote]Oh BTW the 500 * 500 takes 10 seconds to execute (select and insert). I need your hardware LOL.[/quote]Something is definitely wrong there. Even my circa 2002 32 bit single CPU box does better than that. What do you get for an output from the following?[code="sql"] SET STATISTICS TIME ON; SELECT TOP 250000 RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) INTO #MyHead FROM master.sys.all_columns ac1 CROSS JOIN master.sys.all_columns ac2; SET STATISTICS TIME OFF; DROP TABLE #MyHead;GO 3[/code]Wed, 30 Jul 2014 18:09:19 GMTJeff ModenRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspxI don't really understand why "product per lin within a plant" would be end up as a cross join.Based on your example you have two rows provided:INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('CHANC', '11C-8260', '0001', 'APAG')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('CHANC', '11C-8261', '0001', 'APAG')Based on your sample data resource APAG goes from 11C-8260 to 11C-8261 at CHANC.How do you end up with a requested output of Resource APAGFrom Material To Material11C-8260 11C-826011C-8260 11C-826111C-8261 11C-826111C-8261 11C-8260for CHANC??Going from 11C-8260 to 11C-8260 is nowwhere mentioned nor is 11C-8261 to 11C-8261 or 11C-8261 to 11C-8260.What is the business rule leading to this result set based on the given sample data?You'll never be able to determine the time from 11C-8260 to 11C-8260 nor 11C-8261 to 11C-8261 nor 11C-8261 to 11C-8260 since there's not a single information available...Wed, 30 Jul 2014 15:49:14 GMTLutzMRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspx[quote][b]Jeff Moden (7/30/2014)[/b][hr]500*500 is only a quarter million row Cross Join and would probably take less than a quarter second to materialize and just a second or two to insert into an indexed table.For the million row thing, you definitely have to be careful because 1*10^6 squared is 1*10^12 or a Trillion rows. Some of this could be mitigated by using a "Triangular" join, which would produce a bit less than half that of a full up Cross Join but 500 Billion rows is still nothing to sneeze at either duration wise or storage wise.My recommendation would be to NOT turn this "Matrix" into a table.[/quote]The 500 rows was only one Resource at one plant. My cross join was doing more than it should.. but still.. this is working great for one plant. Once I get all the files imported into SQL server I will test run time. I am hoping that since a material can have several versions.. and thus rows.. I will not end up with 1,000,000 * 1,000,000.. or worse since I still have a second company to work with. Do you have a good link for "Triangular" join, never heard of it.Oh I have also asked if it would help to build a table to show what resources we can leave out of this process, I know our plant scheduling software does not use every resource that plant has in our ERP software.Oh BTW the 500 * 500 takes 10 seconds to execute (select and insert).. I need your hardware LOL.Wed, 30 Jul 2014 13:58:56 GMTdwilliscpRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspx500*500 is only a quarter million row Cross Join and would probably take less than a quarter second to materialize and just a second or two to insert into an indexed table.For the million row thing, you definitely have to be careful because 1*10^6 squared is 1*10^12 or a Trillion rows. Some of this could be mitigated by using a "Triangular" join, which would produce a bit less than half that of a full up Cross Join but 500 Billion rows is still nothing to sneeze at either duration wise or storage wise.My recommendation would be to NOT turn this "Matrix" into a table.Wed, 30 Jul 2014 11:36:40 GMTJeff ModenRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspx[quote][b]Jeff Moden (7/28/2014)[/b][hr][quote][b]dwilliscp (7/27/2014)[/b][hr][quote][b]Jeff Moden (7/25/2014)[/b][hr][quote][b]dwilliscp (7/25/2014)[/b][hr]I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.Ideas?[/quote]Define "Massive" because that's important here. A "Control Cursor" (as opposed to RBAR) may be appropriate here.Also, I'd personally like to know what it is you mean by "from" and "to" part numbers because it doesn't make sense to me to reference a toilet paper holder with hot water heater unless they're attached to each other and in the same bathroom.[/quote]Jeff... they are attached... Ok we have a file that shows all resources in a plant, and the materials that we use in them, during production. What we are trying to do, since our shop floor planning software does not do it, is to go in and generate a list of how long it takes to change a resource from producing part A to part B. So I need to take the list of parts, for each company + plant + resource... and create the from part ... to part. Then we can go in and assign how long it will take to transition from part A to Part B... before we can start to produce part B. Got it? The cursor... that I posted on friday.. ran until I killed it on Sunday Morning... and I was running for only one plant.. and parts that started with Z. This was a list of 362 rows.[/quote]Maybe it's just these old eyes but I see a cursor and sample data with "Material_Number" in them but no "Part_Number". Is "Material_Number" what you're talking about?Also, I'm still not bagging what you're raking about the From_Material and To_Material. To me, your cursor...[code="sql"]declare @plant as varchar(4) , @Resource_Code as varchar(8) , @Material_Number as varchar(18)set nocount on;declare i_cursor insensitive cursorfor Select Plant, Resource_Code, Material_Number From Resource_Matopen i_Cursorwhile @@fetch_status = 0begin insert into trans_Matrix(Company, Plant, Resource_Code, From_Material, To_Material) select 'SPOR' as Company , Plant , Resource_Code , @Material_Number as From_Material , Material_Number as To_Material from Resource_Mat where @Plant = plant and @resource_code = resource_code endfetch next from i_Cursor into @Plant, @Resource_Code, @Material_Number close i_Cursordeallocate i_cursorset nocount off;go [/code]... looks like a massive CROSS JOIN of the Resource_Mat table to itself based on the "plant" and "resource_code". While that doesn't make sense to me (even though you mentioned that in the title of the post), if that's what you really need, the following code should do it for you...[code="sql"] INSERT INTO dbo.Trans_Matrix (Company, Plant, Resource_Code, From_Material, To_Material) SELECT Company = 'SPOR' ,Plant = frommat.Plant ,Resource_Code = frommat.Resource_Code ,From_Material = frommat.Material_Number ,To_Material = tomat.Material_Number FROM dbo.Resource_Mat frommat JOIN dbo.Resource_Mat tomat --This is going to work like a conditional CROSS JOIN ON frommat.Plant = tomat.Plant AND frommat.Resource_Code = tomat.Resource_Code;[/code]... and, compared to the cursor, it will run like the wind generating millions of rows in just a couple of seconds if you have some decent indexing on the Resource_Mat table.Of course, you never answered my question about what you meant by a "massive" table. How many rows does it actually have in it???Last but not least, if you go back and look at HanShi's post (the second post on this thread), he suggested the exact same thing I just did. You just needed to study the code a bit to realize that it's probably exactly what you wanted sans some of your column names and the missing criteria for the Resource_Code.[/quote]Thanks Jeff, I had not thought to write the query this way.. It is a bit hard to tell just how many rows I will end up with.. since I have to merge .dat files from each plant, but it is comon for a resource to have around 500 materials + version... but we are only taking the highest version number for this process. (I tend to say part number, but the MRP software uses the name "material number". Sorry for the confusion.) Straight math would be 500 * 500 per resource per plant. Our first plant.. that we are working on has over 1 Mil rows.. but then again I would have to filter down for max version, to see just how many will be fed into this process... I am working on a stored proc, that will load these into a "IMP" table then use that table to create the matrix.Wed, 30 Jul 2014 11:04:08 GMTdwilliscpRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspxSo we end up with...Plant Material_number Production_Version Resource_CodeCHANC 11C-8260 0001 APAGCHANC 11C-8260 0001 SILCHANC 11C-8261 0001 APAGCHANC 11C-8261 0001 SILCHANC 21E-4440 0002 SILLEX 11C-8260 0001 SILLEX 11C-8261 0001 APAGLEX 11C-8262 0001 SILLEX 11C-8263 0001 SILLEX 21E-4440 0001 SILSo we should end up with:Plant CHANC------------- Resource APAGFrom Material To Material11C-8260 11C-826011C-8260 11C-826111C-8261 11C-826111C-8261 11C-8260Resource SILFrom Material To Material11C-8260 11C-826011C-8260 11C-826111C-8260 21E-444011C-8261 11C-826011C-8261 11C-826111C-8261 21E-444021E-4440 11C-8260 21E-4440 11C-826121E-4440 21E-4440Wed, 30 Jul 2014 10:50:06 GMTdwilliscpRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspxI cut the table down to the fields needed... and fixed Plant, when I renamed the plants I exceeded the field size.CREATE TABLE [dbo].[Resource_Mat]([Plant] [varchar](5) NOT NULL,[Material_number] [varchar](18) NOT NULL,[Production_Version] [varchar](4) NOT NULL,[Resource_Code] [varchar](8) NOT NULL,CONSTRAINT [PK_Resource_Mat] PRIMARY KEY CLUSTERED ([Plant] ASC,[Material_number] ASC,[Production_Version] ASC,[Resource_Code] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('CHANC', '11C-8260', '0001', 'SIL')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('CHANC', '11C-8260', '0001', 'APAG')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('CHANC', '11C-8261', '0001', 'APAG')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('CHANC', '11C-8261', '0001', 'SIL')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('CHANC', '21E-4440', '0002', 'SIL')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('LEX', '11C-8260', '0001', 'SIL')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('LEX', '11C-8261', '0001', 'APAG')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('LEX', '21E-4440', '0001', 'SIL')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('LEX', '11C-8262', '0001', 'SIL')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code]) VALUES ('LEX', '11C-8263', '0001', 'SIL')Wed, 30 Jul 2014 10:40:36 GMTdwilliscpRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspx[quote][b]dwilliscp (7/27/2014)[/b][hr][quote][b]Jeff Moden (7/25/2014)[/b][hr][quote][b]dwilliscp (7/25/2014)[/b][hr]I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.Ideas?[/quote]Define "Massive" because that's important here. A "Control Cursor" (as opposed to RBAR) may be appropriate here.Also, I'd personally like to know what it is you mean by "from" and "to" part numbers because it doesn't make sense to me to reference a toilet paper holder with hot water heater unless they're attached to each other and in the same bathroom.[/quote]Jeff... they are attached... Ok we have a file that shows all resources in a plant, and the materials that we use in them, during production. What we are trying to do, since our shop floor planning software does not do it, is to go in and generate a list of how long it takes to change a resource from producing part A to part B. So I need to take the list of parts, for each company + plant + resource... and create the from part ... to part. Then we can go in and assign how long it will take to transition from part A to Part B... before we can start to produce part B. Got it? The cursor... that I posted on friday.. ran until I killed it on Sunday Morning... and I was running for only one plant.. and parts that started with Z. This was a list of 362 rows.[/quote]Maybe it's just these old eyes but I see a cursor and sample data with "Material_Number" in them but no "Part_Number". Is "Material_Number" what you're talking about?Also, I'm still not bagging what you're raking about the From_Material and To_Material. To me, your cursor...[code="sql"]declare @plant as varchar(4) , @Resource_Code as varchar(8) , @Material_Number as varchar(18)set nocount on;declare i_cursor insensitive cursorfor Select Plant, Resource_Code, Material_Number From Resource_Matopen i_Cursorwhile @@fetch_status = 0begin insert into trans_Matrix(Company, Plant, Resource_Code, From_Material, To_Material) select 'SPOR' as Company , Plant , Resource_Code , @Material_Number as From_Material , Material_Number as To_Material from Resource_Mat where @Plant = plant and @resource_code = resource_code endfetch next from i_Cursor into @Plant, @Resource_Code, @Material_Number close i_Cursordeallocate i_cursorset nocount off;go [/code]... looks like a massive CROSS JOIN of the Resource_Mat table to itself based on the "plant" and "resource_code". While that doesn't make sense to me (even though you mentioned that in the title of the post), if that's what you really need, the following code should do it for you...[code="sql"] INSERT INTO dbo.Trans_Matrix (Company, Plant, Resource_Code, From_Material, To_Material) SELECT Company = 'SPOR' ,Plant = frommat.Plant ,Resource_Code = frommat.Resource_Code ,From_Material = frommat.Material_Number ,To_Material = tomat.Material_Number FROM dbo.Resource_Mat frommat JOIN dbo.Resource_Mat tomat --This is going to work like a conditional CROSS JOIN ON frommat.Plant = tomat.Plant AND frommat.Resource_Code = tomat.Resource_Code;[/code]... and, compared to the cursor, it will run like the wind generating millions of rows in just a couple of seconds if you have some decent indexing on the Resource_Mat table.Of course, you never answered my question about what you meant by a "massive" table. How many rows does it actually have in it???Last but not least, if you go back and look at HanShi's post (the second post on this thread), he suggested the exact same thing I just did. You just needed to study the code a bit to realize that it's probably exactly what you wanted sans some of your column names and the missing criteria for the Resource_Code.Mon, 28 Jul 2014 19:12:52 GMTJeff ModenRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspxOnce we have sample data to work with it should be a straight forward approach.It looks like you're looking for the setup time per resource and product.If that's the case you'll also need the business hours in order to ignore off-time (e.g. breaks or shutdown time).Furthermore, the results need to be analyzed further to take additional down time into account (e.g. waiting for raw material).@Jeff: the resource in a plant is the bathroom and the time is needed between the "usage of the toilet paper holder" ("part A") and the "hot water heater" ("Part B"). The "setup" that needs to be done is flush the toilet, put on your trousers, close toilet lid, leave the toilet and approach the sink to wash your hands ;-)Sun, 27 Jul 2014 08:14:32 GMTLutzMRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspx[quote][b]Jeff Moden (7/25/2014)[/b][hr][quote][b]dwilliscp (7/25/2014)[/b][hr]I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.Ideas?[/quote]Define "Massive" because that's important here. A "Control Cursor" (as opposed to RBAR) may be appropriate here.Also, I'd personally like to know what it is you mmean by "from" and "to" part numbers because it doesn't make sense to me to reference a toilet paper holder with hot water heater unless they're attached to each other and in the same bathroom.[/quote]Jeff... they are attached... Ok we have a file that shows all resources in a plant, and the materials that we use in them, during production. What we are trying to do, since our shop floor planning software does not do it, is to go in and generate a list of how long it takes to change a resource from producing part A to part B. So I need to take the list of parts, for each company + plant + resource... and create the from part ... to part. Then we can go in and assign how long it will take to transition from part A to Part B... before we can start to produce part B. Got it? The cursor... that I posted on friday.. ran until I killed it on Sunday Morning... and I was running for only one plant.. and parts that started with Z. This was a list of 362 rows.Sun, 27 Jul 2014 07:35:12 GMTdwilliscpRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspx[quote][b]dwilliscp (7/25/2014)[/b][hr]I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.Ideas?[/quote]Define "Massive" because that's important here. A "Control Cursor" (as opposed to RBAR) may be appropriate here.Also, I'd personally like to know what it is you mmean by "from" and "to" part numbers because it doesn't make sense to me to reference a toilet paper holder with hot water heater unless they're attached to each other and in the same bathroom.Fri, 25 Jul 2014 11:12:57 GMTJeff ModenRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspx[quote][b]dwilliscp (7/25/2014)[/b][hr]INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8260', N'0001', N'SIL', N'15.000', N'KG', N'0.100', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'0.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8260', N'0001', N'APAG', N'1000.000', N'KG', N'4.000', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'400.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8261', N'0001', N'APAG', N'1000.000', N'KG', N'4.138', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'600.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8261', N'0001', N'SIL', N'15.000', N'KG', N'0.100', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'0.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'21E-4440', N'0002', N'SIL', N'1000.000', N'KG', N'0.250', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'0.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8260', N'0001', N'SIL', N'1000.000', N'KG', N'1.200', N'H', N'0.000', N'999999999.000', N'FX', N'2', N'4', N'', N'0.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8261', N'0001', N'APAG', N'1000.000', N'KG', N'3.154', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'600.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'21E-4440', N'0001', N'SIL', N'985.000', N'KG', N'0.250', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'KG', N'0.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8262', N'1', N'SIL', N'1018.000', N'KG', N'0.250', N'STD', N'0.000', N'99999999.000', N'EX', N'2', N'4', N'KG', N'18.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8263', N'1', N'SIL', N'208.000', N'KG', N'0.100', N'STD', N'0.000', N'99999999.000', N'60', N'2', N'4', N'kg', N'8.000', N'')[/quote]NONE of your columns in the DDL provided are declared using NVARCHAR, all are VARCHAR. Why are all of your string values preceded by N to indicate UNICODE string values?Fri, 25 Jul 2014 09:53:46 GMTLynn PettisRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspxI used APEX to create the inserts.. and then find and replace to turn it into dummy data.. will just key in some data later this afternoon when I get a chance.Fri, 25 Jul 2014 09:50:47 GMTdwilliscpRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspxYour insert statements don't work. Here is the results.[code]String or binary data would be truncated.The statement has been terminated.Msg 8152, Level 16, State 2, Line 30String or binary data would be truncated.The statement has been terminated.Msg 8152, Level 16, State 2, Line 31String or binary data would be truncated.The statement has been terminated.Msg 8152, Level 16, State 2, Line 32String or binary data would be truncated.The statement has been terminated.Msg 8152, Level 16, State 2, Line 33String or binary data would be truncated.The statement has been terminated.(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)[/code]Fri, 25 Jul 2014 09:40:14 GMTSean LangeRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspxINSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8260', N'0001', N'SIL', N'15.000', N'KG', N'0.100', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'0.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8260', N'0001', N'APAG', N'1000.000', N'KG', N'4.000', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'400.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8261', N'0001', N'APAG', N'1000.000', N'KG', N'4.138', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'600.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8261', N'0001', N'SIL', N'15.000', N'KG', N'0.100', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'0.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'21E-4440', N'0002', N'SIL', N'1000.000', N'KG', N'0.250', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'0.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8260', N'0001', N'SIL', N'1000.000', N'KG', N'1.200', N'H', N'0.000', N'999999999.000', N'FX', N'2', N'4', N'', N'0.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8261', N'0001', N'APAG', N'1000.000', N'KG', N'3.154', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'600.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'21E-4440', N'0001', N'SIL', N'985.000', N'KG', N'0.250', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'KG', N'0.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8262', N'1', N'SIL', N'1018.000', N'KG', N'0.250', N'STD', N'0.000', N'99999999.000', N'EX', N'2', N'4', N'KG', N'18.000', N'')INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8263', N'1', N'SIL', N'208.000', N'KG', N'0.100', N'STD', N'0.000', N'99999999.000', N'60', N'2', N'4', N'kg', N'8.000', N'')Fri, 25 Jul 2014 09:33:41 GMTdwilliscpRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspx[quote][b]dwilliscp (7/25/2014)[/b][hr][quote][b]dwilliscp (7/25/2014)[/b][hr]I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.Ideas?[/quote]You have one table... lets call it Resource_Mat. PK Company, Plant, Resource, Part Number. What I need to do is ..Get the first row from Resource_mat and for every row in the table create a data set that contains:Company, Plant, Resource, [Selected rows' part number], [current rows' part number]The only restriction is that the loop needs to only work on where Comany + Plant + Resournce matches between selected row and current row.This is why I thought about using a cursor.. but the data set is so large, and SQL will throw it all on one CPU... I expect it to take too long.The result of the above query then gets updated/inserted into a table that holds this information about from part .. to part and then we keep up how long it takes to transitition from one part number to a different part number.[/quote]This is NOT the time to use a cursor. You really need to change the way you think about data. You have to think about what you want to do to a column, not a row.If you can post ddl and sample data along with desired output we can show you how to do this in a set based manner.Fri, 25 Jul 2014 09:20:53 GMTSean LangeRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspxCREATE TABLE [dbo].[Resource_Mat]( [Plant] [varchar](4) NOT NULL, [Material_number] [varchar](18) NOT NULL, [Production_Version] [varchar](4) NOT NULL, [Resource_Code] [varchar](8) NOT NULL, [Std_Batch_Size] [varchar](18) NULL, [UOM] [varchar](3) NULL, [Std_Run_Time] [varchar](14) NULL, [Time_UOM] [varchar](3) NULL, [Batch_Min] [varchar](17) NULL, [Batch_Max] [varchar](17) NULL, [Stocking_Strategy] [varchar](2) NULL, [Trans_Type] [varchar](2) NULL, [Recipe_Status] [varchar](4) NULL, [Short_Description] [varchar](40) NULL, [Rounding_Value] [varchar](17) NULL, [Distribution_Key] [varchar](4) NULL, CONSTRAINT [PK_Resource_Mat] PRIMARY KEY CLUSTERED ( [Plant] ASC, [Material_number] ASC, [Production_Version] ASC, [Resource_Code] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOFri, 25 Jul 2014 09:18:48 GMTdwilliscpRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspxI broke down and wrote it as a Cursor... Still running so I do not know if my code is correct yet... I will create a table and some dummy data in the next post.declare @plant as varchar(4) , @Resource_Code as varchar(8) , @Material_Number as varchar(18)set nocount on;declare i_cursor insensitive cursorfor Select Plant, Resource_Code, Material_Number From Resource_Matopen i_Cursorwhile @@fetch_status = 0begin insert into trans_Matrix(Company, Plant, Resource_Code, From_Material, To_Material) select 'SPOR' as Company , Plant , Resource_Code , @Material_Number as From_Material , Material_Number as To_Material from Resource_Mat where @Plant = plant and @resource_code = resource_code endfetch next from i_Cursor into @Plant, @Resource_Code, @Material_Number close i_Cursordeallocate i_cursorset nocount off;goFri, 25 Jul 2014 09:16:13 GMTdwilliscpRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspx[quote][b]dwilliscp (7/25/2014)[/b][hr]I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.Ideas?[/quote]You have one table... lets call it Resource_Mat. PK Company, Plant, Resource, Part Number. What I need to do is ..Get the first row from Resource_mat and for every row in the table create a data set that contains:Company, Plant, Resource, [Selected rows' part number], [current rows' part number]The only restriction is that the loop needs to only work on where Comany + Plant + Resournce matches between selected row and current row.This is why I thought about using a cursor.. but the data set is so large, and SQL will throw it all on one CPU... I expect it to take too long.The result of the above query then gets updated/inserted into a table that holds this information about from part .. to part and then we keep up how long it takes to transitition from one part number to a different part number.Fri, 25 Jul 2014 08:29:58 GMTdwilliscpRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspx[quote][b]dwilliscp (7/25/2014)[/b][hr]I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.Ideas?[/quote]SELECT company, plant, resource, MIN(partno) as [from part number], MAX(part number) as [to part number]FROM tableGROUP BY company, plant, resourceFri, 25 Jul 2014 07:24:23 GMTChrisM@homeRE: Cross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspxA cursor is not the right approach for this scenario. Allthough it is not very clear what your situation is, it sounds like you can just insert the results from two tables joind together into a new table.Table_A holds the company, plant, part, etc. valuesTable_B holds the 'from part', 'to part', and maybe some more relevant columnsThe query would look like this:SELECT {specify the required columns here}INTO {new_table}FROM Table_AINNER JOIN Table_BON Table_A.part = Table_B.from_partFri, 25 Jul 2014 07:23:49 GMTHanShiCross Join Helphttp://www.sqlservercentral.com/Forums/Topic1596189-392-1.aspxI have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.Ideas?Fri, 25 Jul 2014 06:54:50 GMTdwilliscp