Original Table is like this:
CABLE_NUMBER FROM TO SIZE COLOUR BKB-927826 BKB-927608 BKB-927199 BKB-927206
7 367 343 0.35 L 1 1 1 1
138 367 130P 0.35 G 1 1
150 367 38P 0.35 B 1 1 1 1
335 367 257P 0.35 B 1 1 1 1
41 367 25P 0.35 L 1 1 1 1
284 367 536P 0.35 L 1 1 1 1
692 367 88P 0.35 L NULL 1
781 367 36P 0.35 L 1 1
200 367 36P 0.35 P 1 1 1 1
792 76 333 0.35 W/B NULL 1
Where there is 1 it means its active and unknown or null means not active, instead of the 1 i and to diplay the column name eg. BKB-927206 which is a part number.
I want the display to be like this:
CABLE_NUMBER FROM TO SIZE COLOUR UsedToHarness826 UsedToHarness608 UsedToHarness199 UsedToHarness206
7 367 343 0.35 L [BKB-927826] [BKB-927608] [BKB-927199] [BKB-927206]
138 367 130P 0.35 G [BKB-927199]
150 367 38P 0.35 B [BKB-927826] [BKB-927608] [BKB-927199] [BKB-927206]
335 367 257P 0.35 B [BKB-927826] [BKB-927608] [BKB-927199] [BKB-927206]
41 367 25P 0.35 L [BKB-927826] [BKB-927608] [BKB-927199] [BKB-927206]
284 367 536P 0.35 L [BKB-927826] [BKB-927608] [BKB-927199] [BKB-927206]
692 367 88P 0.35 L NULL [BKB-927206]
781 367 36P 0.35 L [BKB-927199]
200 367 36P 0.35 P [BKB-927826] [BKB-927608] [BKB-927199] [BKB-927206]
792 76 333 0.35 W/B NULL [BKB-927206]
What I have tried:
SELECT top 10
[CABLE_NUMBER]
,[FROM]
,[TO]
,[SIZE]
,[COLOUR]
, REPLACE(REPLACE([BKB-927826], '1', '[BKB-927826]'), '', '' ) AS UsedToHarness826
, REPLACE(REPLACE([BKB-927826], '1', '[BKB-927608]'), '', '' ) AS UsedToHarness608
, REPLACE(REPLACE([BKB-927199], '1', '[BKB-927199]'), '', '' ) AS UsedToHarness199
, REPLACE(REPLACE([BKB-927206], '1', '[BKB-927206]'), '', '' ) AS UsedToHarness199
FROM [Defect].[dbo].[Sheet2$]