为包含多列数据的每个单元格创建一行

使用 UNPIVOT 要规范化表,请执行以下操作:

SELECT u.RespondentID, u.Country
FROM @source
UNPIVOT (Country FOR c IN (Andorra, Austria, Belgium, Cyprus, Denmark, Finland, France)) u

@source 是包含从Excel工作表导入的数据的表。

测试数据:

DECLARE @source TABLE
(
    RespondentID BIGINT NOT NULL,
    Andorra VARCHAR(25),
    Austria VARCHAR(25),
    Belgium VARCHAR(25),
    Cyprus VARCHAR(25),
    Denmark VARCHAR(25),
    Finland VARCHAR(25),
    France VARCHAR(25)
)

INSERT INTO @source 
(RespondentID, Andorra,   Austria, Belgium,  Cyprus,   Denmark,    Finland,   France)
VALUES
(2546078180,   'Andorra', NULL,    NULL,     'Cyprus', NULL,       NULL,      NULL),
(2546077668,   NULL,      NULL,    'Belgium', NULL,    NULL,       NULL,      NULL),
(2546077120,   NULL,      NULL,    NULL,      NULL,    'Denkmark', 'Finland', NULL)

-- I assume that 'NULL' cell values from your Excel sheet become NULL during the import.

输出:

RespondentId         Country
-------------------- -------------------------
2546078180           Andorra
2546078180           Cyprus
2546077668           Belgium
2546077120           Denkmark
2546077120           Finland

UNION子句是正确的方法:

  SELECT * FROM (
    SELECT RespondentID, Field1 as Country
    FROM myTable
    UNION
    SELECT RespondentID, Field2 as Country
    FROM myTable
    UNION
    ....
    UNION
    SELECT RespondentID, Fieldn as Country
    FROM myTable) t
  WHERE Country IS NOT NULL