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

Question

[

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

Answer 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

БЛОГ НА HUSL

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

Ответы (1 шт):

UPDATE: