-1

Here is my issue. I would like to find duplicate 'code ifls' and get the different warehouses where I get this duplicated code

This is how the table looks like:

| code ifls | warehouse | 
|    4013   |        1  | 
|    4013   |        2  | 
|    4013   |        3  | 
|    4014   |        4  | 
|    4014   |        5  | 
|    4015   |        5  |

The result should look like this:

| code ifls | warehouse | warehouse | warehouse |
|    4013   |     1     |     2     |     3     | 
|    4014   |     4     |     5     |           |

I tried that request but without success...

SELECT code ifls as ifls, (SELECT warehouse FROM catalogue WHERE code ifls= ifls) 
FROM catalogue GROUP BY code ifls HAVING COUNT(code ifls) > 1

How would you express this in a SQL query?

Guibrid
  • 23
  • 4
  • http://stackoverflow.com/questions/36177816/combine-multiple-child-rows-into-one-row-mysql-without-hardcoding-or-min-max-val/36178292#36178292 – Zafar Malik Mar 25 '16 at 09:37
  • You can't easily have a dynamic number of columns like that. Is there a set maximum number of columns you need, or are you trying to generate columns as required? – Joachim Isaksson Mar 25 '16 at 09:41

3 Answers3

0

You can use a subselect and group_concat

select `code ifls`, group_concat(warehouse) 
from table  
where `code ifls` in (
    select `code ifls`, count(*)  
    from table 
    group by `code ifls`
    having count(*) > 1
)
group by `code ifls`
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
0

Here is your code-

SELECT `code ifls`, 
SUBSTRING_INDEX(GROUP_CONCAT(warehouse),',',1) AS warehouse_1, 
IF((LENGTH(GROUP_CONCAT(warehouse)) - LENGTH(GROUP_CONCAT(warehouse SEPARATOR ''))) < 1,'',SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(warehouse),',',2),',',-1)) AS warehouse_2, 
IF((LENGTH(GROUP_CONCAT(warehouse)) - LENGTH(GROUP_CONCAT(warehouse SEPARATOR ''))) < 2,'',SUBSTRING_INDEX(GROUP_CONCAT(warehouse),',',-1)) AS warehouse_3 
FROM `catalogue`
GROUP BY `code ifls`;
Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
0

GROUP_CONCAT() is probably the simplest way to do what you're trying to do without complicating things using dynamic columns;

SELECT `code ifls`, GROUP_CONCAT(warehouse) warehouses
FROM myTable
GROUP BY `code ifls`
HAVING COUNT(*) > 1

An SQLfiddle to test with.

It basically delivers a comma separated list of warehouses for each code ilfs, but the HAVING limits it to rows with more than one warehouse.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294