How can I see where the results from my COALESCE statement have been retrieved from?-CodePudding

I have five fields which I need to look at: FirstName, Surname, DateOfBirth, id and Postcode.

If I run this (using SQL Server Management Studio (SSMS)):

SELECT COALESCE(FirstName, Surname, DateOfBirth, id, Postcode)
FROM table1

It returns results like this:

1.  DAVID
2.  SUSAN
3.  764329
4.  FRANK
5.  STEPHANIE
6.  HARRIS
7.  10/10/1967
8.  E3 5QP
9.  DAVID
10. JOHN

The trouble with this is that it isn't always easy to determine where the results come from. For example, DAVID and JOHN could easily be first names or surnames.

Is there a way to see the origin of each result? Something like this:

1.  DAVID FirstName
2.  SUSAN FirstName
3.  764329 id
4.  FRANK FirstName
5.  STEPHANIE FirstName
6.  HARRIS Surname
7.  10/10/1967 DateOfBirth
8.  E3 5QP Postcode
9.  DAVID Surname
10. JOHN Surname

Perhaps two columns of results would be good: one with the result, and one with the origin.

CodePudding user response：

You didn't specify RDBMS, but if you really want to use coalesce then you can try something like this:

SELECT COALESCE(CONCAT(FirstName,' First Name'), CONCAT(Surname,' Surname'),...)
FROM table1

Use CONCAT or a specific way of your RDBMS to concatenate. CONCAT should return null if one of the elemets in null, so it should work the same.

You can also generate a new column using CASE EXPRESSION as @manibharataraju suggested in the comments.