-3

sql: I have a table like this:

+------+------+
|ID    |Result|
+------+------+
|1     |A     |
+------+------+
|2     |A     |
+------+------+
|3     |A     |
+------+------+
|1     |B     |
+------+------+
|2     |B     |
+------+------+

The output should be something like:

Output:

+------+-------+-------+
|ID    |Result1|Result2|
+------+-------+-------+
|1     |A      |B      |
+------+-------+-------+
|2     |A      |B      |
+------+-------+-------+
|3     |A      |       |
+------+-------+-------+

How can I do this?

Kemal Güler
  • 608
  • 1
  • 6
  • 21
swapan
  • 21
  • 2

3 Answers3

0
SELECT
    Id,
    MAX((CASE result WHEN 'A' THEN 'A' ELSE NULL END)) result1,
    MAX((CASE result WHEN 'B' THEN 'B' ELSE NULL END)) result2,
FROM 
    table1
GROUP BY Id

results

+------+-------+-------+
|Id    |Result1|Result2|
+------+-------+-------+
|1     |A      |B      |
|2     |A      |B      |
|3     |A      |NULL   |
+------+-------+-------+

run live demo on SQL fiddle: (http://sqlfiddle.com/#!9/e1081/2)

Accountant م
  • 6,975
  • 3
  • 41
  • 61
  • 1
    This would only work if the only two possible values are A and B - something which is _implied_ by the OP, though not confirmed. – Eli Jul 25 '17 at 20:45
0

there are a few ways to do it. None of tehm a are straight forward. in theory, a simple way would be to create 2 temporary tables, where you separte the data, all the "A" resultas in one table and "B" in another table.

Then get the results with simple query. using JOIN.

if you are allowed to use some scrpting on the process then it is simpler, other wise you need a more complex logic on your query. And for you query to alwasy work, you need to have some rules like, A table always contains more ids than B table.

If you post your real example, it is easier to get better answers.

docliving
  • 111
  • 1
  • 6
0

for this reason:

ID Name filename

1001 swapan 4566.jpg
1002 swapan 678.jpg
1003 karim 7688.jpg
1004 tarek 7889.jpg 1005 karim fdhak.jpg

output:

ID Name filename

1001 swapan 4566.jpg 678.jpg
1003 karim 7688.jpg fdhak.jpg 1004 tarek 7889.jpg ... .. ... ... ...

swapan
  • 21
  • 2
  • SO does not work like this. You have to update the question to provide that new sample data and inform us that the `filename` column might have *unpredictable* values – Accountant م Jul 25 '17 at 21:25
  • check this question ([https://stackoverflow.com/questions/15462753/mysql-join-multiple-rows-as-columns](https://stackoverflow.com/questions/15462753/mysql-join-multiple-rows-as-columns)) – Accountant م Jul 25 '17 at 22:08