Split Delimited String Across Fixed Number Of Columns

Question

Post reply

Split Delimited String Across Fixed Number Of Columns

LoosinMaMind

Hall of Fame

Points: 3366
More actions
May 20, 2016 at 5:09 am

#316720

I have the following in a table and need to transfer the single delimited string out into 99 Columns.
I'm afraid that the 99 Columns is a fixed template but i know from experience of the dataset that we will never reach 99 so I WILL need all 99 Column sin the output but know that they will not all be filled.
Please find my demo script below along with a sample if the expected output.
Thanks in advance as always for any help received.
CREATE TABLE #temptable ( [Diagnoses_For_Spell] nvarchar(max) )
INSERT INTO #temptable
VALUES
( N'I64X,Z755,F03X,M819,Z755,F03X,M819,Z755' ),
( N'F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755' ),
( N'E110,I10X,Z755,F03X,M819,Z755,F03X,M819,Z755' )
SELECT * FROM #temptable
DROP TABLE #temptable
--RESULTS
Diag_1Diag_2Diag_3Diag_4Diag_5Diag_6Diag_7Diag_8Diag_9Diag_10Diag_11Diag_12Diag_13Diag_14Diag_15Diag_16….To 99
I64XZ755F03XM819Z755F03XM819Z755
F03XM819Z755F03XM819Z755F03XM819Z755F03XM819Z755
E110I10XZ755F03XM819Z755F03XM819Z755

Viewing 14 posts - 1 through 13 (of 13 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Partial solution. Will only work if all elements are four characters long (which they are in your sample data).

SELECT

SUBSTRING(Diagnoses_For_Spell,1,4)

,SUBSTRING(Diagnoses_For_Spell,6,4)

,SUBSTRING(Diagnoses_For_Spell,11,4)

,SUBSTRING(Diagnoses_For_Spell,16,4)

,SUBSTRING(Diagnoses_For_Spell,21,4)

,SUBSTRING(Diagnoses_For_Spell,26,4)

,SUBSTRING(Diagnoses_For_Spell,31,4)

,SUBSTRING(Diagnoses_For_Spell,36,4)

,SUBSTRING(Diagnoses_For_Spell,41,4)

,SUBSTRING(Diagnoses_For_Spell,46,4)

FROM #temptable

John

David Burrows SSC Guru Points: 65143 More actions · Answer 2

First you need to allocate a unique number to each row

Second use Jeff Moden's DelimitedSplit8K function (here) to split the data

Third use PIVOT or CASE statements to pivot the data into columns

*Edited* John's solution would be neater

Should think more before posting 🙁

Far away is close at hand in the images of elsewhere.
Anon.

Lowell SSC Guru Points: 323518 More actions · Answer 3

here's an example that i just slapped together that does the first nine columns. using DelimitedSplit8K and the PIVOT operator.

does this help?

/*

--Results

Diagnoses_For_Spell 1 2 3 4 5 6 7 8 9

E110,I10X,Z755,F03X,M819,Z755,F03X,M819,Z755 E110 I10X Z755 F03X M819 Z755 F03X M819 Z755

F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755 F03X M819 Z755 F03X M819 Z755 F03X M819 Z755

I64X,Z755,F03X,M819,Z755,F03X,M819,Z755 I64X Z755 F03X M819 Z755 F03X M819 Z755 NULL

*/

CREATE TABLE #temptable ( [Diagnoses_For_Spell] nvarchar(max) )

INSERT INTO #temptable

VALUES

( N'I64X,Z755,F03X,M819,Z755,F03X,M819,Z755' ),

( N'F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755' ),

( N'E110,I10X,Z755,F03X,M819,Z755,F03X,M819,Z755' )

WITH MyCTE

AS

(

select *

FROM #temptable T1

CROSS APPLY dbo.DelimitedSplit8K(T1.[Diagnoses_For_Spell],',') dl

)

select * from

( select * from MyCTE where ItemNumber <= 9 ) pivot_handle

pivot

(MAX([Item]) for ItemNumber in ([1],[2],[3],[4],[5],[6],[7],[8],[9])) pivot_table

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

I wouldn't want to write 99 columns, so I would use a dynamic approach for this.

DECLARE @sql nvarchar(max);

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

cteTally(n) AS(

SELECT TOP (99)

ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

FROM E a, E b

)

SELECT @sql = N'SELECT ' +

STUFF(( SELECT N',MAX( CASE WHEN ItemNumber = ' + CAST( n AS nvarchar(2)) + N' THEN Item END) AS Diag_' + CAST( n AS nvarchar(2)) + NCHAR(10)

FROM cteTally

ORDER BY n

FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 2, '') +

'FROM #tempTable t

CROSS APPLY dbo.DelimitedSplitN4K( t.[Diagnoses_For_Spell], '','')s

GROUP BY t.[Diagnoses_For_Spell];'

EXEC sp_executesql @sql;

References:

DelimitedSplitN4K: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Concatenation: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Luis Cazares (5/20/2016)
I wouldn't want to write 99 columns, so I would use a dynamic approach for this.
DECLARE @sql nvarchar(max);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
cteTally(n) AS(
SELECT TOP (99)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E a, E b
)
SELECT @sql = N'SELECT ' +
STUFF(( SELECT N',MAX( CASE WHEN ItemNumber = ' + CAST( n AS nvarchar(2)) + N' THEN Item END) AS Diag_' + CAST( n AS nvarchar(2)) + NCHAR(10)
FROM cteTally
ORDER BY n
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 2, '') +
'FROM #tempTable t
CROSS APPLY dbo.DelimitedSplitN4K( t.[Diagnoses_For_Spell], '','')s
GROUP BY t.[Diagnoses_For_Spell];'
EXEC sp_executesql @sql;
References:
DelimitedSplitN4K: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Concatenation: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

You have to love the dynamic crosstab. Nice work, Luis.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Ed Wagner (5/20/2016)
You have to love the dynamic crosstab. Nice work, Luis.

Thank you, Alan.

This has become simpler each time. It's even better now that I have templates for the tally table and the XML concatenation.

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Luis Cazares (5/20/2016)
Ed Wagner (5/20/2016)
You have to love the dynamic crosstab. Nice work, Luis.
Thank you, Alan.
This has become simpler each time. It's even better now that I have templates for the tally table and the XML concatenation.

I find that many things become easier the more I do them. I also find ways to make the code simpler as I do them more often, too.

BTW, I'm Ed, not Alan. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Ed Wagner (5/20/2016)
Luis Cazares (5/20/2016)
Ed Wagner (5/20/2016)
You have to love the dynamic crosstab. Nice work, Luis.
Thank you, Alan.
This has become simpler each time. It's even better now that I have templates for the tally table and the XML concatenation.
I find that many things become easier the more I do them. I also find ways to make the code simpler as I do them more often, too.
BTW, I'm Ed, not Alan. 😉

OMG, I saw Alan's name in another thread and got confused. I didn't have enough sleep last night. 😀

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

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 9

Many thanks for everyone's replies and suggestions. I'm about to leave work now for the weekend so i will have a look at your suggestions over the next couple of days but looking at the responses I think the solutions has been provided.

As always thanks for your help and time.:-D:-):-D

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

Luis Cazares (5/20/2016)
Ed Wagner (5/20/2016)
Luis Cazares (5/20/2016)
Ed Wagner (5/20/2016)
You have to love the dynamic crosstab. Nice work, Luis.
Thank you, Alan.
This has become simpler each time. It's even better now that I have templates for the tally table and the XML concatenation.
I find that many things become easier the more I do them. I also find ways to make the code simpler as I do them more often, too.
BTW, I'm Ed, not Alan. 😉
OMG, I saw Alan's name in another thread and got confused. I didn't have enough sleep last night. 😀

No worries, Luis. The sleep thing doesn't get any better.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

LoosinMaMind (5/20/2016)
I have the following in a table and need to transfer the single delimited string out into 99 Columns.
I'm afraid that the 99 Columns is a fixed template but i know from experience of the dataset that we will never reach 99 so I WILL need all 99 Column sin the output but know that they will not all be filled.
Please find my demo script below along with a sample if the expected output.
Thanks in advance as always for any help received.
CREATE TABLE #temptable ( [Diagnoses_For_Spell] nvarchar(max) )
INSERT INTO #temptable
VALUES
( N'I64X,Z755,F03X,M819,Z755,F03X,M819,Z755' ),
( N'F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755,F03X,M819,Z755' ),
( N'E110,I10X,Z755,F03X,M819,Z755,F03X,M819,Z755' )
SELECT * FROM #temptable
DROP TABLE #temptable
--RESULTS
Diag_1Diag_2Diag_3Diag_4Diag_5Diag_6Diag_7Diag_8Diag_9Diag_10Diag_11Diag_12Diag_13Diag_14Diag_15Diag_16….To 99
I64XZ755F03XM819Z755F03XM819Z755
F03XM819Z755F03XM819Z755F03XM819Z755F03XM819Z755
E110I10XZ755F03XM819Z755F03XM819Z755

Just curious and for purposes of performance, John Mitchell is absolutely on the right track... will all elements ALWAYS (except for errors) have exactly 4 characters?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 12

if these were medical diagnosis codes, i know diagnosis codes vary between three and eight characters in length;

here's a couple examples

463

W45.8XXA

so i think using a delimitedsplit is going to be required in that case, but i searched a few of the codes, and did not find them in my lookup table....might be qa different "diagnosis" than i was thinking.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sergiy SSC Guru Points: 110208 More actions · Answer 13

In your data sample you've got only 6 unique diagnosis codes.

Are you sure you need to repeat them all over and over again in that row you need to populate?

Is there any value in preserving the order of appearance of those codes in a row?

_____________
Code for TallyGenerator