Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: Get comma separated list of integers

Show all headers | 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