Query to display the following output

Question

Post reply

Query to display the following output

cadburypn46

Grasshopper

Points: 24
More actions
July 22, 2011 at 3:50 am

#244782

My data is 'aaa','bbb','ccc',........
how would i display my data into separate rows using temp data..like
aaa
bbb
ccc

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

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

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 1

DECLARE @string VARCHAR(1000)

SET @string = 'aaa,bbb,ccc'

DECLARE @tblNumber TABLE

(

ID INT

)

INSERT INTO @tblNumber

SELECT TOP 100 ROW_number() OVER(order by s.object_id)

from sys.objects o,sys.objects s

select SUBSTRING(@string,id,CHARINDEX(',',@string+',',id)-id) from @tblNumber

where ID <len(@string)

AND SUBSTRING(','+@string,id,1)=','

Regards,
Mitesh OSwal
+918698619998

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 2

What you are asking for is split function

You can find samples on the web if you search with that term

Here is some

http://www.kodyaz.com/articles/sql-server-string-split-tsql-clr-function-sample.aspx

http://www.kodyaz.com/articles/sql-server-t-sql-split-function.aspx

http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

Eralper (7/22/2011)
What you are asking for is split function
You can find samples on the web if you search with that term
Here is some
http://www.kodyaz.com/articles/sql-server-string-split-tsql-clr-function-sample.aspx
http://www.kodyaz.com/articles/sql-server-t-sql-split-function.aspx
http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx

Oh... be careful. Splitting using a recursive CTE or XML can be mighty slow compared to other methods. If the split is being done on something that will fit into VARCHAR(8000) or NVARCHAR(4000), please consider the methods in the following article.

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

If you need to split one of the MAX datatypes, consider using the CLR which is also included in the "resources" near the bottom of that article.

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

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 4

Mitesh Oswal (7/22/2011)
DECLARE @string VARCHAR(1000) SET @string = 'aaa,bbb,ccc' DECLARE @tblNumber TABLE ( ID INT ) INSERT INTO @tblNumber SELECT TOP 100 ROW_number() OVER(order by s.object_id) from sys.objects o,sys.objects s select SUBSTRING(@string,id,CHARINDEX(',',@string+',',id)-id) from @tblNumber where ID <len(@string) AND SUBSTRING(','+@string,id,1)=','

That's the way a lot of good folks so a split. In fact, so did I. But it has a real performance problem as the number of elements increases or the width of the elements increases. The problem is with the concatenation of delimiters.

A new and much more performant method has been discovered and, yes, it's also based on a Numbers/Tally Table or an Itzik-style numbers-CTE.

Here's the link to the new methods.