SQL rows to columns (Pivot with just bit values)

Question

I am trying to convert my resultant rows in to columns, but only with the bit values. To explain better what I mean, my table looks like this;

Id    Item
-------------------
1     TextLineA
1     TextLineB
2     TextLineA

And what I am trying to achieve is a layout like this;

Id TextLineA  TextLineB
--------------------------
1     True       True
2     True       False

Apologies for my poor formatting, but hopefully it conveys what I am looking for. I have tried Pivot but I don't have anything to sum over as I just want the true/false values rather than a sum.

Also, I have about 15 columns and I don't mind statically writing them out. Any pointers would be greatly appreciated!

Answer 1

Use Pivot

SELECT id,
       CASE WHEN [TextLineA] IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END [TextLineA],
       CASE WHEN [TextLineB] IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END [TextLineB]
FROM   Yourtable
       PIVOT (Max(Item)
             FOR item IN ([TextLineA],[TextLineB])) piv 

Answer 2

Tried using a case statement?

SELECT
    CASE MakeFlag
        WHEN 1 THEN 'True'
        WHEN 0 THEN 'False'
    END
FROM [AdventureWorks2012].[Production].[Product]

Answer 3

This sort of approach will solve this:

DECLARE @Example TABLE (Id int, Item varchar(20))

INSERT INTO @Example (ID, Item)
SELECT 1, 'TextLineA' UNION ALL
SELECT 1, 'TextLineB' UNION ALL
SELECT 2, 'TextLineA' UNION ALL
SELECT 3, 'TextLineA' UNION ALL
SELECT 3, 'TextLineB' UNION ALL
SELECT 3, 'TextLineC' 

select ID, 
  MAX(CASE WHEN Item='TextLineA' THEN 1 ELSE 0 END) as TextLineA,
  MAX(CASE WHEN Item='TextLineB' THEN 1 ELSE 0 END) as TextLineB,
  MAX(CASE WHEN Item='TextLineC' THEN 1 ELSE 0 END) as TextLineC
FROM @Example
GROUP BY ID

This results in:

ID  TextLineA   TextLineB   TextLineC
1   1           1           0
2   1           0           0
3   1           1           1

Where 1 is true and 0 is false.