Split values in the column

Question

Post reply

Split values in the column

SSRS Newbie

Hall of Fame

Points: 3244
More actions
October 15, 2015 at 1:53 pm

#321543

Hello All,
I've a table that has salescode(124!080) and salesamount(125.65!19.25) and I need to split the columns. Salesman(124) has commission(125.65). Here is the DDL:
USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
, InvNoVARCHAR(10) NOT NULL
, SalesCode NCHAR(80) NOT NULL
, Amount NCHAR(80) NOT NULL
);
INSERT INTO @TEST_DATA (InvNo, SalesCode, Amount)
VALUES
('20001', '080!124', '25.36!6.34')
,('20002', '124!169', '136.55!68.28')
,('20003', '125!095', '174.48!43.62')
,('20004', '165!166', '138.00!34.50')
;
SELECT
J.DT_ID
,J.InvNo
,J.SalesCode
,J.Amount
FROM @TEST_DATA J

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

You could use the DelimitedSplit8k found in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

SELECT

J.DT_ID

,J.InvNo

,sc.Item AS SalesCode

,a.Item AS Amount

FROM @TEST_DATA J

CROSS APPLY dbo.DelimitedSplit8K( J.SalesCode, '!') sc

CROSS APPLY dbo.DelimitedSplit8K( J.Amount, '!') a

WHERE sc.ItemNumber = a.ItemNumber

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

You can also do this:

SELECT

J.DT_ID

,J.InvNo

,Salesmman = SUBSTRING(J.SalesCode,1,CHARINDEX('!',J.SalesCode)-1)

,Commision = SUBSTRING(J.Amount,1,CHARINDEX('!',J.Amount)-1)

FROM @TEST_DATA J

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

SSRS Newbie Hall of Fame Points: 3244 More actions · Answer 3

All-I appreciate your response. In the salescode column there is single salescode also. How do I handle it? Here is the updated DDL. Is there a way to create 2 rows if there are two salescode (080!124) and single row if there is single salescode (080)?

Again, thank you for your response.

DECLARE @TEST_DATA TABLE

(

DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,

InvNo VARCHAR(10) NOT NULL ,

SalesCode NCHAR(80) NOT NULL ,

Amount NCHAR(80) NOT NULL

);

INSERT INTO @TEST_DATA (InvNo, SalesCode, Amount)

VALUES

('20001', '080!124', '25.36!6.34') ,

('20002', '124!169', '136.55!68.28') ,

('20003', '125!095', '174.48!43.62') ,

('20004', '165!166', '138.00!34.50'),

('20005', '124', '125.25'),

('20006', '080', '25')

;

SELECT

J.DT_ID

,J.InvNo

,J.SalesCode

,J.Amount

FROM @TEST_DATA J

Ant-Green SSC Guru Points: 113446 More actions · Answer 4

Use Luis's suggestion, it will work for single valued entities

SSRS Newbie Hall of Fame Points: 3244 More actions · Answer 5

anthony- I tried using Luis's code and got below error message. Any inputs on what I may be doing wrong. Thanks.

Msg 208, Level 16, State 1, Line 19

Invalid object name 'tempdb.dbo.DelimitedSplit8K'.

Ant-Green SSC Guru Points: 113446 More actions · Answer 6

The splitter function doesn't exist in TempDB.

Not ideal creating the function in TempDB as after a restart of SQL the function will be missing, unless you create it in MODEL also but then any new database you create will also get the function.

Create the function in a userdatabase and run your query in that databases context