CODEWITHSUNDEEP
sql
Benoît
Benoît

Reputation: 3543

How do I combine rows?

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'd like to merge rows 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 smart and generic way to do it ? Otherwise, I think I'm going to assign temp sequence number to each Xi, and then join on them to preserve the ordering.

My data model is perfectly fine with the 3 columns layout, but I need to output it 3 by 3 in CSV to another program.

Upvotes: 4

Views: 80

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The only "generic" way I can think of requires knowing the X and Y values:

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;

In any particular database, there is probably a more generic solution, where (for instance) you don't have to guarantee that the values in col2 are different. However, this solution should work for the data as asked in the question and it is generic in the sense of being standard SQL.

Upvotes: 0

Grim
Grim

Reputation: 1986

Try this, You will join the same table three times and have to 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 the columns by left join. You will have 3^9 columns.

Then, add the conditions for joining only where the C1/C2/C3 differs (in X1,X2,X3)

Finally reduce the results to the first column='X1'.

Upvotes: 1

Related Questions