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

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.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.