2

I have a table criteria whose structure is like this:

+----------+------+-------+
| criteria | user | total |
+----------+------+-------+
| xxxxxxxx | xx   | xxx   |
+----------+------+-------+

I have a problem to creating a query. I want make a pivot query from a single table, and I have this data:

+----------+------+-------+
| criteria | user | total |
+----------+------+-------+
|    F1    |  A1  |  17   |
|    F2    |  A1  |  9    |
|    F3    |  A1  |  6    |
|    F4    |  A1  |  4    |
|    F1    |  A2  |  19   |
|    F2    |  A2  |  6    |
|    F3    |  A2  |  2    |
|    F4    |  A2  |  8    |
+----------+------+-------+

and i want to make query pivot like this :

+----------+------+-------+------+-------+
| criteria | user | total | user | total |
+----------+------+-------+------+-------+
|    F1    |  A1  |  17   |  A2  |  19   |
|    F2    |  A1  |  9    |  A2  |  6    |
|    F3    |  A1  |  6    |  A2  |  2    |
|    F4    |  A1  |  4    |  A2  |  8    |
+----------+------+-------+------+-------+

Can somebody help me write a query to get the above result?

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360

1 Answers1

3

Your problem seems to be handled well by a self-join rather than a pivot. You can JOIN together the criteria table on itself using the criteria column:

SELECT c1.criteria AS criteria, c1.user AS user, c1.total AS total,
    c2.user AS user, c2.total AS total
FROM criteria c1 INNER JOIN criteria c2
ON c1.criteria = c2.criteria
WHERE c1.user < c2.user
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    here it is ... this works , but still there is no duplication of user where the ' A1 ' and ' A2 ' is still mixed in one column , is there a way to separate it ? so that the user ' A1 ' and ' A2 ' do not mix ? –  Oct 06 '15 at 07:20
  • I added a `WHERE` clause to show only distinct user pairs. – Tim Biegeleisen Oct 06 '15 at 07:22