Author |
Topic |
folumike
Starting Member
24 Posts |
Posted - 2013-05-16 : 23:54:37
|
I have a table like this: CREATE TABLE [dbo].[tblcustomer]( [custid] [nvarchar](50) NULL, [date1] [datetime] NULL, [Quantity] [float](8) NULL, [invoicenum] [nvarchar](50) NULL, [price] [money] NULL
) ON [PRIMARY]
INSERT tblcustomer (custid, date1, Quantity,invnum, price) VALUES ('0001', '01/01/2013', 60,'INV001/INV002/INV003/INV004/INV005', 30,20,40,45,76) VALUES ('0002', '01/01/2013', 50, 'INV001', 50) VALUES ('0001', '01/01/2013', 30, '', ) VALUES ('0001', '02/01/2013', 70, '', ) VALUES ('0002', '01/01/2013', 74, 'INV020/INV021', 15,30)
How do I split the table with columns (invnum and price) that have delimeters '/' and ','. Note that some of the two columns are empty and some have no delimeters.
Expected OUTPUT custid | date1 | Quantity |invnum | price
tblcustomer
Please help...
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-17 : 00:14:11
|
[code]Use User-Defined function to split CSV Data -- UDF CREATE FUNCTION [dbo].[CustomSplit] (@sep char(1), @s varchar(4000)) RETURNS table AS RETURN ( WITH Pieces(n, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT n + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop > 0 ) SELECT n, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS Val FROM Pieces ) GO
SELECT custid, date1, Quantity, a.Val AS Invoice, b.Val as Price FROM tblcustomer CROSS APPLY CustomSplit('/', invoicenum)a CROSS APPLY CustomSplit(',', price)b [/code]
-- Chandu |
 |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
noblemfd
Starting Member
38 Posts |
Posted - 2013-05-17 : 00:54:25
|
quote: Originally posted by bandi
Use User-Defined function to split CSV Data -- UDF CREATE FUNCTION [dbo].[CustomSplit] (@sep char(1), @s varchar(4000)) RETURNS table AS RETURN ( WITH Pieces(n, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT n + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop > 0 ) SELECT n, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS Val FROM Pieces ) GO
SELECT custid, date1, Quantity, a.Val AS Invoice, b.Val as Price FROM tblcustomer CROSS APPLY CustomSplit('/', invoicenum)a CROSS APPLY CustomSplit(',', price)b
-- Chandu
I checked the site you directed me to, but could not understand it. Please kindly convert the one one you wrote with MSSQL 2005 to MSSQL 2000. THNAKS |
 |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-17 : 01:53:42
|
--May be this? I have no MSSQL 2000 SELECT Temp1.custid ,Temp1.date1 ,Temp1.Quantity ,Temp1.Invoices ,temp2.Prices FROM (SELECT a.Custid, a.date1, a.Quantity ,SUBSTRING('/' + a.invoicenum + '/', n.Number + 1, CHARINDEX('/', '/' + a.invoicenum + '/', n.Number + 1) - n.Number - 1) AS [Invoices] FROM tblcustomer AS a INNER JOIN master..spt_values AS n ON SUBSTRING('/' + a.invoicenum + '/', n.Number, 1) = '/' WHERE n.Type = 'p' AND n.Number > 0 AND n.Number < LEN('/' + a.invoicenum + '/') )Temp1 JOIN (SELECT a.Custid, a.date1, a.Quantity, SUBSTRING(',' + a.price + ',', n.Number + 1, CHARINDEX(',', ',' + a.price + ',', n.Number + 1) - n.Number - 1) AS [Prices] FROM tblcustomer AS a INNER JOIN master..spt_values AS n ON SUBSTRING(',' + a.price + ',', n.Number, 1) = ',' WHERE n.Type = 'p' AND n.Number > 0 AND n.Number < LEN(',' + a.price + ',') )temp2 ON temp2.custid = temp1.custid AND temp2.date1 = Temp1.date1 AND temp2.Quantity = Temp1.Quantity
-- Chandu |
 |
|
folumike
Starting Member
24 Posts |
Posted - 2013-05-21 : 14:39:10
|
MS SQL 2000
Thanks very it works. But the problem is that it duplicates the values of Invoices and Prices, that is those ones it splits. For example if the input is custid | date1 | Quantity |invnum | price 0001 |01/01/2013 |100 | INV001/INV002 | 30,40
It gives: custid | date1 | Quantity |invnum | price 0001 |01/01/2013 |100 | INV001 | 30 0001 |01/01/2013 |100 | INV001 | 40 0001 |01/01/2013 |100 | INV002 | 30 0001 |01/01/2013 |100 | INV002 | 40
Instead of:
custid | date1 | Quantity |invnum | price 0001 |01/01/2013 |100 | INV001 | 30 0001 |01/01/2013 |100 | INV002 | 40
Please kindly help
quote: Originally posted by bandi
--May be this? I have no MSSQL 2000 SELECT Temp1.custid ,Temp1.date1 ,Temp1.Quantity ,Temp1.Invoices ,temp2.Prices FROM (SELECT a.Custid, a.date1, a.Quantity ,SUBSTRING('/' + a.invoicenum + '/', n.Number + 1, CHARINDEX('/', '/' + a.invoicenum + '/', n.Number + 1) - n.Number - 1) AS [Invoices] FROM tblcustomer AS a INNER JOIN master..spt_values AS n ON SUBSTRING('/' + a.invoicenum + '/', n.Number, 1) = '/' WHERE n.Type = 'p' AND n.Number > 0 AND n.Number < LEN('/' + a.invoicenum + '/') )Temp1 JOIN (SELECT a.Custid, a.date1, a.Quantity, SUBSTRING(',' + a.price + ',', n.Number + 1, CHARINDEX(',', ',' + a.price + ',', n.Number + 1) - n.Number - 1) AS [Prices] FROM tblcustomer AS a INNER JOIN master..spt_values AS n ON SUBSTRING(',' + a.price + ',', n.Number, 1) = ',' WHERE n.Type = 'p' AND n.Number > 0 AND n.Number < LEN(',' + a.price + ',') )temp2 ON temp2.custid = temp1.custid AND temp2.date1 = Temp1.date1 AND temp2.Quantity = Temp1.Quantity
-- Chandu
|
 |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-21 : 15:38:33
|
Try 'DISTINCT' as shown in red below:
quote: Originally posted by folumike
MS SQL 2000
Thanks very it works. But the problem is that it duplicates the values of Invoices and Prices, that is those ones it splits. For example if the input is custid | date1 | Quantity |invnum | price 0001 |01/01/2013 |100 | INV001/INV002 | 30,40
It gives: custid | date1 | Quantity |invnum | price 0001 |01/01/2013 |100 | INV001 | 30 0001 |01/01/2013 |100 | INV001 | 40 0001 |01/01/2013 |100 | INV002 | 30 0001 |01/01/2013 |100 | INV002 | 40
Instead of:
custid | date1 | Quantity |invnum | price 0001 |01/01/2013 |100 | INV001 | 30 0001 |01/01/2013 |100 | INV002 | 40
Please kindly help
quote: Originally posted by bandi
--May be this? I have no MSSQL 2000 SELECT DISTINCT Temp1.custid ,Temp1.date1 ,Temp1.Quantity ,Temp1.Invoices ,temp2.Prices FROM (SELECT a.Custid, a.date1, a.Quantity ,SUBSTRING('/' + a.invoicenum + '/', n.Number + 1, CHARINDEX('/', '/' + a.invoicenum + '/', n.Number + 1) - n.Number - 1) AS [Invoices] FROM tblcustomer AS a INNER JOIN master..spt_values AS n ON SUBSTRING('/' + a.invoicenum + '/', n.Number, 1) = '/' WHERE n.Type = 'p' AND n.Number > 0 AND n.Number < LEN('/' + a.invoicenum + '/') )Temp1 JOIN (SELECT a.Custid, a.date1, a.Quantity, SUBSTRING(',' + a.price + ',', n.Number + 1, CHARINDEX(',', ',' + a.price + ',', n.Number + 1) - n.Number - 1) AS [Prices] FROM tblcustomer AS a INNER JOIN master..spt_values AS n ON SUBSTRING(',' + a.price + ',', n.Number, 1) = ',' WHERE n.Type = 'p' AND n.Number > 0 AND n.Number < LEN(',' + a.price + ',') )temp2 ON temp2.custid = temp1.custid AND temp2.date1 = Temp1.date1 AND temp2.Quantity = Temp1.Quantity
-- Chandu
|
 |
|
|
|
|