SQL Challenge concatenate rows to the end of every group

Question

I have the following table and I need to flatten the data.

From here

table 1

id      val1   val2   ....valn 
-----------------------------------
1         a      z
1         b      x
1         c      v 
2         a      w
2         b      q 
..n

To here

id      val1_1   val2_1   ....valn_1   val1_2  val2_2 ... valn_2  ....  val1_n...valn_n 
-----------------------------------
1         a      z                        b       x
2         a      w                        b       q 
..n

Any ideas?

I have done something using cursors, however it is a little nasty and It has some bugs. Pivot?

Answer 1

Yes you need to a pivot, but you also need to calculate a row-number to pivot against. However, given this is a multi-column pivot, it's probably easier to just use conditional aggregation.

I suggest you pick a proper ordering for the row-number

SELECT
  id,
  val1_1 = MAX(CASE WHEN t.rn = 1 THEN t.val1 END),
  val2_1 = MAX(CASE WHEN t.rn = 1 THEN t.val2 END),
  val3_1 = MAX(CASE WHEN t.rn = 1 THEN t.val3 END),
--  ...........
  val1_2 = MAX(CASE WHEN t.rn = 2 THEN t.val1 END),
  val2_2 = MAX(CASE WHEN t.rn = 2 THEN t.val2 END),
  val3_2 = MAX(CASE WHEN t.rn = 2 THEN t.val3 END),
--  ...........
FROM (
    SELECT *,
        rn = ROW_NUMBER() OVER (PARTITION BY t.rn ORDER BY (SELECT 1))
    FROM table1 t
) t
GROUP BY t.id;