IF OBJECT_ID('#TestTable') IS NOT NULL 
DROP TABLE #TestTable

CREATE TABLE #TestTable (
    ID INT IDENTITY(1,1) NOT NULL,
    EmplID INT NULL,
    MgrID  INT NULL,
    CustID INT NULL,
    LocID  INT NULL,
    PRIMARY KEY (ID))
    
INSERT INTO #TestTable
SELECT 111,1011,10001,789 UNION
SELECT 222,1022,10002,890 UNION
SELECT 333,1033,10003,789 UNION
SELECT 444,1044,10004,890 UNION
SELECT 555,1055,10005,789 UNION
SELECT 666,1066,10006,890 UNION
SELECT 777,1077,10007,789 UNION
SELECT 888,1088,10008,890 UNION
SELECT 999,1099,10009,678 

SELECT * FROM #TestTable

DECLARE 
    @Response1 VARCHAR(50)
   ,@Response2 VARCHAR(50)
   ,@LocID     INT
   ,@strSQL    VARCHAR(MAX)

SET @Response1 = 'EmplID'
SET @Response2 = 'CustID'
SET @LocID     = 890

SET @strSQL = '
SELECT 
   t.ID
  ,'+@Response1+' AS '+@Response1+' 
  ,'+@Response2+' AS '+@Response2+'
FROM
    #TestTable AS t
WHERE
   LocID = '+CAST(@LocID AS VARCHAR(5))+'
'

EXEC(@strSQL)

ID	EmplID	CustID
2	222	10002
4	444	10004
6	666	10006
8	888	10008

IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL 
DROP TABLE #TestTable

CREATE TABLE #TestTable (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Year] INT NULL,
    [Month] INT NULL,
    [FaultType1] VARCHAR(50) NULL,
    [FaultType2] VARCHAR(50) NULL,
    [FaultType3] VARCHAR(50) NULL,
    PRIMARY KEY (ID))
    
INSERT INTO #TestTable
SELECT 2011,11,'Mfg Defect','','' UNION ALL
SELECT 2012,1,'','Power Overload','' UNION ALL
SELECT 2012,3,'','','User Error' UNION ALL
SELECT 2012,4,'Mfg Defect','','' UNION ALL
SELECT 2012,4,'','','User Error' UNION ALL
SELECT 2012,6,'','','User Error' UNION ALL
SELECT 2013,1,'','Power Overload','' UNION ALL
SELECT 2013,1,'Mfg Defect','','' UNION ALL
SELECT 2013,2,'','Power Overload','' 

--SELECT * FROM #TestTable


DECLARE 
    @Response VARCHAR(50)
   ,@Year     INT
   ,@strSQL   VARCHAR(MAX)

SET @Response = 'FaultType1'
SET @Year     = 2012

--SET @Response = 'FaultType2'
--SET @Year     = 2011

--SET @Response = 'FaultType3'
--SET @Year     = 2012

SET @strSQL = '
SELECT 
   t.[ID]
  ,t.[Year]
  ,t.[Month]
  ,'+@Response+' AS '+@Response+' 
FROM
    #TestTable AS t
WHERE
   NULLIF('+@Response+','''') IS NOT NULL
   AND Year >= '+CAST(@Year AS VARCHAR(5))+'
ORDER BY
   Year, MONTH
'

EXEC(@strSQL)

IF OBJECT_ID('dbo.FaultTable1') IS NOT NULL 
DROP TABLE dbo.FaultTable1
IF OBJECT_ID('dbo.FaultTable2') IS NOT NULL 
DROP TABLE dbo.FaultTable2
IF OBJECT_ID('dbo.FaultTable3') IS NOT NULL 
DROP TABLE dbo.FaultTable3

CREATE TABLE dbo.FaultTable1 (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Year] INT NOT NULL,
    [Month] INT NULL,
    [FaultType] VARCHAR(50) NULL CHECK ([FaultType] = 'Mfg Defect'),
    PRIMARY KEY ([ID],[Year]))

CREATE TABLE dbo.FaultTable2 (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Year] INT NOT NULL,
    [Month] INT NULL,
    [FaultType] VARCHAR(50) NULL CHECK ([FaultType] = 'Power Overload'),
    PRIMARY KEY ([ID],[Year]))
    
CREATE TABLE dbo.FaultTable3 (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Year] INT NOT NULL,
    [Month] INT NULL,
    [FaultType] VARCHAR(50) NULL CHECK ([FaultType] = 'User Error'),
    PRIMARY KEY ([ID],[Year]))

    
INSERT INTO FaultTable1
SELECT 2011,11,'Mfg Defect' UNION ALL
SELECT 2012,4,'Mfg Defect' UNION ALL
SELECT 2013,1,'Mfg Defect' 

INSERT INTO FaultTable2
SELECT 2012,1,'Power Overload' UNION ALL
SELECT 2013,1,'Power Overload' UNION ALL
SELECT 2013,2,'Power Overload' 

INSERT INTO FaultTable3
SELECT 2012,3,'User Error' UNION ALL
SELECT 2012,4,'User Error' UNION ALL
SELECT 2012,6,'User Error' 

CREATE VIEW dbo.vwFaults
WITH SCHEMABINDING
AS
SELECT 
    [ID],
    [Year],
    [Month],
    [FaultType] 
FROM dbo.FaultTable1
UNION ALL
SELECT 
    [ID],
    [Year],
    [Month],
    [FaultType] 
FROM dbo.FaultTable2
UNION ALL
SELECT 
    [ID],
    [Year],
    [Month],
    [FaultType] 
FROM dbo.FaultTable3

DECLARE @FaultType VARCHAR(50)
SET @FaultType = 'Power Overload'

SELECT
    [ID],
    [Year],
    [Month],
    [FaultType] 
FROM
    dbo.vwFaults
WHERE
    FaultType = @FaultType
    AND ID > 0

ID	Year	Month	FaultType
1	2012	1	Power Overload
2	2013	1	Power Overload
3	2013	2	Power Overload

;
WITH cte as
(
SELECT 
   t.[ID]
  ,t.[Year]
  ,t.[Month]
  , CASE WHEN @Response = 'FaultType1' THEN FaultType1 
		WHEN @Response = 'FaultType2' THEN FaultType2
		WHEN @Response = 'FaultType3' THEN FaultType3
  END AS  FaultType
FROM
    #TestTable AS t
WHERE Year >= @Year
)
SELECT * FROM cte
WHERE FaultType>''
ORDER BY Year, MONTH