这可能是一个非常简单的问题。我真正想做的就是将列转换为行,其数据类型为位(bit)。SUM、MIN、MAX无法用于位(bit)。COUNT可以使用,但我真的不想计数。我只想将所有列中的内容移动到行中,就好像我拿起一把剪刀,剪下信息并将其向左旋转90度一样。
SELECT MAX(CAST(BitColumn AS TINYINT))
将BitColumn值转换为tinyint数据类型。如果BitColumn至少包含一个值为1,则该语句返回1;否则,它返回0(除非所有值都为null)。
假设以下情况:
CREATE TABLE MyTable (ID INT, Name VARCHAR(10), BitColumn BIT);
INSERT INTO MyTable VALUES (1, 'Name 1', 1);
INSERT INTO MyTable VALUES (1, 'Name 2', 0);
INSERT INTO MyTable VALUES (1, 'Name 3', 1);
INSERT INTO MyTable VALUES (2, 'Name 1', 1);
INSERT INTO MyTable VALUES (2, 'Name 2', 1);
INSERT INTO MyTable VALUES (2, 'Name 3', 1);
INSERT INTO MyTable VALUES (3, 'Name 1', 0);
INSERT INTO MyTable VALUES (3, 'Name 2', 0);
INSERT INTO MyTable VALUES (3, 'Name 3', 0);
SELECT ID,
CAST(MAX(CASE Name WHEN 'Name 1' THEN CAST(BitColumn AS TINYINT) ELSE NULL END) AS BIT) AS [Name 1],
CAST(MAX(CASE Name WHEN 'Name 2' THEN CAST(BitColumn AS TINYINT) ELSE NULL END) AS BIT) AS [Name 2],
CAST(MAX(CASE Name WHEN 'Name 3' THEN CAST(BitColumn AS TINYINT) ELSE NULL END) AS BIT) AS [Name 3]
FROM MyTable
GROUP BY ID
ORDER BY ID
ID Name 1 Name 2 Name 3
--------------------------
1 1 0 1
2 1 1 1
3 0 0 0
对于SQL Server 2005及更高版本,另一种查询方式是使用PIVOT运算符。
SELECT ID, [Name 1], [Name 2], [Name 3]
FROM
(
SELECT ID, Name, CAST(BitColumn AS TINYINT) AS BitColumn
FROM MyTable
) as SourceTable
PIVOT
(
MAX(BitColumn) FOR Name in ([Name 1], [Name 2], [Name 3])
) AS PivotTable
SELECT [1], [2], [3]
FROM
(
SELECT ID, CAST(BitColumn AS TINYINT) AS INTColumn
FROM MyTable
) as SourceTable
PIVOT
(
MAX(INTColumn) FOR ID in ([1], [2], [3])
) AS PivotTable