在sql中使多个列成为一个列

I have a table Employee which have several fields like FirstName,LastName,Email,....... . So what i want to do is that in my case selection of column is dynamic

我有一个表员工，它有几个字段，比如FirstName,LastName,Email，……等等。我要做的是在我的例子中选择列是动态的

Declare @columnNeeded nvarchar(max)

Example one

一个例子

Set @columnNeeded = 'FirstName,Email'
Select @columnNeeded from Employee

Example Two

两个例子

Set @columnNeeded = 'FirstName,LastName'
Select @columnNeeded from Employee

This is pretty simple , now what i want is that regardless of what column will be in result set i need all column selected in one column as comma seperated string . I saw Group_Concat() in mysql but don't know how to do this in sql .So is this possible ?

这很简单，现在我想要的是，无论结果集中是哪一列，我都需要在一列中选择所有列作为逗号分隔的字符串。我在mysql中看到Group_Concat()，但不知道如何在sql中执行，这可能吗?

2 个解决方案

#1

You can do this with dynamic SQL:

您可以使用动态SQL:

declare @sql nvarchar(max) = 'select @columns from Employee';
declare @columnNeeded nvarchar(max) = 'FirstName,Email';

set @sql = replace(@sql, '@columns', @columnNeeded);

exec sp_executesql @sql;

EDIT:

编辑:

If you want them as one column, you could do:

如果你想把它们作为一栏，你可以这样做:

declare @sql nvarchar(max) = 'select @columns from Employee';
declare @columnNeeded nvarchar(max) = 'FirstName,Email';

set @sql = replace(replace(@sql, '@columns', @columnNeeded), ',', '+'',''+');

exec sp_executesql @sql;

To type-safe you would cast the column values:

要使用类型安全，您需要将列值转换为:

declare @tmp nvarchar(4000) = 'cast(' +
                              replace(@columnNeeded, ',', ', nvarchar(4000)), cast(') +
                              ', nvarchar(4000))'
set @sql = replace(replace(@sql, '@columns', @columnNeeded), ',', '+'',''+');

If this works as expected, it adds cast(<col> as nvarchar(4000)) to each of the columns in the list.

如果按照预期工作，它将cast( as nvarchar(4000))添加到列表中的每个列中。

#2

You have to use Dynamic SQL. Since you have different Data types in your table you may have to convert the columns to Varchar to concatenate the result into single column.

您必须使用动态SQL。由于表中有不同的数据类型，您可能需要将列转换为Varchar，以便将结果连接到单个列。

DECLARE @sql  NVARCHAR(max),
        @cols NVARCHAR(max) ='FirstName,Email'

SELECT @cols = 'convert(varchar(100),'
           + Replace(@cols+')+', ',', ')+'',''+convert(varchar(100),')

SELECT @cols = LEFT(@cols, Len(@cols) - 1)

SET @sql ='select ' + @cols + ' from Employee '

--print @sql
EXEC Sp_executesql @sql;

Working Example :

工作的例子:

CREATE TABLE #test1([Key] INT,ID    INT,Value VARCHAR(2))

INSERT #test1
VALUES (1,1,'C' ),(2,1,'C' ),(3,1,'I' )

DECLARE @sql  NVARCHAR(max),
        @cols NVARCHAR(max) ='ID,Value'

SELECT @cols = 'convert(varchar(100),'
           + Replace(@cols+')+', ',', ')+'',''+convert(varchar(100),')

SELECT @cols = LEFT(@cols, Len(@cols) - 1)

SET @sql ='select ' + @cols + ' from #test1 '

EXEC Sp_executesql @sql;

#1