1

I have a table 'City' which contains rows like these:

ID  CityName
1   Chennai
2   Bangalore
3   Delhi
4   Mumbai

I want to get a result like 'Chennai,Bangalore,Delhi,Mumbai' using a select query.

When I search on Google, the answers use the XMLPATH or a temporary variable. Is there any way to do this without using those?

Don't Panic
  • 39,820
  • 10
  • 58
  • 75
  • 1
    You haven't tagged an RDBMS. Use GROUP_CONCAT in MySql, ListAgg in Oracle, and unfortunately you'll need to resort to [a hack](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) in Sql Server, one of which is the `STUFF / FOR XML PATH ` one you seem to mention. – StuartLC Jan 21 '15 at 05:50

1 Answers1

0
CREATE TABLE [dbo].[Cities](
    [Id] [int] NULL,
    [CityName] [varchar](50) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Cities]
           ([Id],[CityName])
     VALUES
           (1,'New York'),
           (2,'Miami'),
           (3,'Orlando')
GO

DECLARE @listStr VARCHAR(MAX)

SELECT @listStr = COALESCE(@listStr+',' ,'') + CityName
FROM Cities

SELECT @listStr

Edit: Also read this post, it contains several approaches in which you can achieve what you want.

http://www.sqlmatters.com/Articles/Converting%20row%20values%20in%20a%20table%20to%20a%20single%20concatenated%20string.aspx

Y.S
  • 1,785
  • 3
  • 15
  • 27
  • Is there any possibility to avoid the @listStr variable, and get the value from a select query. –  Jan 21 '15 at 05:49
  • No. However, You can "wrap" it all in a stored procedure, if that helps you do what you need. – Y.S Jan 21 '15 at 06:00
  • yeah, the link you gave help me a lot. Tanks for your advice @Y.S –  Jan 21 '15 at 07:18