在SQL中将临时表解析为另一个，保留列名

I have a temporary table which looks something like this.

我有一个看起来像这样的临时表。

| USER_NO | MY_CODE1      | MY_CODE2      |  
| UserNo1 | UserNo1_Code1 | UserNo1_Code2 |  
| UserNo2 | UserNo2_Code1 | UserNo2_Code2 |

Where the first line is column names, the 2nd and 3rd are values. I need to append these records into another table.

第一行是列名,第二行是第3行。我需要将这些记录附加到另一个表中。

| CusRef  | CodeName | CodeValue     |  
| UserNo1 | MY_CODE1 | UserNo1_Code1 |  
| UserNo2 | MY_CODE2 | UserNo1_Code2 |  
| UserNo2 | MY_CODE1 | UserNo2_Code1 |  
| UserNo2 | MY_CODE2 | UserNo2_Code2 | 

There a number of other fields in that other table which I need to reference from other table but that is another issue.

我需要从其他表中引用其他表中的许多其他字段,但这是另一个问题。

What SQL could I use to make this first bit work?

我可以使用什么SQL来使第一个工作?

1 个解决方案

#1

Your desired results can be obtained with UNPIVOT

您可以通过UNPIVOT获得所需的结果

SELECT USER_NO As CusRef,
       CodeName,
       CodeValue     
FROM #YourTempTable
UNPIVOT (CodeValue FOR CodeName IN (MY_CODE1, MY_CODE2)) AS U