I have SQL server data Table, like this:

I'd like to obtain this:

That should be an "easy" pivot, but I can't figure it out. How could a SQL query be written?

*EDIT

Excuse me, I have neglected an important detail. The number of rows in the table is very large, some 500.000 so the IDs are very large.

CodePudding user response：

You could do it as a self join - use row number to have another column that is either 1 or 2 (depending on how many times ID appeared), 1 should always appear, 2 sometimes appears, so left joining those rows with 2 onto those rows with 1 (based on the id) gives you the end result..

WITH x AS(
  SELECT
    t.ID,
    t.SYMPTOM1,
    t.SYMPTOM2,
    ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.SYMPTOM1) as rn
  FROM t
)

SELECT
  *
FROM
  x x1
  LEFT JOIN x x2 ON x1.id = x2.id AND x1.rn = 1 AND x2.rn = 2

CodePudding user response：

You can pivot on a row-number

SELECT
  t.ID,
  SYMPTOM1 = MAX(CASE WHEN t.rn = 1 THEN t.SYMPTOM1 END),
  SYMPTOM2 = MAX(CASE WHEN t.rn = 1 THEN t.SYMPTOM2 END),
  SYMPTOM3 = MAX(CASE WHEN t.rn = 2 THEN t.SYMPTOM1 END),
  SYMPTOM4 = MAX(CASE WHEN t.rn = 2 THEN t.SYMPTOM2 END)
FROM (
    SELECT
      t.*,
      rn = ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY (SELECT 1))
    FROM YourTable t
) t
GROUP BY t.ID;