CODEWITHSUNDEEP
mysqlsqlpivot-tablemariadb
Zhamry D. Jones
Zhamry D. Jones

Reputation: 71

How to combine multiple rows into one row

i want to combine multiple rows into one row. the table type is dynamic

Table Room

room_id | room_name
-------------------
1       | room_A
2       | room_B
3       | room_C

Table Type

type_id | type_name
-------------------
1       | type_I
2       | type_II
3       | type_II
4       | type_IV

table Price

price_id | room_id | type_id | price
------------------------------------
1        |    1    |    1    | 100
2        |    1    |    2    | 150
3        |    1    |    3    | 200
4        |    1    |    4    | 250
5        |    2    |    1    | 100
6        |    2    |    2    | 200
7        |    2    |    3    | 300
8        |    2    |    4    | 400
9        |    3    |    1    | 150
10       |    3    |    2    | 250
11       |    3    |    3    | 350
12       |    3    |    4    | 450

what i want is something like this

       |                   price
room   |---------------------------------------
       | type_I | type_II | type_III | type_IV 
-----------------------------------------------
room_A |   100  |   150   |   200    |   250
room_B |   100  |   200   |   300    |   400
room_C |   150  |   250   |   350    |   450

Upvotes: 1

Views: 141

Answers (1)

DineshDB
DineshDB

Reputation: 6193

Try this Query, Hope this helps you:

SELECT TR.Room_Name
    ,MAX(CASE WHEN P.Type_ID=1 THEN Price END)type_I
    ,MAX(CASE WHEN P.Type_ID=2 THEN Price END)type_II
    ,MAX(CASE WHEN P.Type_ID=3 THEN Price END)type_III
    ,MAX(CASE WHEN P.Type_ID=4 THEN Price END)type_IV
FROM TABLE_PRICE P
INNER JOIN TABLE_ROOM TR ON TR.Room_ID=P.Room_ID
INNER JOIN TABLE_TYPE TT ON TT.Type_ID=P.Type_ID
GROUP BY TR.Room_Name

Upvotes: 1

Related Questions