Transform M:M-relation to flat Pivot Table in SQL level
In this page I describe my liked way to transform M:M relation to flat table. This way is efficient as possible, because Sql server make transformation M;M relation to flat table. Disadvantage of this method is low flexibility, this means if some data changed - you must rebuild all software from SQL level to application level. So, this method is applicable if data in referenced table will change very rare during software lifetime.
This is example of this approach https://github.com/Alex-1367/Transform-M2M-relation-to-flat-Pivot-Table-into-SQL.
So, we have table Hotels
Each Hotel can has one room, double room, twin room and triple room. And can propose various type of food supports.
To transform this structure to flat pivot table.
We need to create this view.
1: CREATE VIEW `hotelinfo` AS
2: SELECT `h`.`HotelID` AS `HotelID`,
3: `h`.`HotelName` AS `HotelName`,
4: `h`.`Address` AS `Address`,
5: `h`.`Phone` AS `Phone`,
6: `h`.`Email` AS `Email`,
7: `h`.`ContactPerson` AS `ContactPerson`,
8: `t`.`TownName` AS `TownName`,
9: `t`.`TownID` AS `TownID`,
10: `ho`.`Name` AS `OrganizerName`,
11: `ho`.`organizerId` AS `organizerId`,
12: max((CASE
13: WHEN (`hrt`.`room_type` = 'SNGL') THEN coalesce(`hrt2`.`Capacity`, 0)
14: ELSE 0
15: END)) AS `SNGL_Rooms`,
16: max((CASE
17: WHEN (`hrt`.`room_type` = 'SNGL') THEN `hrt`.`roomID`
18: ELSE NULL
19: END)) AS `SNGL_RoomTypeID`,
20: max((CASE
21: WHEN (`hrt`.`room_type` = 'DBL') THEN coalesce(`hrt2`.`Capacity`, 0)
22: ELSE 0
23: END)) AS `DBL_Rooms`,
24: max((CASE
25: WHEN (`hrt`.`room_type` = 'DBL') THEN `hrt`.`roomID`
26: ELSE NULL
27: END)) AS `DBL_RoomTypeID`,
28: max((CASE
29: WHEN (`hrt`.`room_type` = 'TWIN') THEN coalesce(`hrt2`.`Capacity`, 0)
30: ELSE 0
31: END)) AS `TWIN_Rooms`,
32: max((CASE
33: WHEN (`hrt`.`room_type` = 'TWIN') THEN `hrt`.`roomID`
34: ELSE NULL
35: END)) AS `TWIN_RoomTypeID`,
36: max((CASE
37: WHEN (`hrt`.`room_type` = 'TRPL') THEN coalesce(`hrt2`.`Capacity`, 0)
38: ELSE 0
39: END)) AS `TRPL_Rooms`,
40: max((CASE
41: WHEN (`hrt`.`room_type` = 'TRPL') THEN `hrt`.`roomID`
42: ELSE NULL
43: END)) AS `TRPL_RoomTypeID`,
44: max((CASE
45: WHEN (`hmt`.`meal_type` = 'Breakfast') THEN 1
46: ELSE 0
47: END)) AS `Breakfast_Available`,
48: max((CASE
49: WHEN (`hmt`.`meal_type` = 'Breakfast') THEN `hmt`.`mealID`
50: ELSE NULL
51: END)) AS `Breakfast_MealTypeID`,
52: max((CASE
53: WHEN (`hmt`.`meal_type` = 'Lunch') THEN 1
54: ELSE 0
55: END)) AS `Lunch_Available`,
56: max((CASE
57: WHEN (`hmt`.`meal_type` = 'Lunch') THEN `hmt`.`mealID`
58: ELSE NULL
59: END)) AS `Lunch_MealTypeID`,
60: max((CASE
61: WHEN (`hmt`.`meal_type` = 'Dinner') THEN 1
62: ELSE 0
63: END)) AS `Dinner_Available`,
64: max((CASE
65: WHEN (`hmt`.`meal_type` = 'Dinner') THEN `hmt`.`mealID`
66: ELSE NULL
67: END)) AS `Dinner_MealTypeID`,
68: max((CASE
69: WHEN (`hmt`.`meal_type` = 'ALL INC') THEN 1
70: ELSE 0
71: END)) AS `ALL_INC_Available`,
72: max((CASE
73: WHEN (`hmt`.`meal_type` = 'ALL INC') THEN `hmt`.`mealID`
74: ELSE NULL
75: END)) AS `ALL_INC_MealTypeID`,
76: count(DISTINCT `hrt2`.`RoomTypeID`) AS `TotalRoomTypes`,
77: count(DISTINCT `hmt2`.`MealTypeID`) AS `TotalMealTypes`,
78: sum(`hrt2`.`Capacity`) AS `TotalCapacity`,
79: `h`.`CreatedAt` AS `CreatedAt`,
80: `h`.`UpdatedAt` AS `UpdatedAt`,
81: `h`.`IsActive` AS `IsActive`
82: FROM ((((((`hotels` `h`
83: LEFT JOIN `towns` `t` on((`h`.`ToTown` = `t`.`TownID`)))
84: LEFT JOIN `hotelorganizer` `ho` on((`h`.`ToHotelOrganizer` = `ho`.`organizerId`)))
85: LEFT JOIN `hotel_room_types` `hrt2` on((`h`.`HotelID` = `hrt2`.`HotelID`)))
86: LEFT JOIN `hotelroomtype` `hrt` on((`hrt2`.`RoomTypeID` = `hrt`.`roomID`)))
87: LEFT JOIN `hotel_meal_types` `hmt2` on((`h`.`HotelID` = `hmt2`.`HotelID`)))
88: LEFT JOIN `hotelmealtype` `hmt` on((`hmt2`.`MealTypeID` = `hmt`.`mealID`)))
89: GROUP BY `h`.`HotelID`,
90: `h`.`HotelName`,
91: `h`.`Address`,
92: `h`.`Phone`,
93: `h`.`Email`,
94: `h`.`ContactPerson`,
95: `t`.`TownName`,
96: `t`.`TownID`,
97: `ho`.`Name`,
98: `ho`.`organizerId`,
99: `h`.`CreatedAt`,
100: `h`.`UpdatedAt`,
101: `h`.`IsActive
Sql context:
Comments (
)
)
Link to this page:
http://www.vb-net.com/PivotTable/Index.htm
|
|