在sql server中将多个列组合成单个列 [英] combine multiple columns into single columns in sql server

问题描述

我有一张桌子，在那张桌子上有三列公司1，公司2，公司3。



以下是我的桌子数据：

i have one single table and in that table there are three columns company1,company2,company3.

below is my table data :

Company1    Company2	Company3
ABC		
Test1	    Test3	Test5
Test2	    Test4	Test6
		
		
testing	    testing2	

我希望将这些列合并使用如下序列号的单列：

and i want to combine these columns into single column with serial number like below :

SrNo    CompanyName
1       ABC		
2       Test1	    	
3       Test2	   
4       testing 
5       Test3
6       Test4
7       testing2	
8       Test5
9       Test6

任何帮助都将不胜感激。



提前感谢。

any help would be appreciated.

thanks in advance.

推荐答案

正如你在评论中发现的那样，UNION比UNPIVOT要容易得多



例如：

As you found out in the comments, UNION is much easier than UNPIVOT

For example:

SELECT SrNo, Field1
FROM table1
UNION 
SELECT SrNo, Field2
FROM table1
...

UNION ALL将为您提供重复项和UNION将为您提供唯一的SrNo，字段组合。

UNION ALL will give you duplicates and UNION will give you unique SrNo, field combinations.

您需要在三个单独的查询中选择company1 .. company3并使用UNION连接它们。那么你需要使用现有的序列号生成一个新的序列号。要保持行按顺序排列，以便将company1记录放在第一位，将company3记录放在最后，则需要再添加一个标识符作为优先级。然后你把它们都送到ROW_NUMBER（）就完成了。

You need to select the company1 .. company3 in three separate queries and concatenate those using UNION. then you need to generate a new sequence number using your existing sequence number. To keep the rows in order so company1 records come first and company3 records come last you need to add one more identifier for precedence. Then you feed both to ROW_NUMBER() and you are done.

WITH cte AS
(
    SELECT 1 AS SrNrGroup, SrNr, Company1 AS Company FROM yourtable WHERE Company1 IS NOT NULL
    UNION ALL
    SELECT 2 AS SrNrGroup, SrNr, Company2 FROM yourtable WHERE Company2 IS NOT NULL
    UNION ALL
    SELECT 3 AS SrNrGroup, SrNr, Company3 FROM yourtable WHERE Company3 IS NOT NULL
)
SELECT ROW_NUMBER() OVER(ORDER BY SrNrGroup, SrNr) AS SrNr, Company FROM cte

这篇关于在sql server中将多个列组合成单个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！