How do you PIVOT on a Bit datatype in SQL Server?
This is probably a very simple question. All I want to really do is to make the column into a row whose data type is a bit.
SUM, MIN, MAX doesn't work on bits. COUNT works but I really don't want to c开发者_运维问答ount. I just want to move all the stuff from columns into rows like if I took a pair of scissors, cut the information and moved it -90 degrees.
The solution to this is to cast the bit data type to a data type that is accepted in aggregate functions. For example,
SELECT MAX(CAST(BitColumn AS TINYINT))
casts the BitColumn value to a tinyint datatype. The statement returns 1 if BitColumn contains at least one value of 1; otherwise, it returns 0 (unless all values are null).
Assuming the following:
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);
You can pivot this data using the following query
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
In this case, the max BitColumn value is converted back from tinyint to bit. This is not required.
The results are
ID Name 1 Name 2 Name 3
--------------------------
1 1 0 1
2 1 1 1
3 0 0 0
An alternative query, for SQL Server 2005 and later, uses the PIVOT operator
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
精彩评论