SQL Server отключить несколько столбцов

Question

Я пытаюсь развернуть таблицу вокруг множества столбцов, чтобы получить 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

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

Answer 1

Преобразование столбцов в строки называется 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, Это связано с тем, что тип данных / длина (в непивотном направлении) столбцов должны быть одинаковыми, поскольку в конечном результате вы будете преобразовывать их в один столбец.

Answer 2

SELECT name, column, value
FROM (SELECT name, age, gender
FROM table) src
UNPIVOT (value FOR column IN (age, gender)) pvt

Answer 3

Вы можете легко сделать это с помощью предложения 'WITH' и сгенерированного вспомогательного столбца. В
первом cte вы создаете временный столбец, во втором вы его разворачиваете, а в третьем снова собираете. Вуаля!

Answer 4

      WITH cte1 AS (SELECT ROW_NUMBER() OVER (ORDER BY adl_id, ma_id) AS rk, 
adl_id, 
ma_id, 
ISNULL(CONVERT(CHAR,fosa_id),'') fosa_id,
ISNULL(CONVERT(CHAR,kdah_id),'') kdah_id,
ISNULL(CONVERT(CHAR,lb_id),'') lb_id,
ISNULL(CONVERT(CHAR,sprach_id),'') sprach_id
FROM tm_lieferant WHERE adl_id=1004)
,cte2 AS (SELECT rk, fieldname, [value] FROM (
SELECT rk, fosa_id, kdah_id, lb_id, sprach_id FROM cte1 ) p
UNPIVOT([value] FOR fieldname IN (fosa_id, kdah_id, lb_id, sprach_id
)) AS unpvt)
,cte3 AS (SELECT cte1.adl_id, cte1.ma_id, cte2.fieldname, cte2.value FROM cte2 INNER JOIN cte1 ON cte2.rk=cte1.rk)
SELECT * FROM cte3