Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Original Table is like this:

SQL
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:
SQL
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:

SQL
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$]
Posted
Updated 7-Oct-20 3:43am
v2

1 solution

Use a CASE statement:
SQL
SELECT
    [CABLE_NUMBER],
    [FROM],
    [TO],
    [SIZE],
    [COLOUR],
    CASE [BKB-927826] WHEN 1 THEN '[BKB-927826]' END As UsedToHarness826
    CASE [BKB-927608] WHEN 1 THEN '[BKB-927608]' END As UsedToHarness608
    CASE [BKB-927199] WHEN 1 THEN '[BKB-927199]' END As UsedToHarness199
    CASE [BKB-927206] WHEN 1 THEN '[BKB-927206]' END As UsedToHarness206
FROM 
    [Defect].[dbo].[Sheet2$]
;
CASE (Transact-SQL) - SQL Server | Microsoft Docs[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900