sql-server - sql - 将多列(不同数据类型)转换为一列(一行为列)

Question

我试图将具有不同数据类型的多个列转换为一列(列为一行)，所有列都将转换为 varchar。

我要转换的表格:

+----+----------------+-------------------------+------------+------------+
| 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    |
+----+----------------+---------------------+------------+------------+---+

我需要创建一个单独的列，其中包含所有列的值(转换为 varchar)及其各自的键值，我希望是这样的:

+----+-------------------------+
| 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            |
+----+-------------------------+

我如何使用 SQL-Server 完成这项任务？谢谢

Answer 1

使用合并:

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

结果:

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