How to extract multiple columns into single column-CodePudding

I am trying very hard to implement the below requirements, but I am not understanding is it possible to do using sql server. Kindly suggest me.

In the below table for every ID there may be 1 X mark or 2 X marks or 3 X marks in other columns as shown in below table. So I need to write a query to get a new column name with all the X marked columns. Kindly refer the output table sample.

Table1

Id	CurrentAmount	RiskRating	ShortName	NoExceptions	ABCD	EFGH	IJKL	MNOP
1010	100	2	John Krsp	null	X	null	null	null
1011	200	5	David sku	null	X	null	null	null
1022	300	1	Patrik	null	X	X	X	null

db<>fiddle here

Desired Output:

Id	CurrentAmount	RiskRating	ShortName	ExceptionCode
1010	100	2	John Krsp	ABCD
1011	200	5	David sku	ABCD
1022	300	1	Patrik	ABCD
1022	300	1	Patrik	EFGH
1022	300	1	Patrik	IJKL

CodePudding user response：

You can try to use CROSS APPLY with VALUE

select t1.id,
       t1.CurrentAmount,
       t1.RiskRating,
       t1.ShortName,
       v.Expectioncode
from table1 t1 CROSS APPLY (
   VALUES (ABCD,'ABCD'),
   (EFGH,'EFGH'),
   (IJKL,'IJKL'),
   (MNOP,'MNOP')
) v (val,Expectioncode)
WHERE v.val IS NOT NULL

Edit

From your comment, If some of the data types are not varchar you can try to use CAST as the same type (from your sample code you might cast as VARCHAR(10) which might as same as other columns.) otherwise you might get a converting error.

select t1.id,
       t1.CurrentAmount,
       t1.RiskRating,
       t1.ShortName,
       v.Expectioncode
from table1 t1 CROSS APPLY (
   VALUES (ABCD,'ABCD'),
   (EFGH,'EFGH'),
   (IJKL,'IJKL'),
   (CAST(MNOP AS VARCHAR(10)),'MNOP')
) v (val,Expectioncode)
WHERE v.val IS NOT NULL

sqlfiddle