如何将一些列转换为其他列重复的行？

I have a some data that looks like this:

我有一些看起来像这样的数据：

Count   Id          Test1    Test2       Test3
2417    118006353   0050167  0050380     0050390    
1445    118016614   0070015  0070105                        
1426    118013670   0050167  0050380     0050390                    
978     118005505   0050995  0098392     0099359                    
757     118002779   0050167  
735     118001930   0050380  0050390     0050771                    
694     118005514   0050380  0050390                        
683     117972784   0050557
636     118017677   0056009  0097709                        
601     118008066   0070015

I need to convert it to look like this:

我需要将其转换为如下所示：

Count   Id          Test
2417    118006353   0050167
2417    118006353   0050380
2417    118006353   0050390
1445    118016614   0070015
1445    118016614   0070105
1426    118013670   0050167
1426    118013670   0050380
1426    118013670   0050390
978     118005505   0050995
978     118005505   0098392
978     118005505   0099359
757     118002779   0050167
735     118001930   0050380
735     118001930   0050390
735     118001930   0050771
694     118005514   0050380
694     118005514   0050390
683     117972784   0050557
636     118017677   0056009
636     118017677   0097709
601     118008066   0070015

Is this something I can do in excel? (Or any other tool?)

这是我在excel中可以做的事情吗？（或任何其他工具？）

5 个解决方案

#1

If you really only have the three columns (or some other reasonable number), I'd make three different sets of data: Count, ID, Test1, Count, ID, Test2 and Count, ID, Test3 (to make these, just copy and paste your original data and then delete two of the columns). Now copy and paste each of these three sets below each other. Then use this formula in column D:

如果你真的只有三列（或其他一些合理的数字），我会制作三组不同的数据：Count，ID，Test1，Count，ID，Test2和Count，ID，Test3（制作这些，只需复制并粘贴您的原始数据，然后删除其中两列）。现在将这三组中的每一组复制并粘贴到彼此之下。然后在D列中使用此公式：

=if(C1="",0,1)

Then just filter and delete all the rows where the value in column D is 0. Sort as you wish.

然后只需过滤并删除D列中的值为0的所有行。按您的意愿排序。

#2

You could try a SQL Query, something like this:

您可以尝试SQL查询，如下所示：

SELECT count,ID,Test1 as 'TEST' FROM myTABLE UNION
SELECT count,ID,Test2 FROM myTABLE UNION
SELECT count,ID,Test3 FROM myTABLE
ORDER BY count,ID,TEST

#3

Perhaps this? A union should get you the results in SQL Server...

也许这个？工会应该在SQL Server中获得结果...

SELECT [COUNT], ID, TEST1
FROM TABLE
UNION 
SELECT [COUNT], ID, TEST2
FROM TABLE
WHERE TEST2 IS NOT NULL
UNION
SELECT [COUNT], ID, TEST3
FROM TABLE
WHERE TEST3 IS NOT NULL

#4

You can use the UNPIVOT function in SQL Server to get the result:

您可以使用SQL Server中的UNPIVOT函数来获取结果：

select [count], id, test
from yourtable
unpivot
(
  test
  for col in (test1, test2, test3)
) un;

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

#5

SELECT *
FROM myTable
UNPIVOT (Test FOR TestX IN (Test1,Test2,Test3)) p

#1

=if(C1="",0,1)