August 12, 2015 at 11:24 am
Hello, I am working on moving information from one of our databases into a newer one the company has recently setup. I am working in MS SQL Server 2012 and am trying to Inset/Update (depending on the information) a part table. I will list the two tables I am working on as Old (where I am getting the information) and New (where it is to be inserted into).
The old table has about 250 columns of information, most of which is not needed. The needed information is as follows: Name, ID, Component1, Component1_PIN, Component1_SN, Component1_Description, Component2, Component2_PIN, Component2_SN. The component section repeats up to Component12.
The new table has columns setup as such: Name, ID, Case, CasePIN, CaseSN, Case_Desc, Processor, ProcessorPIN, ProcessorSN, Processor_Description, Memory, MemoryPIN, MemorySN, Memory_Description, etc.
The issue I am having is that in the old table each component can have Case, Processor, Memory, etc in that one column. I need to insert Case, Memory, etc into the correct column in the new table while keeping intact the rest of the information.
Example:
Old Table
Name | ID | Component1 | Component1_PIN | Component1_SN | Component1_Description | Component2 | Component2_PIN | Component2_SN | Component2_Description
Nest8 | 5682 | Case | 901834 | 237848117 | Black, rectangular | Memory | 9081234 | 5398798134 | NULL
Nest8 | 5978 | Case | 901463 | 237848138 | Black, rectangular | Processor | 2394875 | 2903857809 | Bad
Reds3 | 5683 | Memory | 2405 | 89752342 | Crucial | HardDrive | 92387595 | 457982234 | NULL
Bass | 5644 | HardDrive | 79872346 | 5321675789 | NULL | Case | 10984528 | 3498769872 | NULL
I am not sure how to loop through and grab each part and place it in the column it needs to be while keeping it with the ID.
Thank you for any assistance in advance.
August 12, 2015 at 12:13 pm
staticlie (8/12/2015)
Hello, I am working on moving information from one of our databases into a newer one the company has recently setup. I am working in MS SQL Server 2012 and am trying to Inset/Update (depending on the information) a part table. I will list the two tables I am working on as Old (where I am getting the information) and New (where it is to be inserted into).The old table has about 250 columns of information, most of which is not needed. The needed information is as follows: Name, ID, Component1, Component1_PIN, Component1_SN, Component1_Description, Component2, Component2_PIN, Component2_SN. The component section repeats up to Component12.
The new table has columns setup as such: Name, ID, Case, CasePIN, CaseSN, Case_Desc, Processor, ProcessorPIN, ProcessorSN, Processor_Description, Memory, MemoryPIN, MemorySN, Memory_Description, etc.
The issue I am having is that in the old table each component can have Case, Processor, Memory, etc in that one column. I need to insert Case, Memory, etc into the correct column in the new table while keeping intact the rest of the information.
Example:
Old Table
Name | ID | Component1 | Component1_PIN | Component1_SN | Component1_Description | Component2 | Component2_PIN | Component2_SN | Component2_Description
Nest8 | 5682 | Case | 901834 | 237848117 | Black, rectangular | Memory | 9081234 | 5398798134 | NULL
Nest8 | 5978 | Case | 901463 | 237848138 | Black, rectangular | Processor | 2394875 | 2903857809 | Bad
Reds3 | 5683 | Memory | 2405 | 89752342 | Crucial | HardDrive | 92387595 | 457982234 | NULL
Bass | 5644 | HardDrive | 79872346 | 5321675789 | NULL | Case | 10984528 | 3498769872 | NULL
I am not sure how to loop through and grab each part and place it in the column it needs to be while keeping it with the ID.
Thank you for any assistance in advance.
I can't help but wonder why they chose this architecture. Generally, when you have parts that make up what are commonly referred to as "kits", or "assemblies", there will be a separate table with the master part number, and that table will contain the sub-part numbers as separate records, and thus the difficulty of dealing with up to 12 different fields on pretty much every query goes away. The unique key from the parts table should be the foreign key in the kits/assemblies table. Doing otherwise is almost always a bad idea and leads to all kinds of difficulties. What will they do when a part comes along that has more than 12 components? And why are they willing to waste space on what might well be a LOT of parts that don't have more than one or two components ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 12, 2015 at 1:06 pm
Does this work for you?
WITH SampleData AS
(
SELECT *
FROM (VALUES
('Nest8', '5682', 'Case', '901834', '237848117', 'Black, rectangular', 'Memory', '9081234', '5398798134', NULL ),
('Nest8', '5978', 'Case', '901463', '237848138', 'Black, rectangular', 'Processor', '2394875', '2903857809', 'Bad' ),
('Reds3', '5683', 'Memory', '2405', '89752342', 'Crucial', 'HardDrive', '92387595', '457982234', NULL ),
('Bass', '5644', 'HardDrive', '79872346', '5321675789', NULL, 'Case', '10984528', '3498769872', NULL )
) dt(Name,ID,Component1,Component1_PIN,Component1_SN,Component1_Description,Component2,Component2_PIN,Component2_SN,Component2_Description)
), cteUnPivot AS
(
SELECT Name, ID,
MAX(CASE WHEN Component1 = 'Case' THEN Component1_PIN ELSE NULL END) AS CasePin1,
MAX(CASE WHEN Component1 = 'Case' THEN Component1_SN ELSE NULL END) AS CaseSN1,
MAX(CASE WHEN Component1 = 'Case' THEN Component1_Description ELSE NULL END) AS CaseDescription1,
MAX(CASE WHEN Component2 = 'Case' THEN Component1_PIN ELSE NULL END) AS CasePin2,
MAX(CASE WHEN Component2 = 'Case' THEN Component1_SN ELSE NULL END) AS CaseSN2,
MAX(CASE WHEN Component2 = 'Case' THEN Component1_Description ELSE NULL END) AS CaseDescription2,
MAX(CASE WHEN Component1 = 'Memory' THEN Component1_PIN ELSE NULL END) AS MemoryPin1,
MAX(CASE WHEN Component1 = 'Memory' THEN Component1_SN ELSE NULL END) AS MemorySN1,
MAX(CASE WHEN Component1 = 'Memory' THEN Component1_Description ELSE NULL END) AS MemoryDescription1,
MAX(CASE WHEN Component2 = 'Memory' THEN Component1_PIN ELSE NULL END) AS MemoryPin2,
MAX(CASE WHEN Component2 = 'Memory' THEN Component1_SN ELSE NULL END) AS MemorySN2,
MAX(CASE WHEN Component2 = 'Memory' THEN Component1_Description ELSE NULL END) AS MemoryDescription2,
MAX(CASE WHEN Component1 = 'Processor' THEN Component1_PIN ELSE NULL END) AS ProcessorPin1,
MAX(CASE WHEN Component1 = 'Processor' THEN Component1_SN ELSE NULL END) AS ProcessorSN1,
MAX(CASE WHEN Component1 = 'Processor' THEN Component1_Description ELSE NULL END) AS ProcessorDescription1,
MAX(CASE WHEN Component2 = 'Processor' THEN Component1_PIN ELSE NULL END) AS ProcessorPin2,
MAX(CASE WHEN Component2 = 'Processor' THEN Component1_SN ELSE NULL END) AS ProcessorSN2,
MAX(CASE WHEN Component2 = 'Processor' THEN Component1_Description ELSE NULL END) AS ProcessorDescription2,
MAX(CASE WHEN Component1 = 'HardDrive' THEN Component1_PIN ELSE NULL END) AS HardDrivePin1,
MAX(CASE WHEN Component1 = 'HardDrive' THEN Component1_SN ELSE NULL END) AS HardDriveSN1,
MAX(CASE WHEN Component1 = 'HardDrive' THEN Component1_Description ELSE NULL END) AS HardDriveDescription1,
MAX(CASE WHEN Component2 = 'HardDrive' THEN Component1_PIN ELSE NULL END) AS HardDrivePin2,
MAX(CASE WHEN Component2 = 'HardDrive' THEN Component1_SN ELSE NULL END) AS HardDriveSN2,
MAX(CASE WHEN Component2 = 'HardDrive' THEN Component1_Description ELSE NULL END) AS HardDriveDescription2
FROM SampleData
GROUP BY Name, ID
)
SELECT Name, ID, dt.*
FROM cteUnPivot c1
CROSS APPLY (VALUES
(CasePIN1, CaseSN1, CaseDescription1,
MemoryPin1, MemorySN1, MemoryDescription1,
ProcessorPin1, ProcessorSN1, ProcessorDescription1,
HardDrivePin1, HardDriveSN1, HardDriveDescription1),
(CasePin2, CaseSN2, CaseDescription2,
MemoryPin2, MemorySN2, MemoryDescription2,
ProcessorPin2, ProcessorSN2, ProcessorDescription2,
HardDrivePin2, HardDriveSN2, HardDriveDescription2)
) dt(CasePin, CaseSN, CaseDescription,
MemoryPin, MemorySN, MemoryDescription,
ProcessorPin, ProcessorSN, ProcessorDescription,
HardDrivePin, HardDriveSN, HardDriveDescription);
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 12, 2015 at 2:35 pm
Thank you for the solution. I want to make sure I understand what is happening in the example you sent me. It would be possible to select the needed information from a table instead of using this:
SELECT *
FROM (VALUES
('Nest8', '5682', 'Case', '901834', '237848117', 'Black, rectangular', 'Memory', '9081234', '5398798134', NULL ),
('Nest8', '5978', 'Case', '901463', '237848138', 'Black, rectangular', 'Processor', '2394875', '2903857809', 'Bad' ),
('Reds3', '5683', 'Memory', '2405', '89752342', 'Crucial', 'HardDrive', '92387595', '457982234', NULL ),
('Bass', '5644', 'HardDrive', '79872346', '5321675789', NULL, 'Case', '10984528', '3498769872', NULL )
) dt(Name,ID,Component1,Component1_PIN,Component1_SN,Component1_Description,Component2,Component2_PIN,Component2_SN,Component2_Description)
So I would be selecting my needed information from, in this case OldTable, and then working with the data in the second part of the example.
Thank you in advance.
August 12, 2015 at 2:50 pm
staticlie (8/12/2015)
Thank you for the solution. I want to make sure I understand what is happening in the example you sent me. It would be possible to select the needed information from a table instead of using this:SELECT *
FROM (VALUES
('Nest8', '5682', 'Case', '901834', '237848117', 'Black, rectangular', 'Memory', '9081234', '5398798134', NULL ),
('Nest8', '5978', 'Case', '901463', '237848138', 'Black, rectangular', 'Processor', '2394875', '2903857809', 'Bad' ),
('Reds3', '5683', 'Memory', '2405', '89752342', 'Crucial', 'HardDrive', '92387595', '457982234', NULL ),
('Bass', '5644', 'HardDrive', '79872346', '5321675789', NULL, 'Case', '10984528', '3498769872', NULL )
) dt(Name,ID,Component1,Component1_PIN,Component1_SN,Component1_Description,Component2,Component2_PIN,Component2_SN,Component2_Description)
So I would be selecting my needed information from, in this case OldTable, and then working with the data in the second part of the example.
Thank you in advance.
Whoops, I see a problem in the code I sent... when I'm checking Component2, I'm still getting Component1 values. Use this instead:
WITH SampleData AS
(
SELECT *
FROM (VALUES
('Nest8', '5682', 'Case', '901834', '237848117', 'Black, rectangular', 'Memory', '9081234', '5398798134', NULL ),
('Nest8', '5978', 'Case', '901463', '237848138', 'Black, rectangular', 'Processor', '2394875', '2903857809', 'Bad' ),
('Reds3', '5683', 'Memory', '2405', '89752342', 'Crucial', 'HardDrive', '92387595', '457982234', NULL ),
('Bass', '5644', 'HardDrive', '79872346', '5321675789', NULL, 'Case', '10984528', '3498769872', NULL )
) dt(Name,ID,Component1,Component1_PIN,Component1_SN,Component1_Description,Component2,Component2_PIN,Component2_SN,Component2_Description)
), cteUnPivot AS --<< CHANGE TO WITH cteUnPivot AS, remove above lines
(
SELECT Name, ID,
MAX(CASE WHEN Component1 = 'Case' THEN Component1_PIN ELSE NULL END) AS CasePin1,
MAX(CASE WHEN Component1 = 'Case' THEN Component1_SN ELSE NULL END) AS CaseSN1,
MAX(CASE WHEN Component1 = 'Case' THEN Component1_Description ELSE NULL END) AS CaseDescription1,
MAX(CASE WHEN Component2 = 'Case' THEN Component2_PIN ELSE NULL END) AS CasePin2,
MAX(CASE WHEN Component2 = 'Case' THEN Component2_SN ELSE NULL END) AS CaseSN2,
MAX(CASE WHEN Component2 = 'Case' THEN Component2_Description ELSE NULL END) AS CaseDescription2,
MAX(CASE WHEN Component1 = 'Memory' THEN Component1_PIN ELSE NULL END) AS MemoryPin1,
MAX(CASE WHEN Component1 = 'Memory' THEN Component1_SN ELSE NULL END) AS MemorySN1,
MAX(CASE WHEN Component1 = 'Memory' THEN Component1_Description ELSE NULL END) AS MemoryDescription1,
MAX(CASE WHEN Component2 = 'Memory' THEN Component2_PIN ELSE NULL END) AS MemoryPin2,
MAX(CASE WHEN Component2 = 'Memory' THEN Component2_SN ELSE NULL END) AS MemorySN2,
MAX(CASE WHEN Component2 = 'Memory' THEN Component2_Description ELSE NULL END) AS MemoryDescription2,
MAX(CASE WHEN Component1 = 'Processor' THEN Component1_PIN ELSE NULL END) AS ProcessorPin1,
MAX(CASE WHEN Component1 = 'Processor' THEN Component1_SN ELSE NULL END) AS ProcessorSN1,
MAX(CASE WHEN Component1 = 'Processor' THEN Component1_Description ELSE NULL END) AS ProcessorDescription1,
MAX(CASE WHEN Component2 = 'Processor' THEN Component2_PIN ELSE NULL END) AS ProcessorPin2,
MAX(CASE WHEN Component2 = 'Processor' THEN Component2_SN ELSE NULL END) AS ProcessorSN2,
MAX(CASE WHEN Component2 = 'Processor' THEN Component2_Description ELSE NULL END) AS ProcessorDescription2,
MAX(CASE WHEN Component1 = 'HardDrive' THEN Component1_PIN ELSE NULL END) AS HardDrivePin1,
MAX(CASE WHEN Component1 = 'HardDrive' THEN Component1_SN ELSE NULL END) AS HardDriveSN1,
MAX(CASE WHEN Component1 = 'HardDrive' THEN Component1_Description ELSE NULL END) AS HardDriveDescription1,
MAX(CASE WHEN Component2 = 'HardDrive' THEN Component2_PIN ELSE NULL END) AS HardDrivePin2,
MAX(CASE WHEN Component2 = 'HardDrive' THEN Component2_SN ELSE NULL END) AS HardDriveSN2,
MAX(CASE WHEN Component2 = 'HardDrive' THEN Component2_Description ELSE NULL END) AS HardDriveDescription2
FROM SampleData -- << CHANGE TO OldTable>>
GROUP BY Name, ID
)
SELECT Name, ID, dt.*
FROM cteUnPivot c1
CROSS APPLY (VALUES
(CasePIN1, CaseSN1, CaseDescription1,
MemoryPin1, MemorySN1, MemoryDescription1,
ProcessorPin1, ProcessorSN1, ProcessorDescription1,
HardDrivePin1, HardDriveSN1, HardDriveDescription1),
(CasePin2, CaseSN2, CaseDescription2,
MemoryPin2, MemorySN2, MemoryDescription2,
ProcessorPin2, ProcessorSN2, ProcessorDescription2,
HardDrivePin2, HardDriveSN2, HardDriveDescription2)
) dt(CasePin, CaseSN, CaseDescription,
MemoryPin, MemorySN, MemoryDescription,
ProcessorPin, ProcessorSN, ProcessorDescription,
HardDrivePin, HardDriveSN, HardDriveDescription);
And yes, you're correct on how to do this. You can remove the first cte, so it starts with:
WITH cteUnpivot AS
and then in cteUnpivot, change the FROM clause to FROM OldTable. I've put these in remarks in the above code.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply