CODEWITHSUNDEEP
mysqlsqlmergeunpivot
kmitov
kmitov

Reputation: 1342

Merge values in sql rows

I am looking for ways to merge row values into one row where the column to merge is the same

Transform:

FK | F1 
========
3  | ABC    
3  | DEF

to

FK | F1    | F2
=================
3  | ABC   | DEF

Update: I initially don`t know the values of F1. They might be everything, but I know they are unique for a given FK and they are varchars.

Update 2: With your help I came to this query that will also add the FK for which there is only one value. I suppose it could be improved.

SELECT IFNULL(jointable.FK,table.FK) AS FK, IFNULL(jointable.F1,table.F1), jointable.F2 FROM table LEFT JOIN (SELECT T1.FK, T1.F1, T2.F1 AS F2 FROM table T1 LEFT JOIN table T2 ON T1.FK = T2.FK WHERE T1.F1 <> T2.F1 GROUP BY T1.FK ) as jointable ON table.FK=jointable.FK GROUP BY FK;

Upvotes: 1

Views: 114

Answers (2)

Moslem Ben Dhaou
Moslem Ben Dhaou

Reputation: 7005

Try this

SELECT FK
     , T1.F1
     , T2.F1 AS F2
FROM table T1
LEFT JOIN table T2 ON T1.FK = T2.FK AND T1.F1 <> T2.F1 --Criteria moved here

The LEFT JOIN is used since you mentioned that you have 1 or more values, which means the INNER JOIN could end up excluding rows.

The second criteria is to make sure you don't en up with rows like:

FK | F1    | F2
=================
3  | ABC   | ABC

Please be aware that in case of an OUTER JOIN (either LEFT or RIGHT) the join criteria is not the same as the filter criteria, and therefore I moved it above.

In SQL Server, you can use ROW_NUMBER() over FK, maybe with an ORDER BY.

In MySQL you might be able to use it with a GROUP BY as you mentioned in comments, I am not sure it will work (at least not in SQL Server without an aggregate function or a CTE).

Here is a live test: http://ideone.com/Bu5aae

Upvotes: 2

Goon10
Goon10

Reputation: 150

A suggestion:

SELECT FK, CONCAT(T1.F1,'',T2.F1) AS Result
FROM table T1, table T2
WHERE T1.FK = T2.FK

Upvotes: 0

Related Questions