October 14, 2015 at 7:26 am
I have the following line which is delimiter-ed by a tab (CHAR(9)) and need to have them broken into their own columns. Does anyone have an idea on the best way to have this done?
vendor Description Vendor Part# Part#_in_Epicor Stock EA_UOM New_Thomas_Cost New Proposed List Price New_Manf_List New_GP Increase New_cs_Cost New_cs_List manu_List CS_Qty New_CS_GP CS_discount Curr_Cost Curr_List Prev_GP Start_date Purch_UOM Supp_name Price_Comment
October 14, 2015 at 7:47 am
jonathanm 4432 (10/14/2015)
I have the following line which is delimiter-ed by a tab (CHAR(9)) and need to have them broken into their own columns. Does anyone have an idea on the best way to have this done?vendor Description Vendor Part# Part#_in_Epicor Stock EA_UOM New_Thomas_Cost New Proposed List Price New_Manf_List New_GP Increase New_cs_Cost New_cs_List manu_List CS_Qty New_CS_GP CS_discount Curr_Cost Curr_List Prev_GP Start_date Purch_UOM Supp_name Price_Comment
Jeff's article at http://www.sqlservercentral.com/articles/Tally+Table/72993/ is what you're looking for. The DelimitedSplit8K function will do what you need to do and do it quickly. Here's an illustration of the split:
WITH cteData AS (
SELECT your_column = 'vendor name' + CHAR(9) + 'some description' + CHAR(9) + 'somenumber'
)
SELECT s.ItemNumber, s.Item
FROM cteData d
CROSS APPLY dbo.DelimitedSplit8K(d.your_column, CHAR(9)) s
ORDER BY s.ItemNumber;
To break the values out into their own columns instead of rows, you can use a crosstab query like this:
WITH cteData AS (
SELECT your_column = 'vendor name' + CHAR(9) + 'some description' + CHAR(9) + 'somenumber' UNION ALL
SELECT your_column = 'name 2' + CHAR(9) + 'description 2' + CHAR(9) + 'number2'
)
SELECT d.your_column,
vendor = MAX(CASE WHEN s.ItemNumber = 1 THEN s.Item END),
description = MAX(CASE WHEN s.ItemNumber = 2 THEN s.Item END),
part_number = MAX(CASE WHEN s.ItemNumber = 3 THEN s.Item END)
FROM cteData d
CROSS APPLY dbo.DelimitedSplit8K(d.your_column, CHAR(9)) s
GROUP BY d.your_column;
October 14, 2015 at 12:12 pm
Worked like a charm. Thank you very much.
October 14, 2015 at 12:34 pm
jonathanm 4432 (10/14/2015)
Worked like a charm. Thank you very much.
Glad to hear it. Thanks for the feedback.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply