Как разбить два столбца на две строки по одному id

Рейтинг: 0Ответов: 1Опубликовано: 27.03.2023

[ 1

хочу чтобы таблица выглядела так: case_id 2112 id_cond 3183 case_id 2112 id cond 1935

sql
Источник: Stack Overflow на русском

Ответы

▲ 1

 1. Если только разложить по case_id и id_condition - UNPIVOT:

SELECT 
  un.[case_id],
  un.[condition_value] AS [id_cond]
FROM
  (
    SELECT 
      2712 AS [case_id],
      1711 AS [id_police],
      CONVERT([datetime], '2009-09-11', 120) AS [sign_date],
      CONVERT([datetime], '2009-09-11', 120) AS [sign_date2],
      3183 AS [id_condition],
      1935 AS [id_condition2]
  ) t
  UNPIVOT (
    [condition_value] FOR [condition_name]
    IN ([id_condition], [id_condition2])
  ) un
case_id id_cond
2712 3183
2712 1935

 2. Если надо разложить больше столбцов - CROSS APPLY:

SELECT 
  t.[case_id],
  t.[id_police],
  c.[sign_date],
  c.[condition]
FROM
  (
    SELECT 
      2712 AS [case_id],
      1711 AS [id_police],
      CONVERT([datetime], '2009-09-11', 120) AS [sign_date],
      CONVERT([datetime], '2009-09-11', 120) AS [sign_date2],
      3183 AS [id_condition],
      1935 AS [id_condition2]
  ) t
  CROSS APPLY (
    VALUES
      (t.[id_condition], t.[sign_date]),
      (t.[id_condition2], t.[sign_date2])
  ) c ([condition], [sign_date])
case_id id_police sign_date condition
2712 1711 2009-09-11 00:00:00.000 3183
2712 1711 2009-09-11 00:00:00.000 1935

UPDATE:

 2.1 Еще один вариант под CROSS APPLY:

SELECT 
  t.[case_id],
  t.[id_police],
  c.[sign_date],
  c.[condition]
FROM
  (
    SELECT 
      2712 AS [case_id],
      1711 AS [id_police],
      CONVERT([datetime], '2009-09-11', 120) AS [sign_date],
      CONVERT([datetime], '2009-09-11', 120) AS [sign_date2],
      3183 AS [id_condition],
      1935 AS [id_condition2]
  ) t
  CROSS APPLY (
    SELECT
      t.[id_condition] AS [condition], 
      t.[sign_date] AS [sign_date]
    UNION ALL
    SELECT
      t.[id_condition2] AS [condition], 
      t.[sign_date2] AS [sign_date]
  ) c