How to extract multiple columns into single column

Question

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

Please post all queries, data samples and expected results as text NOT as images. We can't copy text from screen shots... — SOS
– SOS, Commented Apr 10, 2022 at 2:34
I am unable to add the sample data, its always taking wrong format — user17824023
– user17824023, Commented Apr 10, 2022 at 2:45
Anyone here can help by editing the question later if the formatting is off. You can also create a fiddle and post the SQL, using sqlfiddle.com Try the "Text to DDL" option which let's you paste delimited data and create the DDL from it automatically. — SOS
– SOS, Commented Apr 10, 2022 at 2:49
Thank you for the response, I have added the sample data, Kindly help me, Thank you in advance — user17824023
– user17824023, Commented Apr 10, 2022 at 2:59
Please do not upload images of code/errors when asking a question. and Why should I provide a Minimal Reproducible Example for a very simple SQL query? — Ken White
– Ken White, Commented Apr 10, 2022 at 3:05

D-Shih · Accepted Answer · 2022-04-10 03:32:12Z

2

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

edited Apr 10, 2022 at 3:32

answered Apr 10, 2022 at 3:05

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user17824023 Over a year ago

Thank you D-Shih, there is one column 'MNOP' which is in float data type, so I am getting an error, can you please suggest me. "Error converting data type varchar to float."

D-Shih Over a year ago

@user17824023 I edit my answer hope that help

Steffen · Accepted Answer · 2022-04-10 09:11:03Z

0

Another way is to use the UNPIVOT function. One advantage is that the column names do not have to be defined again.

SELECT [Id]
      ,[CurrentAmount]
      ,[RiskRating]
      ,[ShortName]
      ,ExceptionCode
  FROM (Select [Id]
      ,[CurrentAmount]
      ,[RiskRating]
      ,[ShortName]
      , ABCD, EFGH, IJKL, CAST(MNOP AS VARCHAR(10)) MNOP
      FROM [Table1]) p
  UNPIVOT 
    (ExceptionCode_Value FOR ExceptionCode IN
        (ABCD, EFGH, IJKL, MNOP)) AS unpvt;

answered Apr 10, 2022 at 9:11

Steffen

11 bronze badge

Collectives™ on Stack Overflow

How to extract multiple columns into single column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related