September 29, 2016 at 1:54 pm
So I am trying to fix something a developer did in our database and I'm having trouble figuring this out.
First about the string itself. It is a double delimited string, the first value is a text description and the second is a unit price as shown below. The two delimiters are the tilde and carrot characters.
Sharpening~11.44^Face Shear~17.16^(1) STD Tip(s) replaced (0.250 kerf)~52.64^
Here is the messed up part. This value is stored on a parent record. Not parent in that the child is in another table, but the child is in the same table, different line number.
Here is the condensed table and some sample data. The Service_Op Column is empty for now.
Company | OrderNum | OrderLine | Part | LineDesc | Pricing | Service_Op
100 | 109187 | 1 | SKP | Sales Kit Part | Sharpening~11.44^Face Shear~17.16^(1) STD Tip(s) replaced (0.250 kerf)~52.64^ |
100 | 109187 | 2 | CS1 | Component1 | |
100 | 109187 | 3 | CS2 | Component2 | |
100 | 109187 | 4 | CS3 | Component3 | |
Here is what I need the data to look like. I need the Service_Op column updated for the child parts as show below. The string length is variable, so the solution would need to be dynamic and the parent isn't always on line 1, but all childs of that parent are in sequential order after the parent.
100 | 109187 | 1 | SKP | Sales Kit Part | Sharpening~11.44^Face Shear~17.16^(1) STD Tip(s) replaced (0.250 kerf)~52.64^ |
100 | 109187 | 2 | CS1 | Component1 | | Sharpening
100 | 109187 | 3 | CS2 | Component2 | | Face Shear
100 | 109187 | 4 | CS3 | Component3 | | (1) STD Tip(s) replaced (0.250 kerf)
Basically I need to split the string by the delimiters, ignore the unit price and if need be create a separate array of just the strings w/o unit prices, then loop the array and join on ordernum and current orderline + the current array position + 1 and update the service_op column with the current array value.
I'm doing this because reporting has become a nightmare.
Is that possible?
September 29, 2016 at 2:48 pm
I'm out of time but perhaps this can get you started. You can use DelimitedSplit8K_LEAD (see link in my signature) to parse the string like this:
SELECT ItemNumber, item = SUBSTRING(item,1, CHARINDEX('~',Item)-1)
FROM dbo.DelimitedSplit8K_LEAD('Sharpening~11.44^Face Shear~17.16^(1) STD Tip(s) replaced (0.250 kerf)~52.64^','^') s1
WHERE CHARINDEX('~',Item) > 0
ItemNumber item
-------------------- ------------------------------------
1 Sharpening
2 Face Shear
3 (1) STD Tip(s) replaced (0.250 kerf)
Then you can join this back to your table. Hopefully that's a start.
-- Itzik Ben-Gan 2001
September 30, 2016 at 11:19 am
Thanks! I will try it out.
October 5, 2016 at 2:18 pm
There's probably a way to do this w/o taking 3 passes at the table but it's not jumping out at me at the moment...
In any case this should get you pretty close.
(note: dbo.SplitCSVToTable8K is just a renamed version of the DelimitedSplit8K [/url]function)
IF OBJECT_ID('tempdb..#TableName', 'U') IS NOT NULL
DROP TABLE #TableName;
CREATE TABLE #TableName (
Company INT NOT NULL,
OrderNum INT NOT NULL,
OrderLine INT,
Part CHAR(3) NOT NULL,
LineDesc VARCHAR(20) NOT NULL,
Service_Op VARCHAR(1000) NULL
);
INSERT #TableName (Company,OrderNum,OrderLine,Part,LineDesc,Service_Op) VALUES
(100, 109187, 1, 'SKP', 'Sales Kit Part', 'Sharpening~11.44^Face Shear~17.16^(1) STD Tip(s) replaced (0.250 kerf)~52.64^'),
(100, 109187, 2, 'CS1', 'Component1', NULL),
(100, 109187, 3, 'CS2', 'Component2', NULL),
(100, 109187, 4, 'CS3', 'Component3', NULL),
(102, 109190, 4, 'SKP', 'Sales Kit Part', 'Sharpening~11.44^Face Shear~17.16^(1) STD Tip(s) replaced (0.250 kerf)~52.64^'),
(102, 109190, 6, 'CS1', 'Component1', NULL),
(102, 109190, 8, 'CS2', 'Component2', NULL),
(102, 109190, 9, 'CS3', 'Component3', NULL),
(105, 109227, 3, 'SKP', 'Sales Kit Part', 'Sharpening~11.44^Face Shear~17.16^(1) STD Tip(s) replaced (0.250 kerf)~52.64^'),
(105, 109227, 5, 'CS1', 'Component1', NULL),
(105, 109227, 7, 'CS2', 'Component2', NULL),
(105, 109227, 9, 'CS3', 'Component3', NULL);
WITH
cte_SplitServiceOpp AS (
SELECT
tn.Company,
tn.OrderNum,
tn.OrderLine,
sc.ItemNumber,
Service_Op = SUBSTRING(sc.Item, 1, CHARINDEX('~', sc.Item) -1)
FROM
#TableName tn
CROSS APPLY dbo.SplitCSVToTable8K(tn.Service_Op, '^') sc
WHERE
Service_Op IS NOT NULL
AND sc.Item <> ''
),
cte_OrderChildren AS (
SELECT
tn.Company,
tn.OrderNum,
tn.OrderLine,
ItemNumber = ROW_NUMBER() OVER (PARTITION BY tn.Company, tn.OrderNum ORDER BY tn.OrderLine)
FROM
#TableName tn
WHERE
tn.Service_Op IS NULL
),
cte_JoinThem AS (
SELECT
oc.Company,
oc.OrderNum,
oc.OrderLine,
sso.Service_Op
FROM
cte_OrderChildren oc
JOIN cte_SplitServiceOpp sso
ON oc.Company = sso.Company
AND oc.OrderNum = sso.OrderNum
AND oc.ItemNumber = sso.ItemNumber
)
SELECT
tn.Company,
tn.OrderNum,
tn.OrderLine,
tn.Part,
tn.LineDesc,
Service_Op = ISNULL(tn.Service_Op, jt.Service_Op)
FROM
#TableName tn
LEFT JOIN cte_JoinThem jt
ON tn.Company = jt.Company
AND tn.OrderNum = jt.OrderNum
AND tn.OrderLine = jt.OrderLine;
Results...
Company OrderNum OrderLine Part LineDesc Service_Op
----------- ----------- ----------- ---- -------------------- ----------------------------------------------------------------
100 109187 1 SKP Sales Kit Part Sharpening~11.44^Face Shear~17.16^(1) STD Tip(s) replaced (0.250 kerf)~52.64^
100 109187 2 CS1 Component1 Sharpening
100 109187 3 CS2 Component2 Face Shear
100 109187 4 CS3 Component3 (1) STD Tip(s) replaced (0.250 kerf)
102 109190 4 SKP Sales Kit Part Sharpening~11.44^Face Shear~17.16^(1) STD Tip(s) replaced (0.250 kerf)~52.64^
102 109190 6 CS1 Component1 Sharpening
102 109190 8 CS2 Component2 Face Shear
102 109190 9 CS3 Component3 (1) STD Tip(s) replaced (0.250 kerf)
105 109227 3 SKP Sales Kit Part Sharpening~11.44^Face Shear~17.16^(1) STD Tip(s) replaced (0.250 kerf)~52.64^
105 109227 5 CS1 Component1 Sharpening
105 109227 7 CS2 Component2 Face Shear
105 109227 9 CS3 Component3 (1) STD Tip(s) replaced (0.250 kerf)
HTH,
Jason
October 6, 2016 at 9:33 am
Here's another option that actually updates the original table...
IF OBJECT_ID('tempdb..#TableName', 'U') IS NOT NULL
DROP TABLE #TableName;
CREATE TABLE #TableName (
Company INT NOT NULL,
OrderNum INT NOT NULL,
OrderLine INT,
Part CHAR(3) NOT NULL,
LineDesc VARCHAR(20) NOT NULL,
Service_Op VARCHAR(1000) NULL
);
INSERT #TableName (Company,OrderNum,OrderLine,Part,LineDesc,Service_Op) VALUES
(100, 109187, 1, 'SKP', 'Sales Kit Part', 'Sharpening~11.44^Face Shear~17.16^(1) STD Tip(s) replaced (0.250 kerf)~52.64^'),
(100, 109187, 2, 'CS1', 'Component1', NULL),
(100, 109187, 3, 'CS2', 'Component2', NULL),
(100, 109187, 4, 'CS3', 'Component3', NULL),
(102, 109190, 4, 'SKP', 'Sales Kit Part', 'Sharpening~11.44^Face Shear~17.16^(1) STD Tip(s) replaced (0.250 kerf)~52.64^'),
(102, 109190, 6, 'CS1', 'Component1', NULL),
(102, 109190, 8, 'CS2', 'Component2', NULL),
(102, 109190, 9, 'CS3', 'Component3', NULL),
(105, 109227, 3, 'SKP', 'Sales Kit Part', 'Sharpening~11.44^Face Shear~17.16^(1) STD Tip(s) replaced (0.250 kerf)~52.64^'),
(105, 109227, 5, 'CS1', 'Component1', NULL),
(105, 109227, 7, 'CS2', 'Component2', NULL),
(105, 109227, 9, 'CS3', 'Component3', NULL);
WITH
cte_SplitServiceOpp AS (
SELECT
tn.Company,
tn.OrderNum,
tn.OrderLine,
sc.ItemNumber,
Service_Op = SUBSTRING(sc.Item, 1, CHARINDEX('~', sc.Item) -1)
FROM
#TableName tn
CROSS APPLY dbo.SplitCSVToTable8K(tn.Service_Op, '^') sc
WHERE
Service_Op IS NOT NULL
AND sc.Item <> ''
),
cte_OrderChildren AS (
SELECT
tn.Company,
tn.OrderNum,
tn.OrderLine,
ItemNumber = ROW_NUMBER() OVER (PARTITION BY tn.Company, tn.OrderNum ORDER BY tn.OrderLine),
tn.Service_Op
FROM
#TableName tn
WHERE
tn.Service_Op IS NULL
)
UPDATE oc SET
oc.Service_Op = sso.Service_Op
FROM
cte_OrderChildren oc
JOIN cte_SplitServiceOpp sso
ON oc.Company = sso.Company
AND oc.OrderNum = sso.OrderNum
AND oc.ItemNumber = sso.ItemNumber;
SELECT * FROM #TableName tn;
October 6, 2016 at 12:44 pm
This is great! I have one question. Since the values are in a table already, would I still need a temp table?
October 6, 2016 at 1:05 pm
Mike-342950 (10/6/2016)
This is great! I have one question. Since the values are in a table already, would I still need a temp table?
No. The temp table is just so you can copy/paste/run the code and see how it works.
-- Itzik Ben-Gan 2001
October 6, 2016 at 2:12 pm
OK Thanks! I will work on it and let you know how it works!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply