sql - Transform multiple columns (different data type) into one column (one row for column)

Question

I trying to transform multiple columns with different data type into one column (one row for column), all column would be cast to varchar.

My table to transform:

+----+----------------+-------------------------+------------+------------+
| id | column_varchar |     column_datetime     | column_int | column_bit |
+====+================+============+==========+===========+===========+===+
| 1  |      NULL      |           NULL          |     NULL   |     1      |
+----+----------------+-----------------+------------+------------+-------+
| 2  |      NULL      | 2019-01-15 00:00:00.000 |     NULL   |    NULL    |
+----+----------------+-------------------------+------------+------------+  
| 3  |      apple     |           NULL          |     NULL   |    NULL    |
+----+----------------+---------------------+------------+------------+---+
| 4  |      NULL      |           NULL          |     NULL   |     0      |
+----+----------------+---------------------+------------+------------+---+
| 5  |      NULL      | 2018-01-15 00:00:00.000 |     NULL   |    NULL    |
+----+----------------+---------------------+------------+------------+---+
| 6  |      NULL      |           NULL          |     25     |    NULL    |
+----+----------------+---------------------+------------+------------+---+

i need create one single column that contains the value of all columns (cast to varchar) with its respective key value, I expect something like:

+----+-------------------------+
| id |        column_value     |
+====+=========================+
|  1 |              1          |
+----+-------------------------+
|  2 | 2019-01-15 00:00:00.000 |
+----+-------------------------+
|  3 |           apple         |
+----+-------------------------+
|  4 |            0            |
+----+-------------------------+
|  5 | 2018-01-15 00:00:00.000 |
+----+-------------------------+
|  6 |           25            |
+----+-------------------------+

How can i accomplish this task with SQL-Server? thanks

I think using COALESCE proposed by @ level3looper is the easiest option to implement — bryannsi
– bryannsi, Commented Jan 16, 2019 at 19:11
I think it is very interesting to use Cross Apply with xml proposed by @JohnCappelletti, but a bit more complicated than I need. — bryannsi
– bryannsi, Commented Jan 16, 2019 at 19:13

level3looper · Accepted Answer · 2019-01-16 18:31:36Z

2

Using Coalesce:

Select id,coalesce
             (
               cv,
               Cast(cd As VarChar(50)),
               Cast(ci As VarChar(50)),
               Cast(cb As VarChar(50))
             ) As ColVal
From #tbl

Result:

id  ColVal
1   1
2   Jan 15 2019 12:00AM
3   Apple
4   0
5   Jan 15 2019 12:00AM
6   25

answered Jan 16, 2019 at 18:31

level3looper

1,0411 gold badge8 silver badges10 bronze badges

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

Comments

John Cappelletti · Accepted Answer · 2019-01-16 18:41:03Z

Here is one option using a bit of XML in concert with a CROSS APPLY (or two)

Other options may be more performant, but the beauty here is that it will consume virtually any table, view or query without having to specify the columns.

Example

Select A.ID 
      ,C.*
 From YourTable A
 Cross Apply (values (convert(xml,(Select A.* for XML RAW)))) B(XMLData)
 Cross Apply (
                Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                      ,Value = xAttr.value('.','varchar(max)')
                 From  XMLData.nodes('//@*') xNode(xAttr)
             ) C
 Where Item not in ('ID')

Returns

ID  Item              Value
1   column_bit        1
2   column_datetime   2019-01-15T00:00:00
3   column_varchar    apple
4   column_bit        0
5   column_datetime   2019-01-15T00:00:00
6   column_int        25

Philip Kelley · Accepted Answer · 2019-01-16 18:26:25Z

0

The following should do it:

SELECT
   id
  ,column_varchar  column_value  --  Column alias in only needs to be set in the first select
 from MyTable
 where column_varchar is not null
UNION ALL SELECT
   id
  ,convert(varchar(50), column_datetime, 121)
 from MyTable
 where column_datetime is not null
UNION ALL SELECT
   id
  ,cast(column_int as (varchar(50))
 from MyTable
 where column_int is not null
UNION ALL SELECT
   id
  ,cast(column_bit as (varchar(50))
 from MyTable
 where column_bit is not null

(Just to say, for a whole lot of reasons doing this is probably not a good idea.)

answered Jan 16, 2019 at 18:26

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

1 Comment

Philip Kelley Over a year ago

I assumed that multiple columns in a row might be NOT NULL. If this is not the case, then this solution is overkill.

John Cappelletti · Accepted Answer · 2019-01-16 18:33:43Z

0

If only ONE value per row, you can use concat()

You may notice that I did a convert on the datetime column to maintain the format.

Example

Select A.ID
      ,column_value = concat([column_varchar],convert(varchar(25),[column_datetime],25),[column_int],[column_bit])
 From  YourTable A

Returns

1   1
2   2019-01-15 00:00:00.000
3   apple
4   0
5   2019-01-15 00:00:00.000
6   25

answered Jan 16, 2019 at 18:33

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

4 Comments

Philip Kelley Over a year ago

This will work if there is always one and only one NOT NULL column.

John Cappelletti Over a year ago

@PhilipKelley Thus my first line.

bryannsi Over a year ago

@JohnCappelletti The function concat() is very interesting, the problem occurs with the null values. Thanks for the reply.

John Cappelletti Over a year ago

@bryannsi concat() will treat NULL values as an empty string ''

Collectives™ on Stack Overflow

sql - Transform multiple columns (different data type) into one column (one row for column)

4 Answers 4

Comments

Comments

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related