user17824023
user17824023

Reputation: 47

How to extract multiple columns into single column

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

Upvotes: 1

Views: 1383

Answers (2)

Steffen
Steffen

Reputation: 1

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;

Upvotes: 0

D-Shih
D-Shih

Reputation: 46249

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

Upvotes: 2

Related Questions