splitting out a column

Question

splitting out a column

Christopher Klein

SSCrazy

Points: 2648
More actions
May 1, 2008 at 7:29 am

#380294

hi there,
I have 3 columns of data in a @table that I need to manipulate that I can't figure out how:
Mut1
AZ5397UA9,AZ539CYK2,AZ54022Z3,AZ8040BQ1,AZ0442BB0,AZ2404AW1,AZ307TAD0,AZ442FAE8,AZ442FAQ1,AS490FAV1
Credit1
I know there are split functions that I can take the 2nd column to break it out but I need the result to come out as
Mut1,AZ5397UA9,Credit1
Mut1,AZ539CYK2,Credit1
Mut1,AZ54022Z3,Credit1
Mut1,AZ8040BQ1,Credit1
Mut1,AZ0442BB0,Credit1
Mut1,AZ2404AW1,Credit1
Mut1,AZ307TAD0,Credit1
Mut1,AZ442FAE8,Credit1
Mut1,AZ442FAQ1,Credit1
Mut1,AS490FAV1,Credit1
I'm hoping to be able to do this without something like a cursor 🙁
thanks,
Chris

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

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

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

SUBSTRING and PATINDEX/CHARINDEX can split things out, but you need to have some sort of pattern.

Also, if there are a variable number of items in the 2nd column, there's no good automatic way to do this. The SQL gets ugly.

Does this happen often or is it a one time thing?

Christopher Klein SSCrazy Points: 2648 More actions · Answer 2

the 2nd column can have any number of items in it, including just 1.

I hate when the seemingly simple tasks turn into muck.

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Try something like this...

declare @t table (a varchar(10), b varchar(200), c varchar(10))

insert @t

select 'Mut1', 'AZ5397UA9,AZ539CYK2,AZ54022Z3,AZ8040BQ1,AZ0442BB0,AZ2404AW1,AZ307TAD0,AZ442FAE8,AZ442FAQ1,AS490FAV1', 'Credit1'

union all select 'Mut1', 'B5397UB9,B539CYK2,B540223,B8040BQ1,B0442BB0,B2404BW1,B307TBD0,B442FBE8,B442FBQ1,BS490FBV1', 'Credit2'

union all select 'Mut2', 'CV5397UC9,CV539CYK2,CV54022V3,CV8040CQ1,CV0442CC0,CV2404CW1,CV307TCD0,CV442FCE8,CV442FCQ1', 'Credit1'

union all select 'Mut3', 'DT5397UC9', 'Credit1'

select a, c, left(d, charindex(',', d + ',')-1) as e from (

select *, substring(b, number, 200) as d from @t a inner join

(select distinct number from master.dbo.spt_values where number between 1 and 200) b

on substring(',' + b, number, 1) = ',') t

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

It took me awhile to find it, but I knew I had read a solution to this problem using a Numbers table. Check out this article: http://www.sqlservercentral.com/articles/Advanced+Querying/2547/ and the function provided. It should do the trick or at least get you going in the right direction. Here is what I did with your example with it:

[font="Courier New"]DECLARE @table TABLE (col1 VARCHAR(25), col2 VARCHAR(MAX), col3 VARCHAR(25))

INSERT INTO @table

SELECT

'Mut1',

'AZ5397UA9,AZ539CYK2,AZ54022Z3,AZ8040BQ1,AZ0442BB0,AZ2404AW1,AZ307TAD0,AZ442FAE8,AZ442FAQ1,AS490FAV1',

'Credit1'

SELECT

col1,

string,

col3

FROM

@table T CROSS Apply

dbo.fnSetSplit (col2) AS N[/font]

And it returned the results you are looking for.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Christopher Klein SSCrazy Points: 2648 More actions · Answer 5

I like your quote 🙂

thanks, worked! Need to add that snippet to my collection of very useful snippets.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Sorry I used cross apply which will not work in SQL 7, 2000, I did not check the forum we were in. The numbers table solution can still work for you though. You just need to apply it differently.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

Here's a demo of what you can do in cases like this in both SQL Server 2000 and 2005... test data included...

--drop table jbmtest

--===== Create and populate a 1000 row test table.

-- Column "RowNum" has a range of 1 to 1000 unique numbers

-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'

-- for all rows.

-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

-- Jeff Moden

SELECT TOP 1000

RowNum = IDENTITY(INT,1,1),

SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),

SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),

SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

SomeHex12 = RIGHT(NEWID(),12)

INTO dbo.JBMTest

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned

ALTER TABLE dbo.JBMTest

ADD CONSTRAINT PK_JBMTest_RowNum PRIMARY KEY CLUSTERED (RowNum)

--===== Do the split

SELECT RowNum,

SomeInt,

Val = SUBSTRING(','+h.SomeCsv+',', t.N+1, CHARINDEX(',', ','+h.SomeCsv+',', t.N+1)-t.N-1)

FROM dbo.Tally t

RIGHT OUTER JOIN --Necessary in case SomeCsv is NULL

dbo.jbmTest h

ON SUBSTRING(','+h.SomeCsv+',', t.N, 1) = ','

AND t.N < LEN(','+h.SomeCsv+',')

Code to build the Tally table can be found at the following URL... you really need to keep a permanent copy of this useful little table in your databases... replaces the need for loops in a lot of places...

http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/

--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)

RyanRandall SSChampion Points: 13623 More actions · Answer 8

Jeff Moden (5/1/2008)
... INTO dbo.JBMTest

I like the idea of a generic multi-purpose test table 🙂

I will be steeling that idea 😀

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

Heh... no problems there, Ryan... it'll generate a million rows very quickly for performance testing, as well.

--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)

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

Or this one, which uses less string manipulation, but does contain dynamic SQL:

--Data

declare @table table(col1 varchar(5), col2 varchar(200), col3 varchar(10))

declare @sql varchar(1000)

INSERT INTO @table VALUES ('Mut1',

'AZ5397UA9,AZ539CYK2,AZ54022Z3,AZ8040BQ1,AZ0442BB0,AZ2404AW1,AZ307TAD0,AZ442FAE8,AZ442FAQ1,AS490FAV1',

'Credit1')

INSERT INTO @table VALUES ('Mut2',

'AZ5397UA9,AZ539CYK2,AZ54022Z3,AZ8040BQ1,AZ0442BB0,AZ2404AW1,AZ307TAD0,AZ442FAE8,AZ442FAQ1,AS490FAV1',

'Credit2')

SET @sql = ''

SELECT @sql = @sql + ' INSERT INTO ##Temp (col2,col1,col3) SELECT '''

+ REPLACE(col2, ',', ''',''' + col1 +''', ''' + col3 + ''' UNION ALL SELECT ''')

+ ''', ''' + col1 + ''', ''' + col3 + ''' '

FROM @table

--PRINT @sql

-- has to be global temp table to be visible to the dynamic SQL

CREATE TABLE ##Temp(col1 varchar(5), col2 varchar(20), col3 varchar(10))

EXEC (@SQL)

SELECT * FROM ##Temp

DROP TABLE ##Temp

John

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

John Mitchell (5/1/2008)
Or this one, which uses less string manipulation, but does contain dynamic SQL:

Nicley done but, be careful... get more than 8000 characters (about 16 rows in the example given) and BOOM!

--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)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

John Mitchell (5/1/2008)
Or this one, which uses less string manipulation, but does contain dynamic SQL:
...
-- has to be global temp table to be visible to the dynamic SQL
CREATE TABLE ##Temp(col1 varchar(5), col2 varchar(20), col3 varchar(10))
EXEC (@SQL)
SELECT * FROM ##Temp
DROP TABLE ##Temp

Doesn't have to be a global temp table to work. If you replace ##Temp with #Temp, the code works just fine.

The #Temp table will be visible to the dynamic SQL. You just can't create the #Temp table in the dynamic SQL and have it visible outside the dynamic SQL.