How to combine lines?

Question

I have the following table:

C1 | C2 | C3
---+----+---
X1 | Y1 | Z1
X1 | Y2 | Z2
X2 | Y3 | Z3
X2 | Y4 | Z4
X3 | Y5 | Z5
X3 | Y6 | Z6

and I would like to combine the lines to get:

C1 | C2 | C3 | C4 | C5 | C6 | C7 | C8 | C9
---+----+----+---+-----+----+----+----+---
X1 | Y1 | Z1 | X2 | Y3 | Z3 | X3 | Y5 | Z5
X1 | Y2 | Z2 | X2 | Y4 | Z4 | X3 | Y6 | Z6

Is there a reasonable and general way to do this? Otherwise, I think that I will assign a temp sequence number to each Xi, and then join them to maintain order.

My data model goes well with the layout of the three columns, but I need to output it 3 by 3 in CSV to another program.

Answer 1

Try this, you will enter the same table three times and must identify them by aliases.

SELECT MT1.value1 AS C1,MT1.value2 AS C2,MT1.value3 AS C3,
  MT2.value1 AS C4,MT2.value2 AS C5,MT2.value3 AS C6,
  MT3.value1 AS C7,MT3.value2 AS C8,MT3.value3 AS C9 FROM MY_TABLE MT1
LEFT JOIN MY_TABLE MT2 ON (MT1.id = MT2.id AND MT2.C1='X2')
LEFT JOIN MY_TABLE MT3 ON (MT2.id = MT3.id AND MT3.C1='X3')
WHERE MT1.C1='X1'

Think:

First you need to add columns on the left join. You will have 3 ^ 9 columns.

Then we add the conditions for the connection only where C1 / C2 / C3 is different (in X1, X2, X3)

, = 'X1'.

Answer 2

"" , , X Y:

select max(c1) as c1, max(c2) as c2, max(c3) as c3,
       max(c4) as c4, max(c5) as c5, max(c6) as c6,
       max(c7) as c7, max(c8) as c8, max(c9) as c9      
from ((select c1 as x, c2 as y, c1, c2, c3, NULL as c4, NULL as c5, NULL as c6, NULL as c7, NULL as c8, NULL as c9
       from table
       where c1 = 'x1'
      ) union all
      (select c1 as x, c2 as y, NULL, NULL, NULL, c1 as c4, c2 as c5, c3 as c6, NULL as c7, NULL as c8, NULL as c9
       from table
       where c1 = 'x2'
      ) union all
      (select c1 as x, c2 as y, NULL, NULL, NULL, NULL as c4, NULL as c5, NULL as c6, c1 as c7, c2 as c8, c3 as c9
       from table
       where c1 = 'x3'
      )
     ) t
group by x, y;

, , , () , col2 . , , SQL.