May 22, 2014 at 9:13 pm
IF OBJECT_ID('tempdb..#tempTEST') IS NOT NULL DROP TABLE #tempTEST;
CREATE TABLE #tempTEST(
RowID INT
,Name VARCHAR(10)
,DataCSV VARCHAR(100)
PRIMARY KEY (RowID)
)
INSERT INTO #tempTEST (RowID,Name,DataCSV) VALUES (1,'Bob','X_1,C_2,F_7')
INSERT INTO #tempTEST (RowID,Name,DataCSV) VALUES (2,'Ian','X_1,F_7')
INSERT INTO #tempTEST (RowID,Name,DataCSV) VALUES (3,'Tim','P_1,F_7')
SELECT * FROM #tempTEST
IF OBJECT_ID('tempdb..#tempTEST') IS NOT NULL DROP TABLE #tempTEST;
-- CSV function
CREATE FUNCTION [dbo].[CSVtoTable_DHBItem]
(
@LIST VARCHAR(5000),
@Delimeter VARCHAR(1)
)
RETURNS @RET1 TABLE (RESULT VARCHAR(50))
AS
BEGIN
DECLARE @RET TABLE(RESULT VARCHAR(50))
IF LTRIM(RTRIM(@LIST))='' RETURN
DECLARE @START BIGINT
DECLARE @LASTSTART BIGINT
SET @LASTSTART=0
SET @START=CHARINDEX(@Delimeter,@LIST,0)
IF @START=0
INSERT INTO @RET VALUES(SUBSTRING(@LIST,0,LEN(@LIST)+1))
WHILE(@START >0)
BEGIN
INSERT INTO @RET VALUES(SUBSTRING(@LIST,@LASTSTART,@START-@LASTSTART))
SET @LASTSTART=@START+1
SET @START=CHARINDEX(@Delimeter,@LIST,@START+1)
IF(@START=0)
INSERT INTO @RET VALUES(SUBSTRING(@LIST,@LASTSTART,LEN(@LIST)+1))
END
INSERT INTO @RET1 SELECT * FROM @RET
RETURN
END
HI,
Trying to get this out put....got me stumped...
1, Bob, X_1
1, Bob, C_2
1, Bob, F_7
2, Ian, X_1
2, Ian, F_7
3, Tim, P_1
3, Tim, F_7
Any ideas:-)
May 22, 2014 at 9:20 pm
Try...
SELECT T.Name, Val.Result FROM #tempTEST t
CROSS APPLY [dbo].[CSVtoTable_DHBItem] (t.DataCSV, ',') Val
May 22, 2014 at 9:54 pm
Thanks... and perfect...:cool:
May 23, 2014 at 7:25 am
I would HIGHLY recommend that you read the article in my signature about splitting strings. In there you will find a different way of splitting strings that will blow the doors off that while loop splitter for performance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply