MySQL DB with multiple numbers show as rows

Question

I have a rather complicated question (for me at least)

I have the following table structure

| id | roomtype | roomno | feature
| 1  |    STDK  |   11   |  BT
| 2  |    STDK  |   11   |  AE
| 3  |    STDK  |   22   |  SMK
| 4  |    STDK  |   22   |  BT
| 5  |    STDT  |   33   |  NONSMK

and I want to have an output like this

Type: STDK - RoomNo: 11 - Features: BT, AE
Type: STDK - RoomNo: 22 - Features: SMK, BT
Type: STDT - RoomNo: 33 - Features: NONSMK

It's probably not that complicated but I can't get it...

Mosty Mostacho · Accepted Answer · 2012-02-08 06:12:29Z

5

This shows the result you're looking for... But I don't like the idea of formatting the result in the query!

select
    concat("Type: ", roomtype,
    " - RoomNo: ", roomno,
    " - Features: ", group_concat(feature order by feature separator ', '))
    as result
from t1
group by roomtype, roomno

Here is a working example

answered Feb 8, 2012 at 6:12

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mu is too short Over a year ago

+1 for not liking the idea of all that formatting in SQL and for teaching me about sqlize.com

Mosty Mostacho Over a year ago

Thanks ^.^ There are some other useful links in my profile (I'm lazy, I don't even bookmark them) :P

mu is too short Over a year ago

I have a couple jsfiddle.net boiler plate setups in mine.

PrimuS Over a year ago

Great thanks, that's what I was looking for, although I think I'll make some changes, since I don't like the idea of SQL formatting as well. GROUP_CONCAT was what I was looking for, the rest will be done by the php... cheers and thanks

mu is too short · Accepted Answer · 2012-02-08 06:02:02Z

3

You could use MySQL's group_concat:

select roomtype, roomno, group_concat(feature order by feature separator ', ')
from your_table
group by roomtype, roomno
order by roomno

Producing your fully formatted output from that is left as an exercise.

answered Feb 8, 2012 at 6:02

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Comments

Ariful Islam · Accepted Answer · 2012-02-08 06:18:28Z

0

SELECT 
     roomtype AS Type, 
     roomno AS RoomNo, 
     GROUP_CONCAT(feature ORDER BY feature SEPARATOR ', ') AS Features
FROM 
    tableName
GROUP BY roomtype, roomno
ORDER BY roomno

answered Feb 8, 2012 at 6:18

Ariful Islam

7,6837 gold badges38 silver badges55 bronze badges

Collectives™ on Stack Overflow

MySQL DB with multiple numbers show as rows

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related