How to get different column names with the union statement in SQL

Question

I'm looking for errors in my data; throughout different columns in the table. I'm using an UNION statement to run multiple queries at once, looking for the errors in the table. All the errors in the table that are found are put in one column under the column name of the very first query in which an error is found. Is there a way to split the column in such a way where every error that is found, by a separate query in the UNION statement, is also presented as a separate column/entity?

This is the code I'm using:

SELECT DISTINCT
[1001account] 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE
StresstestaccountEnabled LIKE '%Yes%' AND BalancesheetAmount <> 0
AND
PATINDEX('%[^0-9]%', [1001account]) > 0 OR [1001account] IS NULL

UNION

SELECT DISTINCT
[5028account] 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE
StresstestaccountEnabled LIKE '%Yes%' AND BalancesheetAmount <> 0
AND
PATINDEX('%[^0-9]%', [5028account]) > 0 OR [5028account] IS NULL

UNION

SELECT DISTINCT
[BalanceSheetType] 
FROM
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE
StressTestAccountEnabled LIKE '%Yes%' AND BalanceSheetAmount <> 0
AND
[BalanceSheetType] NOT LIKE '%Assets%'
AND
BalanceSheetType NOT LIKE '%Liabilities%'

UNION

SELECT DISTINCT 
CorepRRR 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE '%Yes%' AND BalanceSheetAmount<>0
AND
CorepRRR NOT LIKE '%D%'
AND
CorepRRR NOT LIKE '%R%'
AND
CorepRRR NOT LIKE '%P%'
AND
CorepRRR NOT LIKE '%Not Applicable%'

UNION

SELECT 
[CoreGroupId] 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE '%Yes%' AND BalanceSheetAmount<>0
AND
[CoreGroupId] NOT IN (2, 3, 4, 5, 6, 7, 8, 9, 11 ,14, 15, 99)

UNION

SELECT 
CoreGroupDescription 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE '%Yes%' AND BalanceSheetAmount<>0
AND
[CoreGroupDescription] NOT IN 
( 'DLL', 'ABB_Assets', 'ABB_Liab', 'LRDW', 'OBV', 'VRN', 'Force', 'ROB', 'HedgeAccounting', 'LRDW_US', 'CorepDefaultsCorrections', 'Corrections'  )

UNION

SELECT DISTINCT 
[Country] 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE 'Yes'
AND
BalanceSheetAmount <> 0 
AND
Country NOT LIKE '%[^a-z]%'
AND
(
LEN(Country)>2 OR LEN(Country)<2 
)

UNION

SELECT DISTINCT 
CountryOfRisk 
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE 'Yes'
AND
BalanceSheetAmount != 0
AND
Country NOT LIKE '%[^a-z]%'
AND
(
LEN(Country)>2 OR LEN(Country)<2
)

UNION


SELECT DISTINCT 
InterestType
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE 'Yes'
AND
BalanceSheetAmount <> 0
AND
InterestType NOT LIKE 'Fixed'
AND
InterestType NOT LIKE 'Floating'

UNION

SELECT DISTINCT
InterestRate
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE
BalanceSheetAmount <> 0
AND
StressTestAccountEnabled LIKE 'Yes'
AND
(
InterestRate > 20
OR
InterestRate < -2
)

UNION

SELECT DISTINCT 
LocalBalance
FROM 
[Cris_Ocean_tmp].[dbo].[NiiStressTest2018_v2_PUBLISHED_201812_CC_0326]
WHERE 
StressTestAccountEnabled LIKE 'Yes'
AND
BalanceSheetAmount <> 0
AND
LocalBalance IS NULL

This is a sample of the output:

[1001account]
NULL
1 -19.4163150000
2 -17.4100000000
3 -7.0000000000
4 -6.0000000000

The NULL is classified as an error in my query and it is found in the column [1001account], but the other values in row 2, 3 and 4 belong to another column: Swaprates and Interestrates.

How can I visualise this in a way that the user can see that the rows 2, 3 and 4 belong to a different column?

Answer 1

Just add a second column in the SELECT list, which tells you where the value comes from:

SELECT DISTINCT
[1001account] AS [value], '1001account' AS [column]

The same for other columns:

SELECT DISTINCT
[5028account] AS [value], '5028account' AS [column]