将多列上的数据拆分为行（在SQL或SAS中）(Split data over multiple columns into rows (in SQL or SAS))

将多列上的数据拆分为行（在SQL或SAS中）(Split data over multiple columns into rows (in SQL or SAS))

我有以下形式的数据：

Country City1 City2 City3 AverageTemperature UK London Glasgow Manchester 15 Italy Rome Naples Venice 25 Germany Munich Berlin 20 USA New York 25

无论是SQL还是SAS数据步骤，我都想以这种形式获取数据：

Country City AverageTemperature UK London 15 UK Glasgow 15 UK Manchester 15 Italy Rome 25 Italy Naples 25 Italy Venice 25 Germany Munich 20 Germany Berlin 20 USA New York 25

这样我就可以跨越各行。 我已经考虑通过循环遍布城市不空白的三个城市列来实现这一目标，但我不确定如何自信地做到这一点 - 是否可以使用SQL或SAS轻松完成？ 只是一个指针将非常感激。

I have data in the following form:

Country City1 City2 City3 AverageTemperature UK London Glasgow Manchester 15 Italy Rome Naples Venice 25 Germany Munich Berlin 20 USA New York 25

With either SQL or a SAS data step, I would like to get the data in this form:

Country City AverageTemperature UK London 15 UK Glasgow 15 UK Manchester 15 Italy Rome 25 Italy Naples 25 Italy Venice 25 Germany Munich 20 Germany Berlin 20 USA New York 25

So that I have the data across individual rows. I have thought about doing this by looping over the three city columns where the city is not blank, but I'm not sure how to confidently do this - is it easily done with either SQL or SAS? Just a pointer would be greatly appreciated.

最满意答案

SELECT COUNTRY, City1, AverageTemperature FROM Table_Name UNION ALL SELECT COUNTRY, City2, AverageTemperature FROM Table_Name UNION ALL SELECT COUNTRY, City3, AverageTemperature FROM Table_Name

要获取City列不为null的行，您可以执行以下操作

SELECT COUNTRY, City1, AverageTemperature FROM Table_Name WHERE City1 IS NOT NULL UNION ALL SELECT COUNTRY, City2, AverageTemperature FROM Table_Name WHERE City2 IS NOT NULL UNION ALL SELECT COUNTRY, City3, AverageTemperature FROM Table_Name WHERE City3 IS NOT NULL SELECT COUNTRY, City1, AverageTemperature FROM Table_Name UNION ALL SELECT COUNTRY, City2, AverageTemperature FROM Table_Name UNION ALL SELECT COUNTRY, City3, AverageTemperature FROM Table_Name

To get rows where City column is not null you can do something like this

SELECT COUNTRY, City1, AverageTemperature FROM Table_Name WHERE City1 IS NOT NULL UNION ALL SELECT COUNTRY, City2, AverageTemperature FROM Table_Name WHERE City2 IS NOT NULL UNION ALL SELECT COUNTRY, City3, AverageTemperature FROM Table_Name WHERE City3 IS NOT NULL