为具有多列数据的每个单元格创建一行 [英] Create a row for each cell that has data from multiple columns

问题描述

我正在处理从调查产生的数据，该调查的第一列具有唯一的受访者ID，然后有多个与受访者在寻找雇员方面所选择的国家/地区相关的列.所以我的桌子看起来像:

I'm dealing with data that has been generated from a survey that has a unique respondant ID as the first column and then has multiple columns relating to the choices of country that the respondent was looking at in relation to finding employees. So my table looks something like:

RespondentID  Andorra  Austria  Belgium  Cyprus  Denmark  Finland  France   

2546078180    Andorra  NULL     NULL     Cyprus  NULL     NULL     NULL 
2546077668    NULL     NULL     Belgium  NULL    NULL     NULL     NULL 
2546077120    NULL     NULL     NULL     NULL    Denmark  Finland  NULL 

我最后想要得到一张表，该表列出了每个给定答案的受访者ID.因此，根据以上数据，它看起来像:

What I want to end up with is a table that lists the Respondent ID for each answer given. So on the above data it would look like:

RespondentID  Country   

2546078180    Andorra
2546078180    Cyprus
2546077668    Belgium
2546077120    Denmark
2546077120    Finland   

因为这应该允许我创建一个表，详细列出受访者所链接的国家/地区，然后我可以将此表与其他答复(大多数是是/否或我们需要对数据进行报告的单个答案)一起加入

As this should allow me to create a table detailing the countries that a respondent is linked with and then I can join this table to the other responses which were mostly yes/no or single answers which we need to do reporting on the data.

这些数据是通过Excel电子表格输入的，因此，如果需要或更好的话，在导入SQL之前进行一些格式化也是可以接受的.

This data is coming in via an Excel spreadsheet so it would also be acceptable to do some formatting prior to the import into SQL if this is required or better.

推荐答案

使用 UNPIVOT 来标准化您的表:

Use UNPIVOT to normalize your table:

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

@source 是一个表，其中包含从Excel工作表导入的数据.

@source is a table that contains the data imported from your Excel worksheet.

测试数据:

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

这篇关于为具有多列数据的每个单元格创建一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！