MySQL - Turning 3 rows into 3 columns

Question

I have a single table that stores data weirdly - I cannot change the structure of the table or the database itself, I have to work with this data:

I need to turn this data:

NID        SID        CID        DATA
1000       10         1          This
1000       10         2          is
1000       10         3          data
1000       11         1          This
1000       11         2          is
1000       11         3          lore
1000       12         1          This
1000       12         2          is
1000       12         3          picard

Into this:

This      is      data
This      is      lore
This      is      picard

But I can't seem to figure out just how to join this stuff together, it's not clicking in my brain.

Can I have multiple SELECT statements to bring this together? Something like

SELECT data FROM submitted WHERE nid='1000' AND cid='1' AS col1
SELECT data FROM submitted WHERE nid='1000' AND cid='2' AS col2
SELECT data FROM submitted WHERE nid='1000' AND cid='3' AS col3

And then my PHP code could be as simple as

print ("<tr>
<td>$row[col1]</td>
<td>$row[col2]</td>
<td>$row[col3]</td>
</tr>");

I don't think MySQL accepts "AS" after a "WHERE". It's been years since I touched MySQL, so I'm super rusty, thanks for any help.

Answer 1

This should do the trick since GROUP_CONCAT ignores nulls unless they are the only values processed:

SELECT SID
, GROUP_CONCAT(IF(CID=1, DATA, NULL)) AS d1
, GROUP_CONCAT(IF(CID=2, DATA, NULL)) AS d2
, GROUP_CONCAT(IF(CID=3, DATA, NULL)) AS d3
FROM submitted
WHERE NID = 1000
GROUP BY SID
;

Note: Including SID in the results is entirely optional, and only based on my own personal compulsions. ;)

If you don't actually need them in separate result fields, this would be simpler:

SELECT SID, GROUP_CONCAT(DATA ORDER BY CID SEPARATOR ' ')
FROM submitted
WHERE NID = 1000
GROUP BY SID
;