How do you PIVOT on a Bit datatype in SQL Server?

Question

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 count. 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.

Answer 1

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