Concat rows to string in SQL level using MySQL GROUP_CONCAT or MsSQL STRING_AGG. My implementation of Working Time management system.
This is my Working Time management system for various organizations like restaurants, museum and so on https://github.com/Alex-1367/WorkingTimeManagement-ConcatString
We can assembly a number of parts of working time to one string like this.
And than show Working time to user:
Grouping rows in MySQL make with GROUP_CONCAT:
1: CREATE VIEW `timeinfo` AS
2: SELECT `te`.`ScheduleID` AS `ScheduleID`,
3: `te`.`EntityTypeID` AS `EntityTypeID`,
4: `tet`.`EntityName` AS `EntityTypeName`,
5: `te`.`EntityID` AS `EntityID`,
6: `te`.`ScheduleName` AS `ScheduleName`,
7: group_concat((CASE
8: WHEN (`ts`.`DayOfWeek` = 1) THEN if(`ts`.`IsClosed`, 'Closed', concat(time_format(`ts`.`OpenTime`, '`H:`i'), '-', time_format(`ts`.`CloseTime`, '`H:`i')))
9: END)
10: ORDER BY `ts`.`OpenTime` ASC separator ', ') AS `Monday`,
11: group_concat((CASE
12: WHEN (`ts`.`DayOfWeek` = 2) THEN if(`ts`.`IsClosed`, 'Closed', concat(time_format(`ts`.`OpenTime`, '`H:`i'), '-', time_format(`ts`.`CloseTime`, '`H:`i')))
13: END)
14: ORDER BY `ts`.`OpenTime` ASC separator ', ') AS `Tuesday`,
15: group_concat((CASE
16: WHEN (`ts`.`DayOfWeek` = 3) THEN if(`ts`.`IsClosed`, 'Closed', concat(time_format(`ts`.`OpenTime`, '`H:`i'), '-', time_format(`ts`.`CloseTime`, '`H:`i')))
17: END)
18: ORDER BY `ts`.`OpenTime` ASC separator ', ') AS `Wednesday`,
19: group_concat((CASE
20: WHEN (`ts`.`DayOfWeek` = 4) THEN if(`ts`.`IsClosed`, 'Closed', concat(time_format(`ts`.`OpenTime`, '`H:`i'), '-', time_format(`ts`.`CloseTime`, '`H:`i')))
21: END)
22: ORDER BY `ts`.`OpenTime` ASC separator ', ') AS `Thursday`,
23: group_concat((CASE
24: WHEN (`ts`.`DayOfWeek` = 5) THEN if(`ts`.`IsClosed`, 'Closed', concat(time_format(`ts`.`OpenTime`, '`H:`i'), '-', time_format(`ts`.`CloseTime`, '`H:`i')))
25: END)
26: ORDER BY `ts`.`OpenTime` ASC separator ', ') AS `Friday`,
27: group_concat((CASE
28: WHEN (`ts`.`DayOfWeek` = 6) THEN if(`ts`.`IsClosed`, 'Closed', concat(time_format(`ts`.`OpenTime`, '`H:`i'), '-', time_format(`ts`.`CloseTime`, '`H:`i')))
29: END)
30: ORDER BY `ts`.`OpenTime` ASC separator ', ') AS `Saturday`,
31: group_concat((CASE
32: WHEN (`ts`.`DayOfWeek` = 7) THEN if(`ts`.`IsClosed`, 'Closed', concat(time_format(`ts`.`OpenTime`, '`H:`i'), '-', time_format(`ts`.`CloseTime`, '`H:`i')))
33: END)
34: ORDER BY `ts`.`OpenTime` ASC separator ', ') AS `Sunday`,
35: group_concat(concat(date_format(`tss`.`SpecialDate`, '`Y-`m-`d'), ': ', if(`tss`.`IsClosed`, 'Closed', concat(time_format(`tss`.`OpenTime`, '`H:`i'), '-', time_format(`tss`.`CloseTime`, '`H:`i'))))
36: ORDER BY `tss`.`SpecialDate` ASC separator '; ') AS `SpecialDates`,
37: group_concat(if((`tss`.`Description` IS NOT NULL),concat(date_format(`tss`.`SpecialDate`, '`Y-`m-`d'), ': ', `tss`.`Description`), NULL)
38: ORDER BY `tss`.`SpecialDate` ASC separator '; ') AS `SpecialDescriptions`,
39: `te`.`CreatedAt` AS `CreatedAt`,
40: `te`.`UpdatedAt` AS `UpdatedAt`,
41: `te`.`IsActive` AS `IsActive`
42: FROM (((`timeentity` `te`
43: JOIN `timeentitytype` `tet` on((`te`.`EntityTypeID` = `tet`.`EntityTypeID`)))
44: LEFT JOIN `timeslot` `ts` on(((`ts`.`ScheduleID` = `te`.`ScheduleID`)
45: AND (`ts`.`IsActive` = 1))))
46: LEFT JOIN `timespecialschedule` `tss` on(((`tss`.`ScheduleID` = `te`.`ScheduleID`)
47: AND (`tss`.`IsActive` = 1))))
48: WHERE (`te`.`IsActive` = 1)
49: GROUP BY `te`.`ScheduleID`,
50: `te`.`EntityTypeID`,
51: `tet`.`EntityName`,
52: `te`.`EntityID`,
53: `te`.`ScheduleName`,
54: `te`.`CreatedAt`,
55: `te`.`UpdatedAt`,
56: `te`.`IsActive`
57:
This was abstract presentation of Working time information, but my system allow present working time for various organizations, for example this is presentation of Museums working time.
1: CREATE VIEW `timeinfomuseum` AS
2: SELECT `m`.`MuseumID` AS `MuseumID`,
3: `m`.`ToTown` AS `ToTown`,
4: `t`.`TownName` AS `TownName`,
5: `m`.`MuseumName` AS `MuseumName`,
6: `ts`.`ScheduleID` AS `ScheduleID`,
7: `ts`.`ScheduleName` AS `ScheduleName`,
8: group_concat((CASE
9: WHEN (`tsl`.`DayOfWeek` = 1) THEN if(`tsl`.`IsClosed`, 'Closed', concat(time_format(`tsl`.`OpenTime`, '`H:`i'), '-', time_format(`tsl`.`CloseTime`, '`H:`i')))
10: END)
11: ORDER BY `tsl`.`OpenTime` ASC separator ', ') AS `Monday`,
12: group_concat((CASE
13: WHEN (`tsl`.`DayOfWeek` = 2) THEN if(`tsl`.`IsClosed`, 'Closed', concat(time_format(`tsl`.`OpenTime`, '`H:`i'), '-', time_format(`tsl`.`CloseTime`, '`H:`i')))
14: END)
15: ORDER BY `tsl`.`OpenTime` ASC separator ', ') AS `Tuesday`,
16: group_concat((CASE
17: WHEN (`tsl`.`DayOfWeek` = 3) THEN if(`tsl`.`IsClosed`, 'Closed', concat(time_format(`tsl`.`OpenTime`, '`H:`i'), '-', time_format(`tsl`.`CloseTime`, '`H:`i')))
18: END)
19: ORDER BY `tsl`.`OpenTime` ASC separator ', ') AS `Wednesday`,
20: group_concat((CASE
21: WHEN (`tsl`.`DayOfWeek` = 4) THEN if(`tsl`.`IsClosed`, 'Closed', concat(time_format(`tsl`.`OpenTime`, '`H:`i'), '-', time_format(`tsl`.`CloseTime`, '`H:`i')))
22: END)
23: ORDER BY `tsl`.`OpenTime` ASC separator ', ') AS `Thursday`,
24: group_concat((CASE
25: WHEN (`tsl`.`DayOfWeek` = 5) THEN if(`tsl`.`IsClosed`, 'Closed', concat(time_format(`tsl`.`OpenTime`, '`H:`i'), '-', time_format(`tsl`.`CloseTime`, '`H:`i')))
26: END)
27: ORDER BY `tsl`.`OpenTime` ASC separator ', ') AS `Friday`,
28: group_concat((CASE
29: WHEN (`tsl`.`DayOfWeek` = 6) THEN if(`tsl`.`IsClosed`, 'Closed', concat(time_format(`tsl`.`OpenTime`, '`H:`i'), '-', time_format(`tsl`.`CloseTime`, '`H:`i')))
30: END)
31: ORDER BY `tsl`.`OpenTime` ASC separator ', ') AS `Saturday`,
32: group_concat((CASE
33: WHEN (`tsl`.`DayOfWeek` = 7) THEN if(`tsl`.`IsClosed`, 'Closed', concat(time_format(`tsl`.`OpenTime`, '`H:`i'), '-', time_format(`tsl`.`CloseTime`, '`H:`i')))
34: END)
35: ORDER BY `tsl`.`OpenTime` ASC separator ', ') AS `Sunday`,
36: group_concat(concat(date_format(`tss`.`SpecialDate`, '`Y-`m-`d'), ': ', if(`tss`.`IsClosed`, 'Closed', concat(time_format(`tss`.`OpenTime`, '`H:`i'), '-', time_format(`tss`.`CloseTime`, '`H:`i'))))
37: ORDER BY `tss`.`SpecialDate` ASC separator '; ') AS `SpecialDates`,
38: group_concat(if((`tss`.`Description` IS NOT NULL),concat(date_format(`tss`.`SpecialDate`, '`Y-`m-`d'), ': ', `tss`.`Description`), NULL)
39: ORDER BY `tss`.`SpecialDate` ASC separator '; ') AS `SpecialDescriptions`
40: FROM ((((`museums` `m`
41: JOIN `towns` `t` on((`m`.`ToTown` = `t`.`TownID`)))
42: LEFT JOIN `timeentity` `ts` on(((`ts`.`EntityID` = `m`.`MuseumID`)
43: AND (`ts`.`EntityTypeID` =
44: (SELECT `timeentitytype`.`EntityTypeID`
45: FROM `timeentitytype`
46: WHERE (`timeentitytype`.`EntityName` = 'Museum')))
47: AND (`ts`.`IsActive` = 1))))
48: LEFT JOIN `timeslot` `tsl` on(((`tsl`.`ScheduleID` = `ts`.`ScheduleID`)
49: AND (`tsl`.`IsActive` = 1))))
50: LEFT JOIN `timespecialschedule` `tss` on(((`tss`.`ScheduleID` = `ts`.`ScheduleID`)
51: AND (`tss`.`IsActive` = 1))))
52: WHERE (`m`.`IsActive` = 1)
53: GROUP BY `m`.`MuseumID`,
54: `m`.`ToTown`,
55: `t`.`TownName`,
56: `m`.`AdultPrice`,
57: `m`.`ChildPrice`,
58: `m`.`PensionerPrice`,
59: `m`.`VisitTime`,
60: `m`.`Phone`,
61: `m`.`Email`,
62: `ts`.`ScheduleID`,
63: `ts`.`ScheduleName`
64:
If we need to move database to MsSQL solution will be with function STRING_AGG.
1: SELECT
2: m.MuseumID,
3: m.ToTown,
4: t.TownName,
5: m.AdultPrice,
6: m.ChildPrice,
7: m.PensionerPrice,
8: m.VisitTime,
9: m.Phone,
10: m.Email,
11: ts.ScheduleID,
12: ts.ScheduleName,
13: -- Monday (multiple time slots)
14: STRING_AGG(
15: CASE WHEN tsl.DayOfWeek = 1 THEN
16: CASE WHEN tsl.IsClosed = 1 THEN 'Closed'
17: ELSE FORMAT(tsl.OpenTime, 'HH:mm') + '-' + FORMAT(tsl.CloseTime, 'HH:mm')
18: END
19: END, ', '
20: ) WITHIN GROUP (ORDER BY tsl.OpenTime) as Monday,
21: -- Tuesday (multiple time slots)
22: STRING_AGG(
23: CASE WHEN tsl.DayOfWeek = 2 THEN
24: CASE WHEN tsl.IsClosed = 1 THEN 'Closed'
25: ELSE FORMAT(tsl.OpenTime, 'HH:mm') + '-' + FORMAT(tsl.CloseTime, 'HH:mm')
26: END
27: END, ', '
28: ) WITHIN GROUP (ORDER BY tsl.OpenTime) as Tuesday,
29: -- Wednesday (multiple time slots)
30: STRING_AGG(
31: CASE WHEN tsl.DayOfWeek = 3 THEN
32: CASE WHEN tsl.IsClosed = 1 THEN 'Closed'
33: ELSE FORMAT(tsl.OpenTime, 'HH:mm') + '-' + FORMAT(tsl.CloseTime, 'HH:mm')
34: END
35: END, ', '
36: ) WITHIN GROUP (ORDER BY tsl.OpenTime) as Wednesday,
37: -- Thursday (multiple time slots)
38: STRING_AGG(
39: CASE WHEN tsl.DayOfWeek = 4 THEN
40: CASE WHEN tsl.IsClosed = 1 THEN 'Closed'
41: ELSE FORMAT(tsl.OpenTime, 'HH:mm') + '-' + FORMAT(tsl.CloseTime, 'HH:mm')
42: END
43: END, ', '
44: ) WITHIN GROUP (ORDER BY tsl.OpenTime) as Thursday,
45: -- Friday (multiple time slots)
46: STRING_AGG(
47: CASE WHEN tsl.DayOfWeek = 5 THEN
48: CASE WHEN tsl.IsClosed = 1 THEN 'Closed'
49: ELSE FORMAT(tsl.OpenTime, 'HH:mm') + '-' + FORMAT(tsl.CloseTime, 'HH:mm')
50: END
51: END, ', '
52: ) WITHIN GROUP (ORDER BY tsl.OpenTime) as Friday,
53: -- Saturday (multiple time slots)
54: STRING_AGG(
55: CASE WHEN tsl.DayOfWeek = 6 THEN
56: CASE WHEN tsl.IsClosed = 1 THEN 'Closed'
57: ELSE FORMAT(tsl.OpenTime, 'HH:mm') + '-' + FORMAT(tsl.CloseTime, 'HH:mm')
58: END
59: END, ', '
60: ) WITHIN GROUP (ORDER BY tsl.OpenTime) as Saturday,
61: -- Sunday (multiple time slots)
62: STRING_AGG(
63: CASE WHEN tsl.DayOfWeek = 7 THEN
64: CASE WHEN tsl.IsClosed = 1 THEN 'Closed'
65: ELSE FORMAT(tsl.OpenTime, 'HH:mm') + '-' + FORMAT(tsl.CloseTime, 'HH:mm')
66: END
67: END, ', '
68: ) WITHIN GROUP (ORDER BY tsl.OpenTime) as Sunday,
69: -- Special Schedules
70: STRING_AGG(
71: CASE WHEN tss.SpecialDate IS NOT NULL THEN
72: FORMAT(tss.SpecialDate, 'yyyy-MM-dd') + ': ' +
73: CASE WHEN tss.IsClosed = 1 THEN 'Closed'
74: ELSE FORMAT(tss.OpenTime, 'HH:mm') + '-' + FORMAT(tss.CloseTime, 'HH:mm')
75: END
76: END, '; '
77: ) WITHIN GROUP (ORDER BY tss.SpecialDate) as SpecialDates,
78: -- Special Descriptions
79: STRING_AGG(
80: CASE WHEN tss.Description IS NOT NULL THEN
81: FORMAT(tss.SpecialDate, 'yyyy-MM-dd') + ': ' + tss.Description
82: END, '; '
83: ) WITHIN GROUP (ORDER BY tss.SpecialDate) as SpecialDescriptions
84: FROM Museums m
85: JOIN Towns t ON m.ToTown = t.TownID
86: LEFT JOIN [Time-entity] ts ON ts.EntityID = m.MuseumID
87: AND ts.EntityTypeID = (SELECT EntityTypeID FROM TimeEntityType WHERE EntityName = 'Museum')
88: AND ts.IsActive = 1
89: LEFT JOIN TimeSlot tsl ON tsl.ScheduleID = ts.ScheduleID AND tsl.IsActive = 1
90: LEFT JOIN TimeSpecialSchedule tss ON tss.ScheduleID = ts.ScheduleID AND tss.IsActive = 1
91: WHERE m.IsActive = 1
92: GROUP BY m.MuseumID, m.ToTown, t.TownName, m.AdultPrice, m.ChildPrice,
93: m.PensionerPrice, m.VisitTime, m.Phone, m.Email, ts.ScheduleID, ts.ScheduleName;
94:
Sql context:
Comments (
)
)
Link to this page:
http://www.vb-net.com/SqlConcatRowsToString/Index.htm
|
|