如何在SQL Server 2005中将单行转换为列

我会让查询继续返回当前的数据,作为单独的列,并让UI代码根据需要格式化它。使其更灵活,以便可以使用相同数据的X不同显示,全部使用同一个查询作为数据源。

也许我错过了什么但你不能

选择col1+''+col2+。。。。把它们连在一起。

也就是说,如果我必须从一个表的一行返回(比如说)来自5个独立列的值,我会将其修改为如下:

SELECT Col1 as Data, 1 as MyOrder from MyTable where MyTableId = @DesiredRow
UNION ALL SELECT Col2, 2 from MyTable where MyTableId = @DesiredRow
UNION ALL SELECT Col3, 3from MyTable where MyTableId = @DesiredRow
UNION ALL SELECT Col4, 4 from MyTable where MyTableId = @DesiredRow
UNION ALL SELECT Col5, 5 from MyTable where MyTableId = @DesiredRow
order by MyOrder

(必须看看格式是否正确…)

...select cast(Col1 as varchar(100)) As Data...

(2) 它很可能会按照所选的顺序对返回的行进行排序,我在orderby中添加了这个顺序,只是为了确定。而且,(3)您的里程可能会有所不同,这取决于我刚才不知道的应用考虑因素。

祝你好运!

PIVOT/UNPIVOT functions 这正是它的作用。

SELECT SingleColumn FROM
  (SELECT CAST(Column1 AS VARCHAR(128)) AS c1,
          CAST(Column2 AS VARCHAR(128)) AS c2,
          CAST(Column3 AS VARCHAR(128)) AS c3,
          CAST(Column4 AS VARCHAR(128)) AS c4,
          CAST(Column5 AS VARCHAR(128)) AS c5,
          CAST(Column6 AS VARCHAR(128)) AS c6
    FROM (...) AS t
  ) AS p
  UNPIVOT (SingleColumn FOR cols IN (c1, c2, c3, c4, c5, c6)) AS unp