翻转SQL结果(Flip SQL Result)

如何做到这一点的SQL？

我有选择，结果是：

1 a
2 b
3 c
4 d
5 e

然后我想要的结果，显示一个类似

1 2 3 4 5
a b c d e

多谢你们！

Answer 1:

您可以使用透视

select [1],[2],[3],[4],[5]
from
(
  select column1, column2
  from mytable
) d
pivot
(
  max(column2)
  for column1 in ([1],[2],[3],[4],[5])
) piv;

SQL FIDDLE DEMO

Answer 2:

看起来你需要使用枢声明， https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

-- Potentially your case example:
DECLARE @t TABLE (id int, val CHAR(1));
INSERT INTO @t VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e')
-- Pivot table with one row and five columns
SELECT 'Value' AS 'Id', 
        [1], [2], [3], [4], [5]
FROM @t AS SourceTable
PIVOT
(
max(val)
FOR id IN ([1], [2], [3], [4], [5])
) AS PivotTable;

Answer 3:

您可以使用PIVOT：

-- Create sample data

CREATE TABLE Test
(
  A INT,
  B NVARCHAR(10)
)
INSERT INTO Test (A, B) VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e')

-- Pivot query

SELECT [1],[2],[3],[4],[5]
FROM
(
  SELECT A, B
  FROM Test
) x
PIVOT
(
  MIN(B)
  FOR A IN ([1],[2],[3],[4],[5])
) piv;

OUTPUT：

1   2   3   4   5
a   b   c   d   e

SQL FIDDLE

至于你问的，动态的支点应该是这样的：

SELECT @cols += ([Name]) + ','
FROM   (
        SELECT  Name
        FROM    Table
        ) a
SET @cols   = LEFT(@cols, LEN(@cols) - 1)
SET @sql    = 
'SELECT * 
 FROM 
    (
    SELECT Col1 as [NamePiv], 
           Col2 as [Val]
    FROM   Table1
    ) x
    PIVOT
    (
        MIN (Val)
        FOR NamePiv IN (' + @cols +') 
    ) p'

文章来源: Flip SQL Result