splitting list into table

Question

Viewing 5 posts - 1 through 4 (of 4 total)

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

Ref the jeff article The "Numbers" or "Tally" Table: What it is and how it replaces a loop

From this,

CREATE TABLE #temp

(ID INT, ITEM1 VARCHAR(100))

GO

INSERT #temp

SELECT 1,'123,126,139,203'

UNION all

SELECT 2,'126,139,203'

UNION all

SELECT 3,'303'

/*create the TALLY table*/

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N INTO Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

ALTER TABLE Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

SELECT b.ID, SUBSTRING(','+b.ITEM1+',',N+1,CHARINDEX(',',','+b.ITEM1+',',N+1)-N-1) AS Value

FROM Tally a

CROSS JOIN #temp b

WHERE N < LEN(','+b.ITEM1+',')

AND SUBSTRING(','+b.ITEM1+',',N,1) = ','

Soft Developer SSC Journeyman Points: 83 More actions · Answer 2

Thanks Arun,

I have tested with test data. It is working. I will try with real data. Thanks alot for your solution.

arun.sas SSChampion Points: 11831 More actions · Answer 3

Hi,

The solution based on the jeff article mentioned above, with your real data you should mine with this article to get more.

nigel. SSChampion Points: 11636 More actions · Answer 4

A variation on arun's solution above with fewer string concatenations:

SELECT

ID, substring(item1,N,charindex(',',item1+',',N)-N)

FROM

tally

CROSS JOIN

#temp

WHERE

N <= LEN(item1)+1 AND

(N = 1 OR SUBSTRING(item1,N-1,1)=',')