Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>Try this one -</p> <p><strong>Schema:</strong></p> <pre><code>SET NOCOUNT ON; DECLARE @TB1 TABLE ( HomeID INT , DayID INT , TimeCode INT , timevalue CHAR(10) ) INSERT INTO @TB1 (HomeID, DayID, TimeCode, timevalue) VALUES (1, 1, 1, '07:00:00'), (1, 1, 2, '07:30:00'), (1, 1, 3, '08:00:00'), (1, 1, 4, '13:00:00'), (1, 1, 5, '17:00:00'), (1, 1, 6, '19:00:00'), (1, 1, 7, '21:30:00'), (1, 2, 1, '08:00:00'), (1, 2, 2, '08:30:00'), (1, 2, 3, '13:00:00'), (1, 2, 4, '20:00:00'), (1, 2, 5, '23:00:00'), (2, 1, 1, '08:00:00'), (2, 1, 2, '08:30:00'), (2, 1, 3, '09:00:00'), (2, 1, 4, '13:00:00'), (2, 1, 5, '18:00:00'), (2, 1, 6, '20:00:00'), (2, 1, 7, '22:00:00'), (2, 2, 1, '09:00:00'), (2, 2, 2, '10:00:00'), (2, 2, 3, '13:00:00'), (2, 2, 4, '19:00:00'), (2, 2, 5, '22:30:00') DECLARE @TB2 TABLE ( DayType INT , StringCode INT , RndString VARCHAR(50) ) INSERT INTO @TB2 (DayType, StringCode, RndString) VALUES (1, 1, 'I get up at '), (1, 2, 'I have breakfast at '), (1, 3, 'I go to office at '), (1, 4, 'I have lunch at '), (1, 5, 'I come back from office at '), (1, 6, 'I have dinner at '), (1, 7, 'I sleep at '), (2, 1, 'I get up at '), (2, 2, 'I have breakfast at '), (2, 3, 'I have lunch at '), (2, 4, 'I have dinner at '), (2, 5, 'I sleep at ') </code></pre> <p><strong>Example 1:</strong></p> <pre><code>SELECT '@HomeID' = t.HomeID , ( SELECT '@ID' = t2_.DayID , ( SELECT t4.RndString + LEFT(t3.TimeValue, 5) FROM @TB1 t3 JOIN @TB2 t4 ON t3.DayID = t4.DayType AND t3.TimeCode = t4.StringCode WHERE t2_.DayID = t3.DayID AND t.HomeID = t3.HomeID FOR XML PATH ('String'), TYPE ) FROM ( SELECT DISTINCT t2.DayID FROM @TB1 t2 WHERE t.HomeID = t2.HomeID ) t2_ FOR XML PATH ('Day'), TYPE ) FROM ( SELECT DISTINCT T1.HomeID FROM @TB1 T1 ) t FOR XML PATH ('Person'), TYPE </code></pre> <p><strong>Example 2:</strong></p> <pre><code>SELECT Person.HomeID , ID = [Day].DayID , String.String FROM @TB1 Person JOIN ( SELECT T1.HomeID, T1.DayID FROM @TB1 T1 ) [Day] on Person.HomeID = [Day].HomeID JOIN ( SELECT String = t4.RndString + LEFT(t3.TimeValue, 5) , t3.DayID , t3.HomeID FROM @TB1 t3 JOIN @TB2 t4 ON t3.DayID = t4.DayType AND t3.TimeCode = t4.StringCode ) String ON [Day].DayID = String.DayID AND [Day].HomeID = String.HomeID GROUP BY Person.HomeID, [Day].DayID, String FOR XML AUTO </code></pre> <p><strong>Output:</strong></p> <pre><code>&lt;Person HomeID="1"&gt; &lt;Day ID="1"&gt; &lt;String&gt;I get up at 07:00&lt;/String&gt; &lt;String&gt;I have breakfast at 07:30&lt;/String&gt; &lt;String&gt;I go to office at 08:00&lt;/String&gt; &lt;String&gt;I have lunch at 13:00&lt;/String&gt; &lt;String&gt;I come back from office at 17:00&lt;/String&gt; &lt;String&gt;I have dinner at 19:00&lt;/String&gt; &lt;String&gt;I sleep at 21:30&lt;/String&gt; &lt;/Day&gt; &lt;Day ID="2"&gt; &lt;String&gt;I get up at 08:00&lt;/String&gt; &lt;String&gt;I have breakfast at 08:30&lt;/String&gt; &lt;String&gt;I have lunch at 13:00&lt;/String&gt; &lt;String&gt;I have dinner at 20:00&lt;/String&gt; &lt;String&gt;I sleep at 23:00&lt;/String&gt; &lt;/Day&gt; &lt;/Person&gt; &lt;Person HomeID="2"&gt; &lt;Day ID="1"&gt; &lt;String&gt;I get up at 08:00&lt;/String&gt; &lt;String&gt;I have breakfast at 08:30&lt;/String&gt; &lt;String&gt;I go to office at 09:00&lt;/String&gt; &lt;String&gt;I have lunch at 13:00&lt;/String&gt; &lt;String&gt;I come back from office at 18:00&lt;/String&gt; &lt;String&gt;I have dinner at 20:00&lt;/String&gt; &lt;String&gt;I sleep at 22:00&lt;/String&gt; &lt;/Day&gt; &lt;Day ID="2"&gt; &lt;String&gt;I get up at 09:00&lt;/String&gt; &lt;String&gt;I have breakfast at 10:00&lt;/String&gt; &lt;String&gt;I have lunch at 13:00&lt;/String&gt; &lt;String&gt;I have dinner at 19:00&lt;/String&gt; &lt;String&gt;I sleep at 22:30&lt;/String&gt; &lt;/Day&gt; &lt;/Person&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload