Groups > comp.databases.ms-sqlserver > #340

Re: Get comma separated list of integers

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Get comma separated list of integers
Date	Tue, 17 May 2011 23:04:23 +0200
Organization	Erland Sommarskog
Lines	33
Message-ID	<Xns9EE8EAB62787AYazorman@127.0.0.1> (permalink)
References	<65c25398-5844-4c31-8579-79071c7b03f8@u15g2000vby.googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx04.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="10832"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+uDdp4wsEeMdhMQuu958tx"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:i6aY7yYFWQt+W1zeq4usUr+YNzw=
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:340

Show key headers only | View raw

BobRoyAce (broy@omegasoftwareinc.com) writes:
> The following query will return one row for each pkLoanID present in
> the queried table:
> 
> SELECT pkLoanID FROM Loans
> 
> So, if there were three records, I would get back three records.
> 
> What I want is a query that will instead return just a single row/
> value which would equal a comma-separated list of the pkLoanID values
> (e.g. 1,2,3).
> 
> How can I accomplish with with SQL?

It's a bit quirky and definitely not in the feature area were you would 
look:

SELECT substring(csv, 1, len(csv) - 1)
FROM   (SELECT ltrim(str(pkLoanID)) + ','
        FROM   Loans
        ORDER  BY pkLoandID
        FOR XML PATH('')) AS x(csv)

See  also http://www.projectdmx.com/tsql/rowconcatenate.aspx


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Get comma separated list of integers BobRoyAce <broy@omegasoftwareinc.com> - 2011-05-17 07:41 -0700
  Re: Get comma separated list of integers Gene Wirchenko <genew@ocis.net> - 2011-05-17 11:17 -0700
  Re: Get comma separated list of integers Erland Sommarskog <esquel@sommarskog.se> - 2011-05-17 23:04 +0200

csiph-web