SQL Server разворачивает несколько столбцов

Я пытаюсь повернуть таблицу вокруг множества столбцов, чтобы получить 3 столбца (поворот, имя столбца, значение)

так например:

name  |  age  |  gender
------+-------+---------
John  |   20  |    M
Jill  |   21  |    F

станет:

name | column | value
-----+--------+-------
John |  age   |   20
John | gender |   M
Jill |  age   |   21
Jill | gender |   F

Я немного погуглил, но не нашел похожей ситуации, особенно потому, что поворот, похоже, выполняется в противоположном направлении, чем то, что я пытаюсь выполнить.


sql sql-server unpivot
person mathematician    schedule 18.07.2014    source источник

Ответы (1)


arrow_upward
39
arrow_downward

Преобразование столбцов в строки называется UNPIVOT. Вы не указали, какую версию SQL Server вы используете, но есть несколько разных способов получить результат.

Вы можете использовать SELECT с UNION ALL:

SELECT name, 'age' as column, cast(age as varchar(10)) as value
FROM yourtable
UNION ALL
SELECT name, 'gender' as column, gender as value
FROM yourtable;

Если вы используете SQL Server 2005+, вы можете использовать функцию UNPIVOT:

SELECT name, column, age
FROM
(
  SELECT 
    name, 
    age = cast(age as varchar(10)), 
    gender
  FROM yourtable
) d
UNPIVOT
(
  value
  for column in (age, gender)
) unpiv;

Наконец, вместо функции UNPIVOT вы также можете использовать CROSS APPLY либо с VALUES (2008+), либо с UNION ALL:

SELECT name, column, age
FROM yourtable
CROSS APPLY
(
  VALUES
    ('age', cast(age as varchar(10)),
    ('gender', gender)
) c (column, value);

Любая из этих версий даст вам результат, который вы хотите. Вы заметите, что мне пришлось преобразовать столбец age в столбец varchar. Это связано с тем, что тип данных/длина (в unpivot) столбцов должны быть одинаковыми, поскольку в конечном результате вы будете преобразовывать их в один столбец.

person Taryn    schedule 18.07.2014
comment
При тестировании на сервере SQL 2008 хотелось бы отметить, что max_length также должно совпадать, поэтому, если Gender был char(1), его также нужно было преобразовать, чтобы произошло разворот. Однако с внешним приложением не пробовал. - person Jaaz Cole; 18.07.2014
comment
так что вы говорите, что в моих поисках того, как использовать поворот, так как я нашел только противоположные приложения, я должен был взглянуть на UNpivot... гениально!! : ) спасибо! - person mathematician; 18.07.2014
comment
@JaazCole Только UNPIVOT требует, чтобы тип данных был одинаковой длины — см. это на DBA.SE - person Taryn; 18.07.2014
comment
этот CROSS APPLY трюк потрясающий. на самом деле он кажется более гибким и IMO более читаемым, чем использование UNPIVOT - person jtate; 08.11.2019