SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL "Text to Columns" function?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funk.phenomena
Posting Yak Master

Canada
120 Posts

Posted - 09/11/2013 :  10:24:26  Show Profile  Reply with Quote
I have the following Table:

ORDER_ID SKUS

12345678 SCINC, SCNRQ, SRPPR

All the values in the 'SKUs' column are combined together, seperated by a comma.

Is there an easy to way "de-pivot" this so that the Order ID gets repeated as follows:


ORDER_ID SKU

12345678 SCINC
12345678 SCNRQ
12345678 SRPPR


Here's some code:


SELECT ORDER_ID, SKU FROM ORDERS

Edited by - funk.phenomena on 09/11/2013 10:25:44

MuMu88
Aged Yak Warrior

547 Posts

Posted - 09/11/2013 :  10:56:51  Show Profile  Reply with Quote
You need string splitter function to do this. There are several on the web. Here is a good one:
http://www.sqlservercentral.com/articles/Tally+Table/72993/ (The code is in Figure 21).


quote:
Originally posted by funk.phenomena

I have the following Table:

ORDER_ID SKUS

12345678 SCINC, SCNRQ, SRPPR

All the values in the 'SKUs' column are combined together, seperated by a comma.

Is there an easy to way "de-pivot" this so that the Order ID gets repeated as follows:


ORDER_ID SKU

12345678 SCINC
12345678 SCNRQ
12345678 SRPPR


Here's some code:


SELECT ORDER_ID, SKU FROM ORDERS


Go to Top of Page

funk.phenomena
Posting Yak Master

Canada
120 Posts

Posted - 09/11/2013 :  10:57:50  Show Profile  Reply with Quote
Terrific, Thanks!
Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 09/11/2013 :  15:13:37  Show Profile  Reply with Quote
I didn't read the article so for all I know I'm offering up the same solution but I doubt it. Anyways, the concept behind this is called walking the tree, or so I've heard it called. It's not particularly elegant but it works.

The first thing you need is a table that contains a series of numbers starting at 1 up to however many you need. A simple table like the following will do:

create table walkers(zombie int);

insert into walkers values (1),(2),(3).....

There are all kinds of ways to populate the table so I'll skip that and it's probably easier to call it id rather than zombie but you get the obvious tie-in to the Walking Dead.

Here's the code:

DECLARE @var char(30);
DECLARE @var1 char(30);
SET @var = '12345678 SCINC, SCNRQ, SRPPR';
SET @var1 = REPLACE(REPLACE(@var,SUBSTRING(@var,1,CHARINDEX(' ',@var)),''),', ','.')
-- This does two things.  First it removes the order_id from the string and then it converts the comma and space to a period so the function works with PARSENAME (man, used that twice in one day).

SELECT SUBSTRING(@var,1,CHARINDEX(' ',@var)) AS Order_ID,
PARSENAME(@var1,zombie) AS SKU
FROM walkers
WHERE LEN(@var) - LEN(REPLACE(@var,' ','')) >= zombie


If you have more than one record, and obviously you will, you need to join the walkers table to your table. I didn't do that here but any kind of join that limits the counter to the number of sku's will work. A full outer join with a WHERE clause or a non equi join will also work.

Order_ID	SKU
12345678 	SRPPR             
12345678 	SCNRQ
12345678 	SCINC
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/12/2013 :  16:47:09  Show Profile  Reply with Quote
if number of SKUs are small you can use this too


CREATE TABLE OrderTest
(
ORDER_ID int,
SKUS varchar(5000)
)
insert OrderTest
VALUES(12345678,'SCINC,SCNRQ,SRPPR')

SELECT ORDER_ID,t.u.value('.','varchar(100)') AS SKU
FROM (SELECT ORDER_ID,CAST('<SKUs><SKU>' + REPLACE(SKUS,',','</SKU><SKU>') + '</SKU></SKUs>' AS xml) AS SKUXML FROM    OrderTest)o
CROSS APPLY SKUXML.nodes('/SKUs/SKU')t(u)


output
--------------------------------------------
ORDER_ID	SKU
--------------------------------
12345678	SCINC
12345678	SCNRQ
12345678	SRPPR



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000