-1

Say I have a table 'alphabet'. This is just a basic representation/example. 

id    word
1     a
2     b
3     c
4     d
5     e
6     f
7     g
8     h
9     i
10    j
11    k
12    l
13    m

Now assume I am restricted to just a single query (with subqueries) due to a language restriction or otherwise.

I want my 'result' to be as follows: 

row   col1   col2   col3
1     a      b      c
2     d      e      f  
3     g      h      i
4     j      k      l
5     m

Now I've gotten somewhat close to this by emulating a Full Outer Join in MySQL by following the instructions found here: Full Outer Join in MySQL combined with a sub-query on the same table using something along the lines of:

SELECT id,word FROM table WHERE MOD(id,3)=1

This isn't particularly perfect, as it requires me to assume that the ids follow each-other perfectly sequentially, but I haven't been able to think of a better method at the time. Since last I recall, LIMIT and OFFSET do not take sub-queries.

However, following this thought through, results into something along the lines of: 

row   col1   col2   col3
1     a
2     d
3     g
4     j
5     m
6            b
7            e
8            h
9            k
10                  c
11                  f
12                  i
13                  l
13                  m

Is there a way to get my desired format? And note that normally, the desired way to do this is indeed to just do three calls with a limit-offset call based on a count(). But /is this possible/ to be done in a single call?

Community
  • 1
  • 1
Mercutio
  • 43
  • 3
  • I cannot think of a situation where this could be useful !!?!?! – Strawberry May 18 '14 at 17:41
  • @Strawberry I am working with a niche and limited language. And while I CAN grab a COUNT() ahead of time and feed it as an argument, I am using an equivalent of a function language's MAP() to form answers. The code in said language gets more messy if I want to feed an argument other than the straight SQL into it. That aside, you can see it as academic exercise. – Mercutio May 18 '14 at 18:50

4 Answers4

1

Is this what you need?

SELECT FLOOR((col1.id - 1) / 3 + 1) AS id, col1.word AS col1, col2.word AS col2, col3.word AS col3
FROM alphabet col1 
    LEFT JOIN alphabet col2 ON col1.id = col2.id - 1
    LEFT JOIN alphabet col3 ON col2.id = col3.id - 1
WHERE col1.id % 3 = 1;
jeojavi
  • 876
  • 1
  • 6
  • 15
  • That results in roughly: 1 a b c 2 d e f 3 g h i 4 j k l As you can tell, 'm' is missing. – Mercutio May 18 '14 at 17:48
  • @Mercutio It works perfectly for me.. did you notice my edits? – jeojavi May 18 '14 at 18:19
  • I did not in fact, notice the edits. My apologies. This indeed works! The row numbers are not correct in the case of a non-sequential ID, but that is but a minor detail. – Mercutio May 18 '14 at 18:29
  • @Mercutio, what about performance? Imagine, you want to break down to 10 - 50 columns. – Hamlet Hakobyan May 18 '14 at 18:32
  • @Mercutio don't worry :) but if you finally found the correct solution by yourself you can set your own answer as correct – jeojavi May 18 '14 at 18:34
  • @HamletHakobyan you're right this answer is not the optimal one – jeojavi May 18 '14 at 18:37
  • @HamletHakobyan (and Javi) Could you explain in basic terms, how your two solutions relate to eachother in terms of performance? That is unfortunately an area of Databases I am not too proficient with. I presume that Hamlet's is a more linear search, where-as Javi's spends additional time to do the joins? – Mercutio May 18 '14 at 18:46
  • 1
    I think in this example performance is not important, but if your alphabet is bigger and you need a higher number of columns, @HamletHakobyan's solution is far better, because no joins are needed. – jeojavi May 18 '14 at 18:49
  • I indeed have a far larger database. The example was simply there to get a basic idea across and sift out any unnecessary information. In this case, I'll return to marking Hamlet's answer as the accepted answer. Thank you very much Javi. (Besides, I can't mark mine as accepted for another 2 days, and I feel it'd be bad form not to give Hamlet his due credit). – Mercutio May 18 '14 at 18:52
  • @Mercutio that's fine with me :) – jeojavi May 18 '14 at 18:59
1

I doesn't found any use case for this, but it is what you want:

SELECT
  FLOOR((id - 1)/3) + 1 id,
  MAX(CASE WHEN MOD(id - 1,3) = 0 THEN word END) col1,
  MAX(CASE WHEN MOD(id - 1,3) = 1 THEN word END) col2,
  MAX(CASE WHEN MOD(id - 1,3) = 2 THEN word END) col3
FROM tbl
GROUP BY FLOOR((id - 1)/3)

SQLFIDDLE DEMO

Notice, that this will work only in case when you have sequential Id starting from 1.

Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68
  • This is the closest answer so far. But as you say, Hamlet, this runs into the problem of sequential IDs. – Mercutio May 18 '14 at 18:07
  • 2
    @Mercutio This is **exact** answer for question stated in OP. But I predicted the problem and stated it in my answer. As a fast fix you can number the rows in letter order (or any) and use this query. – Hamlet Hakobyan May 18 '14 at 18:12
  • Thank you to anonymous down-voters. They always make me smile. – Hamlet Hakobyan May 18 '14 at 18:31
  • @HamletHakobyan my fault, please accept my apologies, my intention was to upvote. If you edit the answer I can repair the error. – jeojavi May 18 '14 at 19:09
0

How about something like 

Select t1.id as `row`, t1.word as col1, t2.word as col2, t3.word as col3
From alphabet t1
left join alphabet t2 on t2.id = t1.id + 5
left join alphabet t3 on t3.id = t1.id + 10 
Where t1.id <= 5
Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39
  • While this indeed would solve the problem, it requires me knowing the COUNT() of table ahead of time (thus making it two calls). But I'll play with this for a bit and see if this can go somewhere. And it still runs into the issue I outlined, which is that for anything resembling a decent output, it requires the IDs to be sequential (no gaps/deletions to be present) – Mercutio May 18 '14 at 17:55
0

Taking Halmet Hakobyan's answer, finishing this off:

SELECT
FLOOR((rank - 1)/3) + 1 rank,
  MAX(CASE WHEN MOD(rank - 1,3) = 0 THEN word END) col1,
  MAX(CASE WHEN MOD(rank - 1,3) = 1 THEN word END) col2,
  MAX(CASE WHEN MOD(rank - 1,3) = 2 THEN word END) col3
FROM (SELECT @rn:=@rn+1 AS rank, `id`,`word` from tbl) as tbl, (SELECT @rn:=0) t2
GROUP BY FLOOR((rank - 1)/3)

SQLFIDDLE DEMO

This will work even if the ids are not in sequence.

Mercutio
  • 43
  • 3