CODEWITHSUNDEEP
mysqlsqlsql-server-2008
Resonates7
Resonates7

Reputation: 53

Dynamic SQL query to populate column with values in other columns

I'm trying to write a SQL query for a data quality report that presents data quality failed values from multiple columns into one column. Please see the below example

FACT TABLE 

Ac_Nm   INAmt  Ast  Rcs  
123      100  5000   NA  
456      200  -200   Yes  
789      -300 1000   No

DESIRED OUTPUT (POPULATE VAL COLUMN)

Ac_Nm   Is_Clm  Val  
123     RCS     NA  
456     Ast    -200  
789     InAmt  -300

How do I write a SQL query to populate the Val column? I've got the rest of the data quality report query written.

In the above example I have a fact table where data quality issues have been identified in various columns (negative values, 'NA' values where there should be a Yes/No response, etc). I'd like to know how to write a dynamic SQL query that returns that failed value from the Fact Table depending on the account number and the column name. In the first row the desired output lists the account number(123) with the issue column name (RCS) containing the value at issue, and the Val column listing the value causing the issue (NA). I just need to know how to write a SQL query to populate the Val column depending on the account num and issue column.

Upvotes: 0

Views: 2776

Answers (1)

Robert Bain
Robert Bain

Reputation: 9586

You could do it using case statements, assuming only one column is going to have a "bad" value, as follows:

SELECT Ac_Nm,
       CASE WHEN INAmt < 0     THEN 'INAmt'
            WHEN Ast   < 0     THEN 'Ast'
            WHEN Rcs   = 'N/A' THEN 'RCS'
       ELSE NULL END AS Is_Clm,
       CASE WHEN INAmt < 0     THEN CONVERT(INAmt, char)
            WHEN Ast   < 0     THEN CONVERT(Ast, char)
            WHEN Rcs   = 'N/A' THEN Rcs
       ELSE NULL END AS Val
FROM fact_table;

Then to filter out the NULL values, wrap the query in a subquery and select from it. If you need a hand doing that, give me a shout.

Upvotes: 0

Related Questions