We help IT Professionals succeed at work.

Format a result from several rowns into one

Peter Nordberg
Peter Nordberg asked
on
Hi,

I have this query looking like this:
SELECT * FROM dbo.EmployeeWorkTimeSchedule AS ewts WHERE EmployeeID = 2514 AND MONTH(ewts.fromDate) = 12

Open in new window


And giving the following result:
Result
How can I make a query so that the rusult will be formatted like this?
2018-12-01   Pass 1: 8:00-12:00 | Lunch: 12:00 - 13:00 | Pass 2: 13:00-17:00
2018-12-02   Pass 1: 8:00-12:00 | Lunch: 12:00 - 13:00 | Pass 2: 13:00-17:00

Thanks for help!

Peter
Comment
Watch Question

View Solution Only

Senior Developer
Commented:
First of all: Use table alias names consistently.
Then: I don't understand your model. Is it relational or dimensional?

This should be done in the front-end. Otherwise it is a string concat, using STRING_AGG() (SQL Server 2017+) or FOR XML. E.g. FOR XML:

SELECT   CAST(O.fromDate AS DATE) ,
         STUFF((   SELECT   '| ' + I.descr + ': ' + FORMAT(I.fromDate, 'hh:nn') + '-' + FORMAT(I.toDate, 'hh:nn')
                   FROM     dbo.EmployeeWorkTimeSchedule I
                   WHERE    I.EmployeeID = O.EmployeeID
                            AND CAST(I.fromDate AS DATE) = CAST(O.fromDate AS DATE)
                   ORDER BY I.fromDate
                   FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,
               1 ,
               2 ,
               '') AS Desc_Agg
FROM     dbo.EmployeeWorkTimeSchedule O
WHERE    O.EmployeeID = 2514
         AND MONTH(O.fromDate) = 12
GROUP BY CAST(O.fromDate AS DATE);

Open in new window

Peter NordbergIT Manager

Author

Commented:
Thank you! It worked as expected!

Peter