June 12, 2014 at 6:39 am
Hi,
I've a table as below
custid,companyname,phone,address
2,AAAAA,(222) 222-2222,address 2
3,cust 3,(333) 333-3333,address 3
5,BBBBB,(333) 333-3333,DDDDD
6,cust 6,(222) 222-2222,address 6
7,cust 7,(222) 222-2222,address 7
How to split csv values to new fields. so that the desired output should be as below
custidcompanynamephone address
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB (333) 333-3333 DDDDD
6 cust 6 (222) 222-2222 address 6
7 cust 7 (222) 222-2222 address 7
Please do the needful.
June 12, 2014 at 6:44 am
June 12, 2014 at 8:35 am
Where do you have those csv values?
In a file? a parameter? a table?
June 13, 2014 at 12:45 am
In a Table..
June 13, 2014 at 1:33 am
based on the article Eirikur pointed you to...
SELECT MAX( CASE WHEN ItemNumber = 1 THEN Item END) custid
,MAX( CASE WHEN ItemNumber = 2 THEN Item END) coname
,MAX( CASE WHEN ItemNumber = 3 THEN Item END) phone
,MAX( CASE WHEN ItemNumber = 4 THEN Item END) adddet
FROM csvtable
CROSS APPLY dbo.DelimitedSplit8K(csvdata, ',') split
group by csvdata
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 13, 2014 at 2:49 am
Hi Livingston ,
Thx for ur quick response.
Can you help with out using UDF functions.
June 13, 2014 at 2:56 am
edit>misread the response 😉
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 13, 2014 at 2:58 am
ykonline (6/13/2014)
Hi Livingston ,Thx for ur quick response.
Can you help with out using UDF functions.
any reason why you don't wish to use the function
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 13, 2014 at 2:59 am
ykonline (6/13/2014)
Hi Livingston ,Thx for ur quick response.
Can you help with out using UDF functions.
Copy paste the code out of the UDF into your code. Modify it to your need.
Cry because it is not re-usable.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 13, 2014 at 11:05 pm
As per requirement we are not supposed to use UDF
June 14, 2014 at 12:36 am
who set the requirement?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 14, 2014 at 2:24 am
Below is the query i was looking for 🙂
create table #num (n int not null)
go
declare @i int
set @i = 0
while @i < 1000
begin
insert into #num select (@i)
set @i = @i + 1
end
go
select p.id,[1] as custid,[2] as companyname,[3] as phone,[4] as address
from (
select id
, substring(d, start+2, endPos-Start-2) token
, row_number() over(partition by id
order by start) n
from (
select id
, d
, n start
, charindex(',',d,n+2) endPos
from #num
cross join (select id
, ',' + [custid,companyname,phone,address] +',' as d
from TestFile) m
where n < len(d)-1
and substring(d,n+1,1) = ',')d
) pvt
Pivot ( max(token)for n in ([1],[2],[3],[4]))p
Thx for all
June 14, 2014 at 7:15 am
Here is a solution based (loosely) on DelimiterSplit8K.
😎
USE tempdb;
GO
DECLARE @STGTXT TABLE
(
STGTXT_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,STGTXT_TEXT VARCHAR(MAX) NOT NULL
);
INSERT INTO @STGTXT (STGTXT_TEXT)
VALUES
('custid,companyname,phone,address,')
,('2,AAAAA,(222) 222-2222,address 2')
,('3,cust 3,(333), ,333-3333,address 3')
,('5,BBBBB,(333) 333-3333,DDDDD')
,('6,cust 6,(222) 222-2222,address 6')
,('7,cust 7,(222) 222-22224,4,address 7,PBX 1234,');
DECLARE @DELIMITER VARCHAR(1) = CHAR(44);
;WITH TN(N) AS (SELECT N FROM
(VALUES (NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,DELIM_POS AS
(
SELECT
SX.STGTXT_ID
,NM.N
,SX.STGTXT_TEXT
FROM @STGTXT SX
OUTER APPLY
( SELECT 0 AS N
UNION ALL
SELECT NM.N
FROM
(
SELECT TOP (LEN(SX.STGTXT_TEXT)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM TN T1,TN T2,TN T3,TN T4,TN T5,TN T6,TN T7
) AS NM(N)
WHERE SUBSTRING(SX.STGTXT_TEXT,NM.N,1) = @DELIMITER
) AS NM(N)
)
,SPLITTER AS
(
SELECT
DP.STGTXT_ID
,ROW_NUMBER() OVER
(
PARTITION BY DP.STGTXT_ID
ORDER BY DP.N
) AS PART_NO
,CASE
WHEN DP.N + 1 = LEAD(DP.N,1,DP.N + 1) OVER
(
PARTITION BY DP.STGTXT_ID
ORDER BY DP.N
) THEN NULL
ELSE SUBSTRING(DP.STGTXT_TEXT,DP.N + 1,(LEAD(DP.N,1,DP.N + 1) OVER
(
PARTITION BY DP.STGTXT_ID
ORDER BY DP.N
) - DP.N) -1 )
END AS STR_PART
FROM DELIM_POS DP
)
SELECT
SX.STGTXT_ID
,MAX(CASE WHEN SP.PART_NO = 1 THEN SP.STR_PART END) AS custid
,MAX(CASE WHEN SP.PART_NO = 2 THEN SP.STR_PART END) AS companyname
,MAX(CASE WHEN SP.PART_NO = 3 THEN SP.STR_PART END) AS phone
,MAX(CASE WHEN SP.PART_NO = 4 THEN SP.STR_PART END) AS address
,MAX(CASE WHEN SP.PART_NO = 5 THEN SP.STR_PART END) AS COL05
,MAX(CASE WHEN SP.PART_NO = 6 THEN SP.STR_PART END) AS COL06
FROM @STGTXT SX
OUTER APPLY SPLITTER SP
WHERE SX.STGTXT_ID = SP.STGTXT_ID
AND SX.STGTXT_ID > 1
GROUP BY SX.STGTXT_ID--,NM.N
Results
STGTXT_ID custid companyname phone address COL05 COL06
---------- ------- ------------ ---------------- -------- ---------- ---------
2 2 AAAAA (222) 222-2222 NULL NULL NULL
3 3 cust 3 (333) 333-3333 NULL
4 5 BBBBB (333) 333-3333 NULL NULL NULL
5 6 cust 6 (222) 222-2222 NULL NULL NULL
6 7 cust 7 (222) 222-22224 4 address 7 PBX 1234
June 16, 2014 at 5:15 pm
ykonline (6/13/2014)
As per requirement we are not supposed to use UDF
I suspect the people that made that requirement don't know that UDFs have 3 forms, one of which (the iTVF or "Inline Table Valued Function") will run just as fast as any "regular" code. The DelimitedSplit8K function is one of those.
And, to be honest, anyone that allows the use of a WHILE loop for such things over using a good iTVF, probably shouldn't be writting the requirements. I strongly urge you and the folks spitting out the requirements to do a much deeper study of iTVFs before making blanket statements like "not supposed to use UDF".
Here's one article to get you and those other people started.
http://www.sqlservercentral.com/articles/T-SQL/91724/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2014 at 12:05 am
Jeff Moden (6/16/2014)
ykonline (6/13/2014)
As per requirement we are not supposed to use UDFI suspect the people that made that requirement don't know that UDFs have 3 forms, one of which (the iTVF or "Inline Table Valued Function") will run just as fast as any "regular" code. The DelimitedSplit8K function is one of those.
And, to be honest, anyone that allows the use of a WHILE loop for such things over using a good iTVF, probably shouldn't be writting the requirements. I strongly urge you and the folks spitting out the requirements to do a much deeper study of iTVFs before making blanket statements like "not supposed to use UDF".
Here's one article to get you and those other people started.
+1
I'd choose DelimitedSplit8K for the sake of simplicity and performance.
😎
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply