Создайте строку для каждой ячейки, содержащей данные из нескольких столбцов.

Я имею дело с данными, которые были сгенерированы из опроса, который имеет уникальный идентификатор респондента в качестве первого столбца, а затем несколько столбцов, относящихся к выбору страны, на которую респондент смотрел в связи с поиском сотрудников. Итак, моя таблица выглядит примерно так:

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

В итоге я хочу получить таблицу, в которой перечислены идентификаторы респондентов для каждого данного ответа. Таким образом, на приведенных выше данных это будет выглядеть так:

RespondentID  Country   

2546078180    Andorra
2546078180    Cyprus
2546077668    Belgium
2546077120    Denmark
2546077120    Finland

Поскольку это должно позволить мне создать таблицу с подробным описанием стран, с которыми связан респондент, а затем я могу присоединить эту таблицу к другим ответам, которые в основном были да/нет или отдельные ответы, которые нам нужны для отчетности по данным.

Эти данные поступают через электронную таблицу Excel, поэтому было бы приемлемо выполнить некоторое форматирование перед импортом в SQL, если это необходимо или лучше.


sql tsql excel unpivot normalization
person user3318535    schedule 05.09.2014    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Используйте 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
person stakx - no longer contributing    schedule 05.09.2014
comment
Спасибо, тоже присмотрюсь к этому решению. :) - person user3318535; 05.09.2014
comment
Это также очень хорошее решение, я никогда раньше не использовал unpivot, поэтому, возможно, вы можете помочь мне с быстрым вопросом? Некоторые ответы в столбцах страны исходных данных различаются по языку, например, в Германии может быть Германия или Германия. Есть ли способ преобразовать все, что не является нулевым значением в столбце, в определенное значение одновременно с этим? - person user3318535; 05.09.2014
comment
@ user3318535: Да, это просто: вместо SELECT значения ячейки (u.Country) вы можете SELECT имя столбца ячейки (u.c): SELECT u.RespondentID, u.c …. (Объяснение: часть Country FOR c предложения UNPIVOT по существу означает, что c получает имя столбца ячейки, поэтому за ним следует IN (…) перечисление имен столбцов--, а Country получает значение ячейки. Поскольку имена ваших столбцов находятся в фиксированный язык, вы можете просто выбрать их вместо самих значений ячеек.) - person stakx - no longer contributing; 05.09.2014
comment
@ user3318535: Альтернативное решение — создать дополнительную таблицу поиска lookup, содержащую два столбца OriginalValue и TranslatedValue и такие значения, как ('Germany', 'DE'), ('Deutschland', 'DE'), ('Allemagne', 'DE'), ('France', 'FR'), 'Francia', 'FR'), …. Затем JOIN в несводную таблицу ON u.Country = lookup.OriginalValue; наконец, SELECT …, lookup.TranslatedValue вместо u.Country. - person stakx - no longer contributing; 05.09.2014
comment
Genius, UC делает это для меня, хотя справочная таблица также является хорошим криком. Изменено, чтобы быть лучшим ответом. Бесконечно благодарен! - person user3318535; 08.09.2014
comment
@ user3318535: Добро пожаловать. Я тоже никогда раньше не пользовался UNPIVOT, но ваш вопрос показался мне прекрасной возможностью увидеть, как это работает… так что я тоже многому научился. :) - person stakx - no longer contributing; 08.09.2014

arrow_upward
0
arrow_downward

Предложение 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
person Bulat    schedule 05.09.2014
comment
Результат этого запроса также будет содержать строки для всех NULL ячеек. - person stakx - no longer contributing; 05.09.2014
comment
Ура! Спасибо, это было красиво и просто, и его легко воспроизвести на других столах. Может быть, последний вопрос, оказывается, никто не нанимает людей в Андорре, поэтому в этом конкретном случае этот столбец всегда имеет значение Null, и я думаю, что это возвращает значение Null для каждого респондента: ID страны 2500979822 NULL 2500979822 Deutschland 2501468008 NULL 2501468008 Deutschland 2501468008 Vereinigte Staaten von Amerika 2501486940 NULL 2501486940 Венгрия Есть ли способ отфильтровать нули на данном этапе? - person user3318535; 05.09.2014
comment
это можно исправить с помощью WHERE в запросе-оболочке - person Bulat; 05.09.2014
comment
Обновлен ответ, чтобы исключить NULL - person Bulat; 05.09.2014
comment
Я тупой, конечно можно! :) Думаю, я просто слишком долго смотрел на эти данные! - person user3318535; 05.09.2014