split 2 fields in tandem

Question

Hi Guys

I have a code block here ::
USE [Parcelforce_Int]
GO
/****** Object: Table [dbo].[WooCommerce_Orders] Script Date: 26/10/2020 09:04:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WooCommerce_Orders](
[BillingFirstName] nvarchar NULL,
[BillingLastName] nvarchar NULL,
[LineItemsName] nvarchar NULL,
[LineItemsQuantity] nvarchar NULL,
[LineItemsSku] nvarchar NULL,
[LineItemsTotal] nvarchar NULL,
[LineItemsPrice] nvarchar NULL,
[LineItemsSubtotal] nvarchar NULL,
[LineItemsMetaDataValue] nvarchar NULL,
[LineItemsProductID] nvarchar NULL,
[LineItemsTotalTax] nvarchar NULL,
[LineItemsSubtotalTax] nvarchar NULL,
[LineItemsTaxClass] nvarchar NULL,
[LineItemsVariationID] nvarchar NULL,
[LineItemsMetaDataKey] nvarchar NULL,
[LineItemsID] nvarchar NULL,
[LineItemsMetaDataID] nvarchar NULL,
[LineItemsMetaDataValue740MnmChildID] nvarchar NULL,
[LineItemsMetaDataValue740ProductID] nvarchar NULL,
[LineItemsMetaDataValue740Quantity] nvarchar NULL,
[LineItemsMetaDataValue740Variation] nvarchar NULL,
[LineItemsMetaDataValue740VariationID] nvarchar NULL,
[LineItemsMetaDataValue747MnmChildID] nvarchar NULL,
[LineItemsMetaDataValue747ProductID] nvarchar NULL,
[LineItemsMetaDataValue747Quantity] nvarchar NULL,
[LineItemsMetaDataValue747Variation] nvarchar NULL,
[LineItemsMetaDataValue747VariationID] nvarchar NULL,
[LineItemsMetaDataValue749MnmChildID] nvarchar NULL,
[LineItemsMetaDataValue749ProductID] nvarchar NULL,
[LineItemsMetaDataValue749Quantity] nvarchar NULL,
[LineItemsMetaDataValue749Variation] nvarchar NULL,
[LineItemsMetaDataValue749VariationID] nvarchar NULL,
[LineItemsMetaDataValue751MnmChildID] nvarchar NULL,
[LineItemsMetaDataValue751ProductID] nvarchar NULL,
[LineItemsMetaDataValue751Quantity] nvarchar NULL,
[LineItemsMetaDataValue751Variation] nvarchar NULL,
[LineItemsMetaDataValue751VariationID] nvarchar NULL,
[LineItemsMetaDataValue752MnmChildID] nvarchar NULL,
[LineItemsMetaDataValue752ProductID] nvarchar NULL,
[LineItemsMetaDataValue752Quantity] nvarchar NULL,
[LineItemsMetaDataValue752Variation] nvarchar NULL,
[LineItemsMetaDataValue752VariationID] nvarchar NULL,
[LineItemsMnmChildItems] nvarchar NULL,
[LineItemsMnmChildOf] nvarchar NULL,
[LineItemsTaxes] nvarchar NULL,
[BillingEmail] nvarchar NULL,
[OrderRefNumber] nvarchar NULL,
[Total] nvarchar NULL,
[BillingPhone] nvarchar NULL,
[ID] nvarchar NULL,
[BillingCity] nvarchar NULL,
[BillingAddress1] nvarchar NULL,
[BillingPostcode] nvarchar NULL,
[DateCreated] nvarchar NULL,
[ShippingLastName] nvarchar NULL,
[BillingCountry] nvarchar NULL,
[BillingState] nvarchar NULL,
[ShippingCity] nvarchar NULL,
[ShippingFirstName] nvarchar NULL,
[OrderStatus] nvarchar NULL,
[BillingCompany] nvarchar NULL,
[PaymentMethodTitle] nvarchar NULL,
[ShippingAddress1] nvarchar NULL,
[ShippingPostcode] nvarchar NULL,
[DatePaid] nvarchar NULL,
[ShippingLinesMethodTitle] nvarchar NULL,
[ShippingLinesTotal] nvarchar NULL,
[ShippingLinesMetaDataValue] nvarchar NULL,
[ShippingLinesID] nvarchar NULL,
[ShippingLinesInstanceID] nvarchar NULL,
[ShippingLinesMetaDataID] nvarchar NULL,
[ShippingLinesMetaDataKey] nvarchar NULL,
[ShippingLinesMethodID] nvarchar NULL,
[ShippingLinesTaxes] nvarchar NULL,
[ShippingLinesTotalTax] nvarchar NULL,
[BillingAddress2] nvarchar NULL,
[ShippingCountry] nvarchar NULL,
[ShippingState] nvarchar NULL,
[ShippingAddress2] nvarchar NULL,
[PaymentMethod] nvarchar NULL,
[ShippingTotal] nvarchar NULL,
[CustomerID] nvarchar NULL,
[Currency] nvarchar NULL,
[DiscountTotal] nvarchar NULL,
[ShippingCompany] nvarchar NULL,
[TransactionID] nvarchar NULL,
[MetaDataValue] nvarchar NULL,
[MetaDataKey] nvarchar NULL,
[MetaDataID] nvarchar NULL,
[TotalTax] nvarchar NULL,
[CouponLines] nvarchar NULL,
[CurrencySymbol] nvarchar NULL,
[OrderKey] nvarchar NULL,
[LineItems] nvarchar NULL,
[DateModified] nvarchar NULL,
[ShippingTax] nvarchar NULL,
[DateCompleted] nvarchar NULL,
[CreatedVia] nvarchar NULL,
[checkout] nvarchar NULL,
[CustomerIpAddress] nvarchar NULL,
[DiscountTax] nvarchar NULL,
[MetaData] nvarchar NULL,
[ShippingLines] nvarchar NULL,
[ParentID] nvarchar NULL,
[CustomerUserAgent] nvarchar NULL,
[FeeLines] nvarchar NULL,
[Refunds] nvarchar NULL,
[TaxLines] nvarchar NULL,
[CartHash] nvarchar NULL,
[CartTax] nvarchar NULL,
[Version] nvarchar NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
INSERT [dbo].[WooCommerce_Orders] ([BillingFirstName], [BillingLastName], [LineItemsName], [LineItemsQuantity], [LineItemsSku], [LineItemsTotal], [LineItemsPrice], [LineItemsSubtotal], [LineItemsMetaDataValue], [LineItemsProductID], [LineItemsTotalTax], [LineItemsSubtotalTax], [LineItemsTaxClass], [LineItemsVariationID], [LineItemsMetaDataKey], [LineItemsID], [LineItemsMetaDataID], [LineItemsMetaDataValue740MnmChildID], [LineItemsMetaDataValue740ProductID], [LineItemsMetaDataValue740Quantity], [LineItemsMetaDataValue740Variation], [LineItemsMetaDataValue740VariationID], [LineItemsMetaDataValue747MnmChildID], [LineItemsMetaDataValue747ProductID], [LineItemsMetaDataValue747Quantity], [LineItemsMetaDataValue747Variation], [LineItemsMetaDataValue747VariationID], [LineItemsMetaDataValue749MnmChildID], [LineItemsMetaDataValue749ProductID], [LineItemsMetaDataValue749Quantity], [LineItemsMetaDataValue749Variation], [LineItemsMetaDataValue749VariationID], [LineItemsMetaDataValue751MnmChildID], [LineItemsMetaDataValue751ProductID], [LineItemsMetaDataValue751Quantity], [LineItemsMetaDataValue751Variation], [LineItemsMetaDataValue751VariationID], [LineItemsMetaDataValue752MnmChildID], [LineItemsMetaDataValue752ProductID], [LineItemsMetaDataValue752Quantity], [LineItemsMetaDataValue752Variation], [LineItemsMetaDataValue752VariationID], [LineItemsMnmChildItems], [LineItemsMnmChildOf], [LineItemsTaxes], [BillingEmail], [OrderRefNumber], [Total], [BillingPhone], [ID], [BillingCity], [BillingAddress1], [BillingPostcode], [DateCreated], [ShippingLastName], [BillingCountry], [BillingState], [ShippingCity], [ShippingFirstName], [OrderStatus], [BillingCompany], [PaymentMethodTitle], [ShippingAddress1], [ShippingPostcode], [DatePaid], [ShippingLinesMethodTitle], [ShippingLinesTotal], [ShippingLinesMetaDataValue], [ShippingLinesID], [ShippingLinesInstanceID], [ShippingLinesMetaDataID], [ShippingLinesMetaDataKey], [ShippingLinesMethodID], [ShippingLinesTaxes], [ShippingLinesTotalTax], [BillingAddress2], [ShippingCountry], [ShippingState], [ShippingAddress2], [PaymentMethod], [ShippingTotal], [CustomerID], [Currency], [DiscountTotal], [ShippingCompany], [TransactionID], [MetaDataValue], [MetaDataKey], [MetaDataID], [TotalTax], [CouponLines], [CurrencySymbol], [OrderKey], [LineItems], [DateModified], [ShippingTax], [DateCompleted], [CreatedVia], [checkout], [CustomerIpAddress], [DiscountTax], [MetaData], [ShippingLines], [ParentID], [CustomerUserAgent], [FeeLines], [Refunds], [TaxLines], [CartHash], [CartTax], [Version]) VALUES (N'Kate', N'Wright', N'MIXED PACK OF 6 COCKTAILS,PORNSTAR MARTINI,ESPRESSO MARTINI,SEX ON THE BEACH,STRAWBERRY DAIQUIRI,MOJITO', N'1,1,1,1,1,2', NULL, N'16.80,0.00,0.00,0.00,0.00,0.00', N'16.8,0,0,0,0,0', N'16.80,0.00,0.00,0.00,0.00,0.00', N'6,991081e3fd123253d35710fc6770b1c3,no,991081e3fd123253d35710fc6770b1c3,no,991081e3fd123253d35710fc6770b1c3,no,991081e3fd123253d35710fc6770b1c3,no,991081e3fd123253d35710fc6770b1c3,no,991081e3fd123253d35710fc6770b1c3,no', N'667,747,750,752,751,740', N'0.00,0.00,0.00,0.00,0.00,0.00', N'0.00,0.00,0.00,0.00,0.00,0.00', NULL, N'0,0,0,0,0,0', N'mnm_container_size,_mnm_config,_mnm_cart_key,_per_product_pricing,_bundle_weight,_mnm_container,_mnm_item_needs_shipping,_mnm_container,_mnm_item_needs_shipping,_mnm_container,_mnm_item_needs_shipping,_mnm_container,_mnm_item_needs_shipping,_mnm_container,_mnm_item_needs_shipping', N'2512,2513,2514,2515,2516,2517', N'25885,25886,25887,25888,25889,25899,25900,25910,25911,25921,25922,25932,25933,25943,25944', N'740', N'740', N'2', NULL, N'0', N'747', N'747', N'1', NULL, N'0', NULL, NULL, NULL, NULL, NULL, N'751', N'751', N'1', NULL, N'0', N'752', N'752', N'1', NULL, N'0', N'2513,2514,2515,2516,2517', N'2512,2512,2512,2512,2512', NULL, N'katewright30@hotmail.co.uk', N'1560', N'22.75', N'07896656745', N'1560', N'LIVERPOOL', N'74 Burghill Road', N'L12 0BS', N'2020-10-13T13:25:53', N'Wright', N'GB', N'Merseyside', N'LIVERPOOL', N'Kate', N'processing', NULL, N'Credit Card (Stripe)', N'74 Burghill Road', N'L12 0BS', N'2020-10-13T13:25:57', N'DPD24 UK Tracked', N'5.95', N'MIXED PACK OF 6 COCKTAILS × 1', N'2518', N'9', N'25950', N'Items', N'flat_rate', NULL, N'0.00', NULL, N'GB', N'Merseyside', NULL, N'stripe', N'5.95', N'0', N'GBP', N'0.00', NULL, N'ch_1HbnVnAjH4sRpdi1UQEUad80', N'https://www.tappdcocktails.com/?adscale=1&utm_campaign=adscale_11313341651&utm_source=adscale_google&utm_medium=Ads&utm_campaign_name=Brand+%28UK+Bot+Excl%29,07896656745,no,1665640813.1602594993,yes,yes,cus_ICBt9bcNTlB8TK,src_1HbnVjAjH4sRpdi1FELqSbtL,pi_1HbnVmAjH4sRpdi1mVHvylw2,yes,0.52,22.23,GBP,yes,1560,1,1,yes', N'mailchimp_woocommerce_campaign_id,mailchimp_woocommerce_landing_site,_shipping_phone,is_vat_exempt,_wc_google_analytics_pro_identity,_wc_google_analytics_pro_placed,_wc_facebook_for_woocommerce_order_placed,_stripe_customer_id,_stripe_source_id,_stripe_intent_id,_stripe_charge_captured,_stripe_fee,_stripe_net,_stripe_currency,_wc_google_analytics_pro_tracked,_pip_invoice_number,_wc_pip_invoice_email_count,_wc_pip_packing_list_email_count,_wc_facebook_for_woocommerce_purchase_tracked', N'25885,25886,25887,25888,25889,25899,25900,25910,25911,25921,25922,25932,25933,25943,25944', N'0.00', NULL, N'£', N'wc_order_1aKmDyBlSascP', N'id: 2512
meta_data: [{''id'': 25885, ''key'': ''mnm_container_size'', ''value'': ''6''}, {''id'': 25886, ''key'': ''_mnm_config'', ''value'': {''740'': {''mnm_child_id'': 740, ''product_id'': 740, ''variation_id'': 0, ''quantity'': 2, ''variation'': []}, ''747'': {''mnm_child_id'': 747, ''product_id'': 747, ''variation_id'': 0, ''quantity'': 1, ''variation'': []}, ''750'': {''mnm_child_id'': 750, ''product_id'': 750, ''variation_id'': 0, ''quantity'': 1, ''variation'': []}, ''751'': {''mnm_child_id'': 751, ''product_id'': 751, ''variation_id'': 0, ''quantity'': 1, ''variation'': []}, ''752'': {''mnm_child_id'': 752, ''product_id'': 752, ''variation_id'': 0, ''quantity'': 1, ''variation'': []}}}, {''id'': 25887, ''key'': ''_mnm_cart_key'', ''value'': ''991081e3fd123253d35710fc6770b1c3''}, {''id'': 25888, ''key'': ''_per_product_pricing'', ''value'': ''no''}, {''id'': 25889, ''key'': ''_bundle_weight'', ''value'': ''''}]
mnm_child_items: [2513, 2514, 2515, 2516, 2517]
mnm_child_of: 
name: MIXED PACK OF 6 COCKTAILS
price: 16.8
product_id: 667
quantity: 1
sku: 
subtotal: 16.80
subtotal_tax: 0.00
tax_class: 
taxes: []
total: 16.80
total_tax: 0.00
variation_id: 0

id: 2513
meta_data: [{''id'': 25899, ''key'': ''_mnm_container'', ''value'': ''991081e3fd123253d35710fc6770b1c3''}, {''id'': 25900, ''key'': ''_mnm_item_needs_shipping'', ''value'': ''no''}]
mnm_child_items: []
mnm_child_of: 2512
name: PORNSTAR MARTINI
price: 0
product_id: 747
quantity: 1
sku: 
subtotal: 0.00
subtotal_tax: 0.00
tax_class: 
taxes: []
total: 0.00
total_tax: 0.00
variation_id: 0

id: 2514
meta_data: [{''id'': 25910, ''key'': ''_mnm_container'', ''value'': ''991081e3fd123253d35710fc6770b1c3''}, {''id'': 25911, ''key'': ''_mnm_item_needs_shipping'', ''value'': ''no''}]
mnm_child_items: []
mnm_child_of: 2512
name: ESPRESSO MARTINI
price: 0
product_id: 750
quantity: 1
sku: 
subtotal: 0.00
subtotal_tax: 0.00
tax_class: 
taxes: []
total: 0.00
total_tax: 0.00
variation_id: 0

id: 2515
meta_data: [{''id'': 25921, ''key'': ''_mnm_container'', ''value'': ''991081e3fd123253d35710fc6770b1c3''}, {''id'': 25922, ''key'': ''_mnm_item_needs_shipping'', ''value'': ''no''}]
mnm_child_items: []
mnm_child_of: 2512
name: SEX ON THE BEACH
price: 0
product_id: 752
quantity: 1
sku: 
subtotal: 0.00
subtotal_tax: 0.00
tax_class: 
taxes: []
total: 0.00
total_tax: 0.00
variation_id: 0

id: 2516
meta_data: [{''id'': 25932, ''key'': ''_mnm_container'', ''value'': ''991081e3fd123253d35710fc6770b1c3''}, {''id'': 25933, ''key'': ''_mnm_item_needs_shipping'', ''value'': ''no''}]
mnm_child_items: []
mnm_child_of: 2512
name: STRAWBERRY DAIQUIRI
price: 0
product_id: 751
quantity: 1
sku: 
subtotal: 0.00
subtotal_tax: 0.00
tax_class: 
taxes: []
total: 0.00
total_tax: 0.00
variation_id: 0

The format that these rows come through on isnt working for me,I need to select ::

The Firstname + lastname as FullName,
The Order Ref (Called number)
The first item from LineItemsName as BoxType
The second onward from Lineitemsname Name as a list/column (current it comes through as a comma seperated list)
The quantities of each item from Lineitemsquantity as a column called Itemcount (currently comes as a comma seperated list also

Im not quite sure how to achieve this would prefer it as a view so i can feed it via SSRS or just the code to generate the list

Thanks so much!

J

Answer

Hi @James O'Hara ，

The code you provided is too readable, and the insert statement seems incorrect. Can you provide a minimal example (including table data and insert data) and the output you expect, so that we can get the right direction and do some tests. According to your description, it seems to split the field. The following example can show you how to split the field:

        CREATE TABLE test(id INT PRIMARY KEY,name VARCHAR(20) NOT NULL,description VARCHAR(100) null)  
        INSERT INTO dbo.test  
        VALUES(1, 'A','aa,bb'),(2, 'B','aa,bb,cc'),  
              (4, 'C','cc'),(5, 'D','bb,cc,dd')  
          
        select * from test  
          
        SELECT t.id,t.name,t.description,v.value  
        FROM test t  
        CROSS APPLY STRING_SPLIT(t.description, ',')v;  
          
        drop table test

For more details, please refer to:STRING_SPLIT (Transact-SQL)

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

That format looks like a complete nightmare. This is definitely not what a relational database is designed to handle. In a relational database, a cell is supposed to hold an atomic value. So if there is any possibility for a redesign, you should absolutely head for that.

Getting this in a to view? Not likely. You will need to break it up in temp tables anyway.

I really don't want to dig into this table, and the INSERT statement appears incomplete anyway - I can't see any closing paranthesis.

But the title of your question reminds a lot about this current thread where you can get some ideas:
https://learn.microsoft.com/en-us/answers/questions/137982/split-pipe-delimited-columns-in-tsql.html

Share via

split 2 fields in tandem

2 answers

Your answer