SQL Server:将多个列转换为单个列

时间:2022-05-08 09:28:56

I have this

我有这个

name1   name2   name3   value1  value2  value3
  A       B       C        1       2       3

I really need this

我真的需要这个

Name    Value
  A       1
  B       2 
  C       3

Help me, please.

请帮帮我。

3 个解决方案

#1


1  

Use the below query..

使用以下查询..

  Select name1 Name,value1 Value from YourTable 
   Union all
    Select name2 ,value2 from YourTable 
     Union all
      Select name3 ,value3 from YourTable 

#2


1  

You can use union

你可以使用union

 select name1 as Name, value1 as Value from mytable
 union 
 select name2, value2 from mytable
 union 
 select name3, value3 from mytable

#3


0  

Temp Table Method in case union all performs slow or just to show an alternative....

临时表方法,如果联合所有执行缓慢或只是显示替代....

DECLARE @Table as TABLE (name1 CHAR(1), name2 CHAR(1), name3 CHAR(1), value1 INT, value2 INT, value3 INT)
INSERT INTO @Table VALUES ('A','B','C',1,2,3)

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
    BEGIN
        DROP TABLE #TempTable
    END

SELECT name1 as Name, value1 as Value
INTO #TempTable
FROM
    @Table

INSERT INTO #TempTable
SELECT name2, value2
FROM
    @Table

INSERT INTO #TempTable
SELECT name3, value3
FROM
    @Table

SELECT * 
FROm
    #TempTable

#1


1  

Use the below query..

使用以下查询..

  Select name1 Name,value1 Value from YourTable 
   Union all
    Select name2 ,value2 from YourTable 
     Union all
      Select name3 ,value3 from YourTable 

#2


1  

You can use union

你可以使用union

 select name1 as Name, value1 as Value from mytable
 union 
 select name2, value2 from mytable
 union 
 select name3, value3 from mytable

#3


0  

Temp Table Method in case union all performs slow or just to show an alternative....

临时表方法,如果联合所有执行缓慢或只是显示替代....

DECLARE @Table as TABLE (name1 CHAR(1), name2 CHAR(1), name3 CHAR(1), value1 INT, value2 INT, value3 INT)
INSERT INTO @Table VALUES ('A','B','C',1,2,3)

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
    BEGIN
        DROP TABLE #TempTable
    END

SELECT name1 as Name, value1 as Value
INTO #TempTable
FROM
    @Table

INSERT INTO #TempTable
SELECT name2, value2
FROM
    @Table

INSERT INTO #TempTable
SELECT name3, value3
FROM
    @Table

SELECT * 
FROm
    #TempTable