Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL "Text to Columns" function?

Author  Topic 

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2013-09-11 : 10:24:26
I have the following Table:
[CODE]
ORDER_ID SKUS

12345678 SCINC, SCNRQ, SRPPR
[/CODE]
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:

[CODE]
ORDER_ID SKU

12345678 SCINC
12345678 SCNRQ
12345678 SRPPR
[/CODE]

Here's some code:


SELECT ORDER_ID, SKU FROM ORDERS

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-09-11 : 10:56:51
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:
[CODE]
ORDER_ID SKUS

12345678 SCINC, SCNRQ, SRPPR
[/CODE]
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:

[CODE]
ORDER_ID SKU

12345678 SCINC
12345678 SCNRQ
12345678 SRPPR
[/CODE]

Here's some code:


SELECT ORDER_ID, SKU FROM ORDERS


Go to Top of Page

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2013-09-11 : 10:57:50
Terrific, Thanks!
Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-09-11 : 15:13:37
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

52326 Posts

Posted - 2013-09-12 : 16:47:09
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
   

- Advertisement -