Raghunath Bhandari's Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

SQL server	7
Database	6
Tips	5
Tricks	5
#SQLServer	3
Backup	2
MSSQL	1
'E0'	1
isnumeric	1
Issues	1

Archive · View All

December 2011	4
March 2012	2
January 2012	1

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (47) · Preferences

Table Row data in String

Dec 21 2011 10:08PM by Raghunath Bhandari

We can use this script to show the row data in string.



Name

------

RAM

SHYAM

HARI
RITA



Name

RAM,SHYAM,HARI,RITA





--Use the following query

DECLARE @strList varchar(100)

SELECT @strList = COALESCE(@strList + ', ', '') +
      CAST(Name AS varchar(5))

FROM TABLE1



SELECT @strList

Tags: Database, SQL server, Tricks, Tips, Table Row in String, #SQLServer,

Previous: MSSQL transaction log
Next: Dynamic PIVOT Query For Cross Tab Result

Raghunath Bhandari

183 · 1% · 253

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

2 Comments

You can do the same with this script as well:

SELECT ISNULL(STUFF((SELECT ',' +  Name FROM Table1 FOR XML PATH('')), 1, 1, ''), '')

commented on Jan 12 2012 11:09PM

Olga Medvedeva

66 · 3% · 843

This can also be represented as:

DECLARE @strList varchar(max)= ''

SELECT @strList += COALESCE(CAST(Name as varchar(5) + ', ', '') FROM TABLE1

SELECT LEFT(@strList, LEN(@strList) - 1)

A few problems with your approach: If the rows add up to more than 100 characters, you'll get either truncation or an overflow error. If any of them contain NULL, the resulting string will be NULL. In your example, there would also be a trailing comma.

commented on Jan 15 2012 11:33AM

Raghunath Bhandari's Blog

Table Row data in String

2 Comments

Your Comment