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

user_3413150 0
Viewed 1519

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

sql-server
4 Answers

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
user_106140910

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
user_15700000

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.)

user_74910

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
user_15700000
Related