How do you PIVOT by bit type in SQL Server?

Question

This is probably a very simple question. All I want to do is make a column into a row whose data type is small.

SUM, MIN, MAX does not work with bits. COUNT works, but I really don't want to count. I just want to move all things from columns to rows, as if I took a pair of scissors, cut out the information and moved it by -90 degrees.

Answer 1

The solution to this is to transfer the bit data type to the data type, which is accepted in aggregate functions. For example,

SELECT MAX(CAST(BitColumn AS TINYINT)) 

distinguishes the value of BitColumn to the tinyint title. The operator returns 1 if the BitColumn contains at least one value 1; otherwise, it returns 0 (if all values ​​are non-zero).

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 expand 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 maximum value of BitColumn is converted back from tinyint to bit. This is not required.

results

 ID Name 1 Name 2 Name 3 -------------------------- 1 1 0 1 2 1 1 1 3 0 0 0 

Alternate query for SQL Server 2005 and later uses the PIVOT statement

 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 

Answer 2

 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