<div>
what database ? Oracle ? MSSQL ? Access ? 

</div>

<div>
not a lot of options SQL way 
 
load the grid manually from the query data -&gt; non DB aware grid 
 

</div>

<div>
Loading the grid manually could be an option, but is not preferred. 
Does anybody else have an idea?
</div>

<div>
How does it know which function's time block should be represented first. 
For your example in day 2 tuesday you have the 10:00 timeblock in the first line but the 00:00 timeblock in the second line. 
</div>

<div>
It's preferred to have the timeblocks sorted on StartTime with a function/day. 
The example is therefor not the best I ever made :-) sorry for that
</div>

<div>
To be honest I don't think you are going to be able to do this in one complicated SQL query. 
The main issues are that there are duplicate records (excluding the time fields) so you cant group or do proper nested select statements. Also you couldnt really exclude entries from say function 1's second line for monday, thursday and saturday.
</div>

<div>
Can it be done by using multiple SQL's or using a temporary table?
</div>

<div>
I think I'd have to agree with Geert_Gruwez about using a manual grid. 
No reason why you couldnt be a bit clever though and use a custom object to store the data but also know how to display itself.
</div>

<div>
Let me have a think, maybe a stored procedure would be able to do it for you.
</div>

<div>
i even tried it on Oracle 9 and 10 with analytical functions (where you access to the resulting rows). 
I didn't get there, nearly, but just the duplicates (as mikelittlewood) makes it allbeit impossible 
 
for a Delphi solution &nbsp;it's actually easy, SQL almost impossible 
 
MS Access is like driving a car without being allowed to use the gears ... from an SQL standing point 
 

</div>

<div>
Let's wait and see what mikelittlewood coes up with.
</div>

<div>
Hello 
 
I would suggest a solution where you create a helping table. 
Then run some SQL code to fill this up, and then some more SQL to present the data. 
 
Be aware, I cannot check the code, so consider the SQL as psevdo SQL. 
 
Hope you can use it. 
 
kind regards 
Øyvind Strøm 

<pre><code class="code-100 nolinks" id="code-123336">Create a new table: OUTPUTTABLE
 
Fields:
Key
Function 
Sortorder
Mon (char, to hold the combined Start and End string)
MonNumber
Tue (char, to hold the combined Start and End string)
TueNumber
Wed (char, to hold the combined Start and End string)
WedNumber
Thu (char, to hold the combined Start and End string)
ThuNumber
Fri (char, to hold the combined Start and End string)
FriNumber
Sat (char, to hold the combined Start and End string)
SatNumber
Sun (char, to hold the combined Start and End string)
SunNumber
 
Now, execute something like this:
SQL 1:
 
Insert into OUTPUTTABLE
(Function
,Mon
,MonNumber
,Tue
,TueNumber
,Wed
,WedNumber
,Thu
,ThuNumber
,Fri
,FriNumber
,Sat
,SatNumber
,Sun
,SunNumber)
 
SELECT
 Function
 , CASE WHEN Daynumber=1
 THEN StartTime &amp; ' - ' &amp; EndTime
 ELSE ''
 , CASE WHEN Daynumber=1
 THEN Number
 ELSE ''
 , CASE WHEN Daynumber=2
 THEN StartTime &amp; ' - ' &amp; EndTime
 ELSE ''
 , CASE WHEN Daynumber=2
 THEN Number
 ELSE ''
and so on up to 7
 
Then another SQL:
SQL 2:
 
SELECT 
Function
,Sub1.Mon
,Sub1.Tue
,Sub1.Wed
,Sub1.Thu
,Sub1.Fri
,Sub1.Sat
,Sub1.Sun
FROM
(SELECT
Function
,Mon
,Mon AS MonKey
,Tue
,Tue AS TueKey
,Wed
,Wed AS WedKey
,Thu
,Thu AS ThuKey
,Fri
,Fri AS FriKey
,Sat
,Sat AS SatKey
,Sun
,Sun AS SunKey
FROM OUTPUTTABLE
UNION ALL
SELECT
 Function
 ,char(MonNumber,2) AS Mon
 ,Mon AS MonKey
 ,char(TueNumber,2) AS Tue
 ,Tue AS TueKey
 ,char(WedNumber,2) AS Wed
 ,Wed AS WedKey
 ,char(ThuNumber,2) AS Thu
 ,Thu AS ThuKey
 ,char(FriNumber,2) AS Fri
 ,Fri AS FriKey
 ,char(SatNumber,2) AS Sat
 ,Sat AS SatKey
 ,char(SunNumber,2) AS Sun
 ,Sun AS SunKey
FROM OUTPUTTABLE ) AS Sub1
ORDER BY 
Function, MonKey, TueKey, WedKey, ThuKey, FriKey, SatKey, SunKey
 
 
If this does not give you the preferred sorting, make a sortfield in SQL 1, with format 1234567
1: 0 if MonKey has value, else 1
2: 0 if TueKey has value, else 1
3: 0 if WedKey has value, else 1
and so on
</code></pre>
</div>

<div>
&gt;&gt; <a href="http://../../../../M_1341617.html" rel="ugc">OyvindS</a> 
all can be done in 1 select 
 
&gt;&gt;&nbsp;mikelittlewood 
didn't know MSAccess had stored procedures now 
don't blame me for that, just don't like MSAccess :) 
 
 
 

</div>

<div>
Thanks Mike, I'll give it a go and get back to you a.s.a.p.
</div>

<div>
High everyone... 
 
I must be missing something, isn't it a select from the Table described with a join to Functions (and a bit of string formatting) ? 
 
What I do not understand is the statement : &quot;if at all possible, also the field Number should be added on a separate line within the same field where the timeblock is presented&quot; 
 
so, forgetting the formatting for a second, and naming conventions, where is the real problem ? 
 
select mytable.Function, mytable.Daynr, mytable.Starttime, mytable.Endtime, mytable.Number , functions.lookupfield &nbsp; (etc...) 
from MyTable 
inner join Functions on functions.Lookupfield = mytable.function 
 
and if you need uniqueness, can simply group by or use the above as a subquery... 
 
maybe some more explanation, and possibly a snapshot of the access database ?
</div>

<div>
Hello mark wills, 
Probably you need to read a bit more in detail. 
The problem lies within the represenation of the data. 
The day's are presented as columns. So it's a kind of pivot-table. 
If you have any solution then please feel free to post it here, details about the table and even the data in it, are presented at the very top of this question.
</div>

<div>
Thought I had read it a few times. Did not pick up on the pivot-table requirement... until I scrolled down - sorry about that... 
 
Awithout doing too much thinking (as is obvious by now), what about a crosstab / pivot query in access ? Will obviously need a bit of work, and the real challeng is getting the numbers on a second line (and days need expanding, and function needs linking), but as a starting point, what about : 
 
TRANSFORM Max(format(Function.StartTime,&quot;hh:mm&quot;)&amp;&quot;-&quot;&amp;format(Function.EndTime,&quot;hh:mm&quot;)&amp;&quot; &nbsp; &nbsp;&quot;&amp;function.Number ) AS MaxOfStartTime 
SELECT Function.Function 
FROM Function 
GROUP BY Function.Function 
PIVOT Function.Daynr; 
 
 

</div>

<div>
Thank you all very much. The solution works perfectly. 
Adding the lookup for functionname was no problem.
</div>

<div>
Nice to see that things can be done, even when it's difficult. 
Thank you all for your support.
</div>

<div>
<div class="content wysiwyg-content">
Hi, 
 
This is a real chalenge: 
In my table I have the following fields: 
- Function (Integer) Lookupfield in table Functions 
- Daynr (Integer) 1-7 = mon-sun 
- Starttime (Time) 
- Endtime (Time) 
- Number (Integer) 
 
For each Function (Jobdescr.) one or more timeblocks (Start-Endtime) can be present in the table for one or multiple days (Daynr). 
If for some Function more than one timeblock is planned for the same day, then a separate row will be presented (see function 1). 
 
The Start- and Endtime can be converted to a stringvalue and, if at all possible, also the field Number should be added on a separate line within the same field where the timeblock is presented. Maybe that can be done by using some calculated field? 
 
Can anybody help with getting the following output? 
Thanks, Stef 

<pre><code class="code-10 nolinks" id="code-123202">Content of table:
 
Function Daynr Starttime Endtime Number
 1 1 08:00 16:00 3
 1 2 10:00 18:00 2
 1 3 08:00 16:00 1
 1 4 22:00 06:00 1
 1 5 08:00 16:00 2
 1 6 08:00 16:00 1
 1 7 08:00 16:00 1
 1 2 00:00 08:00 2
 1 3 18:00 22:00 1
 1 5 11:00 15:00 2
 1 6 18:00 23:00 1
 1 7 08:00 16:00 1
 2 1 08:00 16:00 1
 2 4 22:00 06:00 1
 2 5 13:00 19:00 2
 2 6 08:00 16:00 3
 2 7 08:00 16:00 2
 3 2 10:00 18:00 2
 3 4 22:00 06:00 1
 3 6 14:30 21:30 2
 3 7 08:45 16:45 3
 
 
Result:
 
Function Mon Thu Wed Thu Fri Sat Sun
-----------------------------------------------------------------------------------------------
function 1 08:00-16:00 10:00-18:00 08:00-16:00 22:00-06:00 08:00-16:00 08:00-16:00 08:00-16:00
 3 2 1 1 2 1 1
function 1 00:00-08:00 18:00-22:00 11:00-15:00 18:00-23:00
 2 1 1 1
function 2 08:00-16:00 22:00-06:00 13:00-19:00 08:00-16:00 08:00-16:00
 1 1 2 3 2
function 3 10:00-18:00 22:00-06:00 14:30-21:30 08:45-16:45
 2 1 2 3
</code></pre>
</div>
</div>

Hi,

This is a real chalenge: 
In my table I have the following fields:
- Function (Integer) Lookupfield in table Functions
- Daynr (Integer) 1-7 = mon-sun
- Starttime (Time)
- Endtime (Time)
- Number (Integer)

For each Function (Jobdescr.) one or more timeblocks (Start-Endtime) can be present in the table for one or multiple days (Daynr).
If for some Function more than one timeblock is planned for the same day, then a separate row will be presented (see function 1).

The Start- and Endtime can be converted to a stringvalue and, if at all possible, also the field Number should be added on a separate line within the same field where the timeblock is presented. Maybe that can be done by using some calculated field?

Can anybody help with getting the following output?
Thanks, Stef

SQL question (expert)

Delphi is the most powerful Object Pascal IDE and component library for cross-platform Native App Development with flexible Cloud services and broad IoT connectivity. It provides powerful VCL controls for Windows 10 and enables FMX development for Windows, Mac and Mobile. Delphi is your choice for ultrafast Enterprise Strong Development™. Look for increased memory for large projects, extended multi-monitor support, improved Object Inspector and much more. Delphi is 5x faster for development and deployment across multiple desktop, mobile, cloud and database platforms including 32-bit and 64-bit Windows 10.

Delphi

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.