October 15, 2015 at 1:53 pm
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
October 15, 2015 at 2:02 pm
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
October 15, 2015 at 2:32 pm
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
-- Itzik Ben-Gan 2001
October 16, 2015 at 8:34 am
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
October 16, 2015 at 8:40 am
Use Luis's suggestion, it will work for single valued entities
October 16, 2015 at 8:47 am
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'.
October 16, 2015 at 8:51 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply