Awesome solution for a complicated problem---- CSV in a column

[Pramod Jamadagni]

Hi guys,

Some time we may have comma separated values in a single column as below

 

Please refer the below link to get this solved.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6206

 

Eg:

CREATE TABLE Quotes (Author varchar(100), Phrase varchar(7000))

Go

INSERT Quotes SELECT 'Shakespeare', 'A,rose,by,any,other,name,smells,just,as,sweet'

UNION SELECT 'Kipling', 'Across,the,valley,of,death,rode,the,six,hundred'

UNION SELECT 'Coleridge', 'In,Xanadu,did,Kubla,Kaan,...,,,,damn,I,forgot,the,rest,of,it'

UNION SELECT 'Descartes', 'I,think,therefore,I,am'

UNION SELECT 'Volk', 'I,think,therefore,I,need,another,beer'

UNION SELECT 'Feldman', 'No,it''s,pronounced,I,gor'

UNION SELECT 'Simpson', 'Mmmmmm,Govinda'

UNION SELECT 'Fudd', 'Be,vewwy,vewwy,quiet,I''m,hunting,wabbits'

Go

 

CREATE TABLE Tally (ID int identity(1,1))

 

Go

 

INSERT Tally DEFAULT VALUES

WHILE SCOPE_IDENTITY() < 8000 INSERT Tally DEFAULT VALUES

Go

--select * from Quotes

 

-- Include empty strings as nulls, for direct comparison

SELECT

Author,

Word = NullIf(SubString(Phrase, ID, CharIndex(',', Phrase + ',', ID) - ID), '')

FROM

Tally T

INNER JOIN Quotes Q ON T.ID <= Len(Q.Phrase) AND Substring(',' + Q.Phrase, T.ID, 1) = ','

 

--Don't include empty strings (changes in blue)

SELECT

Author,

Word = SubString(Phrase, ID, CharIndex(',', Phrase + ',', ID) - ID)

FROM

Tally T

INNER JOIN Quotes Q ON T.ID <= Len(Q.Phrase) AND Substring(',' + Q.Phrase, T.ID, 2) LIKE ',[^,]'

 

"This email and any files transmitted with it contain confidential, proprietary,
privileged information of Symphony Services Corp (India) Pvt. Ltd. and are intended
solely for the use of the recipient/s to whom it is addressed. Any unauthorized
notifying, copying or distributing of this e-mail, directly or indirectly, and the
contents therein in full or part is prohibited by any entity who is not a recipient.
Any email received inadvertently or by mistake should be deleted by the entity who
is not a recipient thereof. You may be pleased to notify the sender immediately by
email and the email should be deleted from your system".