Based on Stevens easy to use sample data (STRONG hint for future questions... 😉 ) here's my approach. The main difference is the "depending column" having a static header ("FaultType", similar to Stevens approach using three tables).

The reasons why I wouldn't use one of Stevens approaches:

a) dynamic SQL: I'm trying to avoid it where it's not absolutely required since it makes the code harder to read (especially if there are multiple quotes involved) and there's a risk of SQL injection that needs to be taken care of. @steven-3: I'd always use sp_executesql in such a scenario instead of exec().

b) separaet tables combined in a union-based view: this concept violates normalization and would require additional table(s) if there are new fault types. I'd strongly vote against such a concept.

;

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