CODEWITHSUNDEEP
mysql
PPcool
PPcool

Reputation: 41

mysql merge two selects to interleave multiple rows

I apologize if the title is abstract.

Is there a way to interleave the null fields in different rows?

ex. original table:

enter image description here

Schema:

CREATE TABLE `test` (
`id` int(6),
`name` varchar(20),
`content` varchar(20)
) DEFAULT CHARSET=utf8;
INSERT INTO `test` (`id`, `name`, `content`) VALUES
('1', Null, Null),
(Null, '1', 'aaa'),
(Null, '2', 'bbb'),
('2', Null, Null),
(Null, '3', 'ccc'),
(Null, '4', 'ddd'),
('3', Null, Null),
(Null, '5', 'eee'),
(Null, '6', 'fff');

Desired result:

enter image description here Thank you for your time and suggestions.

Upvotes: 0

Views: 104

Answers (2)

Wrokar
Wrokar

Reputation: 963

There is no guarantee that the rows in the database are ordered how you want them, so this may be impossible unless there is some way to link the values of the name/content columns to the id column.

If each id will only be associated with two rows, and the names of the tests are simply sequential numbers, it would be possible to map the name to the id and join them. (Join id 1 to name 1 and 2, id 2 to name 3 and 4, etc):

SELECT a.id, b.name, b.content
FROM test a
JOIN test b
ON a.id = CEILING(b.name / 2)

I suspect this isn't the case, so ultimately it is unlikely you can do this select without finding someway to relate the id and the other two columns.

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15961

There is not really a good way to do what you are suggesting. Rows have no inherent order; the interleaving you are suggesting assumes an order that just doesn't exist.

Now, if 'test X row Y' always belongs with id = X, that is a different matter...

something like this should work

SELECT tB.id, tA.name, tA.content
FROM t AS tA
INNER JOIN t AS tB ON tA.content LIKE CONCAT('test ', tB.id, ' %')
WHERE tA.id IS NULL
;

Alternatively, you can get similar results without a join, using string functions to extract the id from the content.

Upvotes: 1

Related Questions