0

While this is not a huge deal, I'm just wondering if this is possible (I'm using MySQL in case that matters).

I have a query that returns something like this:

Basket      Contents
basket1     apple
basket1     orange
basket1     pear
basket2     lemon
basket2     banana
basket2     lime

Is it possible using only SQL to get this output?

Basket      Contents
basket1     apple
            orange
            pear
basket2     lemon
            banana
            lime

In other words, is it possible to list the column entry only once for each entry? Thank you.

  • you can use group concat for this.ex:select column1,group_concat(column2) from table group by column1; – Ravi Kumar Mar 8 '16 at 3:09
  • Some things are easier to do in a client language than in SQL. – Rick James Mar 8 '16 at 5:13
  • Can you explain what this requirement is for? I'm suspicious that you're trying to work around something where there might be a better way. – Phil Sumner Mar 8 '16 at 9:53
  • I agree with Rick, this is presentation and should be dealt with outside the database. – Lennart Mar 8 '16 at 11:52
1

I would say this is easier on the representational layer but if you want it in pure MySQL this will do it:

set @current='';
select show_basket, content 
from (
    select case 
        when @current = basket 
            then '' 
            else basket 
        end as show_basket, 
        content, @current:=basket 
    from test_131547
    order by basket
) a;

Output:

+-------------+---------+
| show_basket | content |
+-------------+---------+
| 1           | lemon   |
|             | lime    |
|             | apple   |
|             | banana  |
| 2           | kiwi    |
|             | apple   |
|             | orange  |
+-------------+---------+
0

I didn't test this code but something like this might work. Essentially the idea is getting a count of the occurrences of each basket "Basket" field. If it is 1 then show basket, if it has been seen before, display an empty string.

SELECT basket, 
       contents 
FROM   (SELECT IF(num <= 1, basket, "")                 AS Basket, 
               contents                                AS Contents, 
               (SELECT Count(*) 
                FROM   tbl1 t2 
                WHERE  t2.basket = t1.basket
                       ) AS num 
        FROM   tbl1 t1) AS tmp; 
0

The row number over partition numbers the basket groups starting at 1. Anything other than 1 would be a duplicate basket name and therefore display null.

 SELECT  CASE WHEN ROW_NUMBER() OVER ( PARTITION BY t.Basket ORDER BY t.Basket ) <> 1
             THEN NULL
             ELSE t.Basket
        END Basket ,
        t.Contents
 FROM    MyTable AS t;
  • MySQL doesn't support row_number() – Julien Vavasseur Mar 8 '16 at 16:11
  • @Julien .... yet ;) – ypercubeᵀᴹ Mar 8 '16 at 16:12
  • Ok, i tried that query against a linked mysql database and it worked so I assumed it was supported. – Nic Carreira Mar 8 '16 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.