blocks|key|331069|text|SQL+Server+2017中新增了一种方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|331070|SELECT+STRING_AGG+(column,+',')+AS+column+FROM+Table;|offset|length|style|CODE|331071|这将为您生成1,3,5,9|331072|entityMap^0|0|0|1H|0|6|7|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|K|$]]

There is new method in SQL Server 2017:

<code>SELECT STRING_AGG (column, ',') AS column FROM Table;</code>

that will produce <code>1,3,5,9</code> for you

blocks|key|330948|text|select++stuff(list,1,1,'')
from++++(
++++++++select++','+%2B+cast(col1+as+varchar(16))+as+[text()]
++++++++from++++YourTable
++++++++for+++++xml+path('')
++++++++)+as+Sub(list)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|330949|Example+at+SQL+Fiddle.|unstyled|offset|length|330950|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!6/0e28a/1/0^0|0|0|M|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@]|9|@$G|S|H|T|1|U]]|A|$]]|$1|I|3|-4|5|F|7|V|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P]]]]

<pre><code>select stuff(list,1,1,'')
from (
 select ',' + cast(col1 as varchar(16)) as [text()]
 from YourTable
 for xml path('')
 ) as Sub(list)
</code></pre>

<a href="http://sqlfiddle.com/#!6/0e28a/1/0" rel="noreferrer">Example at SQL Fiddle.</a>

blocks|key|331045|text|SELECT++CAST(<COLUMN+Name>+AS+VARCHAR(3))+%2B+','
FROM++++<TABLE+Name>
FOR+++++XML+PATH('')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|331046|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT CAST(&lt;COLUMN Name&gt; AS VARCHAR(3)) + ','
FROM &lt;TABLE Name&gt;
FOR XML PATH('')
</code></pre>

blocks|key|331033|text|这是一个尝试创建一个可重用的列来逗号分隔的字符串。在这种情况下，我只需要一个有值的字符串，我不想要空字符串或nulls。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|331034|首先，我创建了一个用户定义的类型，它是一个单列表。|331035|--+================================
--+Create+User-defined+Table+Type
--+================================
USE+[RSINET.MVC]
GO

--+Create+the+data+type
CREATE+TYPE+[dbo].[SingleVarcharColumn]+AS+TABLE+
(
++++data+NVARCHAR(max)
)
GO|code-block|syntax|javascript|331036|该类型的真正用途是简化创建一个标量函数，将列放入逗号分隔值。|331037|--+================================================
--+Template+generated+from+Template+Explorer+using:
--+Create+Scalar+Function+(New+Menu).SQL
--
--+Use+the+Specify+Values+for+Template+Parameters+
--+command+(Ctrl-Shift-M)+to+fill+in+the+parameter+
--+values+below.
--
--+This+block+of+comments+will+not+be+included+in
--+the+definition+of+the+function.
--+================================================
SET+ANSI_NULLS+ON
GO
SET+QUOTED_IDENTIFIER+ON
GO
--+=============================================
--+Author:++Rob+Peterson++++++++
--+Create+date:+8-26-2015
--+Description:+This+will+take+a+single+varchar+column+and+convert+it+to
--+comma+separated+values.
--+=============================================
CREATE+FUNCTION+fnGetCommaSeparatedString+
(
++++--+Add+the+parameters+for+the+function+here
++++@column+AS+[dbo].[SingleVarcharColumn]+READONLY
)
RETURNS+VARCHAR(max)
AS
BEGIN
--+Declare+the+return+variable+here
DECLARE+@result+VARCHAR(MAX)
DECLARE+@current+VARCHAR(MAX)
DECLARE+@counter+INT
DECLARE+@c+CURSOR

SET+@result+=+''
SET+@counter+=+0
--+Add+the+T-SQL+statements+to+compute+the+return+value+here
SET+@c+=+CURSOR+FAST_FORWARD
++++FOR+SELECT+COALESCE(data,'')+FROM+@column
++++OPEN+@c
++++FETCH+NEXT+FROM+@c
++++INTO+@current
++++WHILE+@@FETCH_STATUS+=+0
++++BEGIN
++++++++IF+@result+<>+''+AND+@current+<>+''+SET+@result+=+@result+%2B+','+%2B+@current
++++++++IF+@result+=+''+AND+@current+<>+''+SET+@result+=+@current
++++FETCH+NEXT+FROM+@c
++++INTO+@current
++++END
++++CLOSE+@c
++++DEALLOCATE+@c
--+Return+the+result+of+the+function
RETURN+@result

END
GO|331038|现在，为了使用这个。我选择要将逗号分隔的字符串转换为SingleVarcharColumn类型的列。|331039|DECLARE+@s+as+SingleVarcharColumn

INSERT+INTO+@s+VALUES+('rob')
INSERT+INTO+@s+VALUES+('paul')
INSERT+INTO+@s+VALUES+('james')
INSERT+INTO+@s+VALUES+(null)


INSERT+INTO+@s
SELECT+iClientID+FROM+[dbo].tClient

SELECT+[dbo].fnGetCommaSeparatedString(@s)|331040|才能得到这样的结果。|331041|罗伯，保罗，james,1,9,10,11,12,13,14,15,16,18,19,23,26,27,28,29,30,31,32,34,35,36,37,38,39,40,41,42,44,45,46,47,48,49,50,52,53,54,56,57,59,60,61,62,63,64,65,66,67,68,69,70,71,72,74,75,76,77,78,81,82,83,84,87,88,90,91,92,93,94,98,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,120,121,122,123,124,125,126,127,128,129,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159|331042|我将SingleVarcharColumn类型中的数据列设置为NVARCHAR(MAX)，这可能会影响性能，但我想要的是灵活性，而且它的运行速度足以满足我的要求。如果它是一个varchar，如果它有一个固定的更小的宽度，它可能会更快，但我还没有测试过它。|331043|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|11|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

This a stab at creating a reusable column to comma separated string. In this case, I only one strings that have values and I do not want empty strings or nulls.

First I create a user defined type that is a one column table.

<pre><code>-- ================================
-- Create User-defined Table Type
-- ================================
USE [RSINET.MVC]
GO

-- Create the data type
CREATE TYPE [dbo].[SingleVarcharColumn] AS TABLE 
(
 data NVARCHAR(max)
)
GO
</code></pre>

The real purpose of the type is to simplify creating a scalar function to put the column into comma separated values.

<pre><code>-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Rob Peterson 
-- Create date: 8-26-2015
-- Description: This will take a single varchar column and convert it to
-- comma separated values.
-- =============================================
CREATE FUNCTION fnGetCommaSeparatedString 
(
 -- Add the parameters for the function here
 @column AS [dbo].[SingleVarcharColumn] READONLY
)
RETURNS VARCHAR(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @result VARCHAR(MAX)
DECLARE @current VARCHAR(MAX)
DECLARE @counter INT
DECLARE @c CURSOR

SET @result = ''
SET @counter = 0
-- Add the T-SQL statements to compute the return value here
SET @c = CURSOR FAST_FORWARD
 FOR SELECT COALESCE(data,'') FROM @column
 OPEN @c
 FETCH NEXT FROM @c
 INTO @current
 WHILE @@FETCH_STATUS = 0
 BEGIN
 IF @result &lt;&gt; '' AND @current &lt;&gt; '' SET @result = @result + ',' + @current
 IF @result = '' AND @current &lt;&gt; '' SET @result = @current
 FETCH NEXT FROM @c
 INTO @current
 END
 CLOSE @c
 DEALLOCATE @c
-- Return the result of the function
RETURN @result

END
GO
</code></pre>

Now, to use this. I select the column I want to convert to a comma separated string into the SingleVarcharColumn Type.

<pre><code>DECLARE @s as SingleVarcharColumn

INSERT INTO @s VALUES ('rob')
INSERT INTO @s VALUES ('paul')
INSERT INTO @s VALUES ('james')
INSERT INTO @s VALUES (null)


INSERT INTO @s
SELECT iClientID FROM [dbo].tClient

SELECT [dbo].fnGetCommaSeparatedString(@s)
</code></pre>

To get results like this.

rob,paul,james,1,9,10,11,12,13,14,15,16,18,19,23,26,27,28,29,30,31,32,34,35,36,37,38,39,40,41,42,44,45,46,47,48,49,50,52,53,54,56,57,59,60,61,62,63,64,65,66,67,68,69,70,71,72,74,75,76,77,78,81,82,83,84,87,88,90,91,92,93,94,98,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,120,121,122,123,124,125,126,127,128,129,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159

I made my data column in my SingleVarcharColumn type an NVARCHAR(MAX) which may hurt performance, but I flexibility was what I was looking for and it runs fast enough for my purposes. It would probably be faster if it were a varchar and if it had a fixed and smaller width, but I have not tested it.

blocks|key|113507|text|ALTER+PROCEDURE+[dbo].[spConvertir_CampoACadena](+@nomb_tabla+++varchar(30),
++++++++++++++++++++++++++@campo_tabla++varchar(30),
++++++++++++++++++++++++++@delimitador++varchar(5),
++++++++++++++++++++++++++@respuesta++++varchar(max)+OUTPUT
)
AS
DECLARE+@query++++++varchar(1000),
++++@cadena+++++varchar(500)
BEGIN
++SET+@query+=+'SELECT+@cadena++=+COALESCE(@cadena+%2B+'''%2B+@delimitador+%2B''',+'%2B+''''''+%2B')+%2B+'%2B+@campo_tabla+%2B+'+FROM+'%2B@nomb_tabla
++--select+@query
++EXEC(@query)
++SET+@respuesta+=+@cadena++
END|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|113508|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>ALTER PROCEDURE [dbo].[spConvertir_CampoACadena]( @nomb_tabla varchar(30),
 @campo_tabla varchar(30),
 @delimitador varchar(5),
 @respuesta varchar(max) OUTPUT
)
AS
DECLARE @query varchar(1000),
 @cadena varchar(500)
BEGIN
 SET @query = 'SELECT @cadena = COALESCE(@cadena + '''+ @delimitador +''', '+ '''''' +') + '+ @campo_tabla + ' FROM '+@nomb_tabla
 --select @query
 EXEC(@query)
 SET @respuesta = @cadena 
END
</code></pre>

blocks|key|331094|text|使用最简单的方法来完成此操作-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|331095|SELECT+GROUP_CONCAT(Column)+from+table|code-block|syntax|javascript|331096|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use simplest way of doing this-

<pre><code>SELECT GROUP_CONCAT(Column) from table
</code></pre>

blocks|key|331110|text|%2B------%2B----------------------%2B
%7C+type+%7C++++++++names+++++++++%7C
%2B------%2B----------------------%2B
%7C+cat++%7C+Felon++++++++++++++++%7C
%7C+cat++%7C+Purz+++++++++++++++++%7C
%7C+dog++%7C+Fido+++++++++++++++++%7C
%7C+dog++%7C+Beethoven++++++++++++%7C
%7C+dog++%7C+Buddy++++++++++++++++%7C
%7C+bird+%7C+Tweety+++++++++++++++%7C
%2B------%2B----------------------%2B|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|331111|unstyled|331112|select+group_concat(name)+from+Pets
group+by+type|331113|在这里，您可以轻松地在单个SQL中获得答案，并且通过在SQL中使用group+by，您可以根据该列值来分隔结果。您还可以使用自己的自定义分隔符来拆分值|331114|结果：|331115|%2B------%2B----------------------%2B
%7C+type+%7C++++++++names+++++++++%7C
%2B------%2B----------------------%2B
%7C+cat++%7C+Felon,Purz+++++++++++%7C
%7C+dog++%7C+Fido,Beethoven,Buddy+%7C
%7C+bird+%7C+Tweety+++++++++++++++%7C
%2B------%2B----------------------%2B|331116|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|R|8|@]|9|@]|A|$B|C]]|$1|H|3|I|5|E|7|S|8|@]|9|@]|A|$]]|$1|J|3|K|5|E|7|T|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|U|8|@]|9|@]|A|$B|C]]|$1|N|3|-4|5|E|7|V|8|@]|9|@]|A|$]]]|O|$]]

Is it possible to write a statement that selects a column from a table and converts the results to a string?

Ideally I would want to have comma separated values.

For example, say that the SELECT statement looks something like

<pre><code>SELECT column
FROM table
WHERE column&lt;10
</code></pre>

and the result is a column with values

<pre><code>|column|
--------
| 1 |
| 3 |
| 5 |
| 9 |
</code></pre>

I want as a result the string "1, 3, 5, 9"

SQL Server convert select a column and convert it to a string

SQL Server

是否可以编写一条语句，从表中选择一列并将结果转换为字符串？理想情况下，我希望使用逗号分隔值。例如，假设SELECT语句如下所示SELECT columnFROM tableWHERE column<10并且结果是一个包含值的列|column|--------| 1 || 3 || 5 || 9 |我希望字符串"1，3，5，9“

问SQL Server convert选择列并将其转换为字符串
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server convert选择列并将其转换为字符串EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server convert选择列并将其转换为字符串
EN