SQL Server转换选择一个列并将其转换为字符串。

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

例如，假设SELECT语句看起来像这样

SELECT column
FROM table
WHERE column<10

and the result is a column with values

结果是一个有值的列

|column|
--------
|  1   |
|  3   |
|  5   |
|  9   |

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

我想要的结果是字符串1 3 5 9

6 个解决方案

#1

You can do it like this:

你可以这样做:

Fiddle demo

小提琴演示

declare @results varchar(500)

select @results = coalesce(@results + ',', '') +  convert(varchar(12),col)
from t
order by col

select @results as results

| RESULTS |
-----------
| 1,3,5,9 |

#2

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

Example at SQL Fiddle.

示例SQL小提琴。

#3

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.

这是创建可重用列到逗号分隔字符串的一种尝试。在这种情况下，我只有一个有值的字符串，而我不想要空字符串或null。

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

首先，我创建一个用户定义的类型，它是一个单列表。

-- ================================
-- Create User-defined Table Type
-- ================================
USE [RSINET.MVC]
GO

-- Create the data type
CREATE TYPE [dbo].[SingleVarcharColumn] AS TABLE 
(
    data NVARCHAR(max)
)
GO

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

该类型的真正目的是简化创建标量函数，将列放入逗号分隔的值中。

-- ================================================
-- 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 + ',' + @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

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

现在,使用这个。我选择要转换为逗号分隔的字符串的列，将其转换为SingleVarcharColumn类型。

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)

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

抢劫,保罗,詹姆斯,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,94

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.

我使用SingleVarcharColumn类型a NVARCHAR(MAX)创建数据列，这可能会影响性能，但我需要的是灵活性，它运行得足够快。如果它是一个varchar，如果它有一个固定的和更小的宽度，它可能会更快，但是我还没有测试它。

#4

SELECT  CAST(<COLUMN Name> AS VARCHAR(3)) + ','
FROM    <TABLE Name>
FOR     XML PATH('')

#5

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

#6

You can use the following method:

您可以使用以下方法:

select
STUFF(
        (
        select ', ' + CONVERT(varchar(10), ID) FROM @temp
        where ID<50
group by ID for xml path('')
        ), 1, 2, '') as IDs

---

Implementation:

Declare @temp Table(
ID int
)
insert into @temp
(ID)
values
(1)
insert into @temp
(ID)
values
(3)
insert into @temp
(ID)
values
(5)
insert into @temp
(ID)
values
(9)

 select
STUFF(
        (
        select ', ' + CONVERT(varchar(10), ID) FROM @temp
        where ID<50
group by ID for xml path('')
        ), 1, 2, '') as IDs

Result will be: