如何将单列拆分为多列

I have a SQL Server table table_name like:

我有一个SQL Server表table_name，如：

col1            col2
SomeString_1    23
SomeString_1    65
SomeString_1    300
SomeString_1    323

What I want to do is for one unique value in col1, I want to select all the values from col2 but each in it's own column.

我想要做的是col1中的一个唯一值，我想从col2中选择所有值，但每个值都在它自己的列中。

So the query should be something like:

所以查询应该是这样的：

select col2 from table_name where col1 = 'SomeString_1';

But I need output in the form:

但我需要以下列形式输出：

23  65  300 323

Basically each selected value should be in it's own column. So the result should always have one row and as many columns as the SomeString_1 is repeated.

基本上每个选定的值都应该在它自己的列中。因此，结果应始终有一行，并且重复SomeString_1的列数。

I tried to search on SO but few questions I found had different conditions.

我尝试搜索SO但很少有问题我发现有不同的条件。

3 个解决方案

#1

Seems like OP is asking for separate column value for each row value:

似乎OP要求为每个行值分别列值：

create table #Table1 (COL1 VARCHAR(100), COL2 VARCHAR(10))
INSERT INTO #Table1 VALUES ('SomeString_1', '23'), 
                          ('SomeString_1', '65'), 
                          ('SomeString_1', '300'),
                          ('SomeString_1', '323')

DECLARE @columns nvarchar(MAX) = STUFF((
SELECT DISTINCT ',[col-' + cast(row_number() over (order by (select 1)) as varchar(4))+']'
FROM #Table1
FOR XML PATH('')), 1, 1, '')

DECLARE @sql nvarchar(MAX) = N'
SELECT * FROM
(
    SELECT col2, ''col-'' + cast(row_number() over (order by (select 1)) as varchar(4)) as dupcol2
    FROM #Table1 where col1 = ''SomeString_1''
) T
PIVOT
(MAX(col2) FOR dupcol2 IN ('+@columns+')) P'

EXEC (@sql)

Output:

输出：

col-1 | col-2 | col-3 | col-4
------------------------------
23    | 65    | 300   | 323

#2

An approach can be applied by using FOR XML:

可以使用FOR XML应用一种方法：

SELECT DISTINCT t.COL1, 
        (SELECT t1.COL2 + ' ' AS 'data()'
         FROM @TBL t1 
         WHERE t.COL1 = t1.COL1
         FOR XML PATH(''))
FROM @TBL t
WHERE t.COL1 = 'SomeString_1'

An example,

一个例子，

DECLARE @TBL TABLE (COL1 VARCHAR(100), COL2 VARCHAR(10))
INSERT INTO @TBL VALUES ('SomeString_1', '23'), 
                        ('SomeString_1', '65'), 
                        ('SomeString_1', '300'),
                        ('SomeString_1', '323')

SELECT DISTINCT t.COL1, 
        (SELECT t1.COL2 + ' ' AS 'data()'
         FROM @TBL t1 
         WHERE t.COL1 = t1.COL1
         FOR XML PATH(''))
FROM @TBL t
WHERE t.COL1 = 'SomeString_1'

which returns,

返回，

COL1            (No column name)
SomeString_1    23  65  300  323

#3

You can use COALESCE also..

你也可以使用COALESCE ..

DECLARE @Names VARCHAR(8000) 

SELECT @Names = COALESCE(@Names + ' ', '') + COL2
FROM YourTable
WHERE COL1 = 'SomeString_1'

SELECT @Names

#1