Column to text row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Column to text row

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-14 : 04:27:36

hi

i have a column in a table and I want to turn all the values into a string with a comma and a space.
e.g.
Field1 Field2
10001 Value1
10002 Value2
10003 Value3

I want a string that says 'Value1, Value2, value3'

thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 04:31:36

see scenario 3 here

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-14 : 04:43:57

I dnt know what visakh has been posted because I am not able to open that link as its blocked here.

But i will go for it -

DECLARE @str AS VARCHAR(MAX)
SET @str = ''
SELECT @str = @str + ', ' + field2 FROM YourTable
SELECT SUBSTRING( @str, 2, LEN(@str) )

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 04:49:31

quote:
Originally posted by vaibhavktiwari83

I dnt know what visakh has been posted because I am not able to open that link as its blocked here.

But i will go for it -

DECLARE @str AS VARCHAR(MAX)
SET @str = ''
SELECT @str = @str + ', ' + field2 FROM YourTable
SELECT SUBSTRING( @str, 2, LEN(@str) )

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

or just use


SELECT STUFF((SELECT Field2 + ',' FROM Table FOR XML PATH('')),1,1,'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-14 : 04:52:43

quote:
Originally posted by visakh16

quote:
Originally posted by vaibhavktiwari83

I dnt know what visakh has been posted because I am not able to open that link as its blocked here.

But i will go for it -

DECLARE @str AS VARCHAR(MAX)
SET @str = ''
SELECT @str = @str + ', ' + field2 FROM YourTable
SELECT SUBSTRING( @str, 2, LEN(@str) )

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

or just use

SELECT STUFF((SELECT Field2 + ',' FROM Table FOR XML PATH('')),1,1,'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Little correction
SELECT STUFF((SELECT Field2 + ',' FROM tst FOR XML PATH('')),1,0,'')

and still comma is there at end of string.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 04:54:19

now ok?


SELECT STUFF((SELECT ',' + Field2  FROM Table FOR XML PATH('')),1,1,'')

copied from your suggestion, forgot to do change

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-14 : 07:05:26

quote:
Originally posted by visakh16

now ok?

SELECT STUFF((SELECT ',' + Field2 FROM Table FOR XML PATH('')),1,1,'')

copied from your suggestion, forgot to do change

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Its ok.
Actually I could not do that, thats why I asked you to do that.