StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>for those not using PostgreSQL, I can think of 2 solutions.</p> <p>Assuming (for this example) a results table containing the columns dow,hour and mins then you can..</p> <p>1) Do a rather messy multi-join (Oracle and SQLServer versions shown in a moment) 2) or write a CONCAT_MYSTATS Function that can return the results. For SQLITE you can use the existing GROUP_CONCAT aggregate Function.</p> <p><strong>Lazy Version Code ORACLE:</strong></p> <pre><code>SELECT d.dow, MAX(NVL(d0.hour,0)) || '=' || SUM(NVL(d0.mins,0)) || ';' || MAX(NVL(d1.hour,1)) || '=' || SUM(NVL(d1.mins,0)) || ';' || MAX(NVL(d2.hour,2)) || '=' || SUM(NVL(d2.mins,0)) || ';' || MAX(NVL(d3.hour,3)) || '=' || SUM(NVL(d3.mins,0)) || ';' || MAX(NVL(d4.hour,4)) || '=' || SUM(NVL(d4.mins,0)) || ';' || MAX(NVL(d5.hour,5)) || '=' || SUM(NVL(d5.mins,0)) || ';' || MAX(NVL(d6.hour,6)) || '=' || SUM(NVL(d6.mins,0)) || ';' || MAX(NVL(d7.hour,7)) || '=' || SUM(NVL(d7.mins,0)) || ';' || MAX(NVL(d8.hour,8)) || '=' || SUM(NVL(d8.mins,0)) || ';' || MAX(NVL(d9.hour,9)) || '=' || SUM(NVL(d9.mins,0)) || ';' || MAX(NVL(d10.hour,10)) || '=' || SUM(NVL(d10.mins,0)) || ';' || MAX(NVL(d11.hour,11)) || '=' || SUM(NVL(d11.mins,0)) || ';' || MAX(NVL(d12.hour,12)) || '=' || SUM(NVL(d12.mins,0)) || ';' || MAX(NVL(d13.hour,13)) || '=' || SUM(NVL(d13.mins,0)) || ';' || MAX(NVL(d14.hour,14)) || '=' || SUM(NVL(d14.mins,0)) || ';' || MAX(NVL(d15.hour,15)) || '=' || SUM(NVL(d15.mins,0)) || ';' || MAX(NVL(d16.hour,16)) || '=' || SUM(NVL(d16.mins,0)) || ';' || MAX(NVL(d17.hour,17)) || '=' || SUM(NVL(d17.mins,0)) || ';' || MAX(NVL(d18.hour,18)) || '=' || SUM(NVL(d18.mins,0)) || ';' || MAX(NVL(d19.hour,19)) || '=' || SUM(NVL(d19.mins,0)) || ';' || MAX(NVL(d20.hour,20)) || '=' || SUM(NVL(d20.mins,0)) || ';' || MAX(NVL(d21.hour,21)) || '=' || SUM(NVL(d21.mins,0)) || ';' || MAX(NVL(d22.hour,22)) || '=' || SUM(NVL(d22.mins,0)) || ';' || MAX(NVL(d23.hour,23)) || '=' || SUM(NVL(d23.mins,0)) FROM (SELECT distinct dow FROM mystats) d LEFT OUTER JOIN mystats d0 ON d0.dow = d.dow AND d0.hour=0 LEFT OUTER JOIN mystats d1 ON d1.dow = d.dow AND d1.hour=1 LEFT OUTER JOIN mystats d2 ON d2.dow = d.dow AND d2.hour=2 LEFT OUTER JOIN mystats d3 ON d3.dow = d.dow AND d3.hour=3 LEFT OUTER JOIN mystats d4 ON d4.dow = d.dow AND d4.hour=4 LEFT OUTER JOIN mystats d5 ON d5.dow = d.dow AND d5.hour=5 LEFT OUTER JOIN mystats d6 ON d6.dow = d.dow AND d6.hour=6 LEFT OUTER JOIN mystats d7 ON d7.dow = d.dow AND d7.hour=7 LEFT OUTER JOIN mystats d8 ON d8.dow = d.dow AND d8.hour=8 LEFT OUTER JOIN mystats d9 ON d9.dow = d.dow AND d9.hour=9 LEFT OUTER JOIN mystats d10 ON d10.dow = d.dow AND d10.hour=10 LEFT OUTER JOIN mystats d11 ON d11.dow = d.dow AND d11.hour=11 LEFT OUTER JOIN mystats d12 ON d12.dow = d.dow AND d12.hour=12 LEFT OUTER JOIN mystats d13 ON d13.dow = d.dow AND d13.hour=13 LEFT OUTER JOIN mystats d14 ON d14.dow = d.dow AND d14.hour=14 LEFT OUTER JOIN mystats d15 ON d15.dow = d.dow AND d15.hour=15 LEFT OUTER JOIN mystats d16 ON d16.dow = d.dow AND d16.hour=16 LEFT OUTER JOIN mystats d17 ON d17.dow = d.dow AND d17.hour=17 LEFT OUTER JOIN mystats d18 ON d18.dow = d.dow AND d18.hour=18 LEFT OUTER JOIN mystats d19 ON d19.dow = d.dow AND d19.hour=19 LEFT OUTER JOIN mystats d20 ON d20.dow = d.dow AND d20.hour=20 LEFT OUTER JOIN mystats d21 ON d21.dow = d.dow AND d21.hour=21 LEFT OUTER JOIN mystats d22 ON d22.dow = d.dow AND d22.hour=22 LEFT OUTER JOIN mystats d23 ON d23.dow = d.dow AND d23.hour=23 GROUP BY d.dow </code></pre> <p>SQLSERVER:</p> <pre><code>SELECT d.dow, CAST(MAX(ISNULL(d0.hour,0)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d0.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d1.hour,1)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d1.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d2.hour,2)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d2.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d3.hour,3)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d3.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d4.hour,4)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d4.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d5.hour,5)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d5.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d6.hour,6)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d6.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d7.hour,7)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d7.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d8.hour,8)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d8.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d9.hour,9)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d9.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d10.hour,10)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d10.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d11.hour,11)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d11.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d12.hour,12)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d12.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d13.hour,13)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d13.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d14.hour,14)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d14.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d15.hour,15)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d15.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d16.hour,16)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d16.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d17.hour,17)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d17.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d18.hour,18)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d18.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d19.hour,19)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d19.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d20.hour,20)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d20.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d21.hour,21)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d21.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d22.hour,22)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d22.mins,0)) AS VARCHAR) + ';' + CAST(MAX(ISNULL(d23.hour,23)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d23.mins,0)) AS VARCHAR) FROM (SELECT distinct dow FROM mystats) d LEFT OUTER JOIN mystats d0 ON d0.dow = d.dow AND d0.hour=0 LEFT OUTER JOIN mystats d1 ON d1.dow = d.dow AND d1.hour=1 LEFT OUTER JOIN mystats d2 ON d2.dow = d.dow AND d2.hour=2 LEFT OUTER JOIN mystats d3 ON d3.dow = d.dow AND d3.hour=3 LEFT OUTER JOIN mystats d4 ON d4.dow = d.dow AND d4.hour=4 LEFT OUTER JOIN mystats d5 ON d5.dow = d.dow AND d5.hour=5 LEFT OUTER JOIN mystats d6 ON d6.dow = d.dow AND d6.hour=6 LEFT OUTER JOIN mystats d7 ON d7.dow = d.dow AND d7.hour=7 LEFT OUTER JOIN mystats d8 ON d8.dow = d.dow AND d8.hour=8 LEFT OUTER JOIN mystats d9 ON d9.dow = d.dow AND d9.hour=9 LEFT OUTER JOIN mystats d10 ON d10.dow = d.dow AND d10.hour=10 LEFT OUTER JOIN mystats d11 ON d11.dow = d.dow AND d11.hour=11 LEFT OUTER JOIN mystats d12 ON d12.dow = d.dow AND d12.hour=12 LEFT OUTER JOIN mystats d13 ON d13.dow = d.dow AND d13.hour=13 LEFT OUTER JOIN mystats d14 ON d14.dow = d.dow AND d14.hour=14 LEFT OUTER JOIN mystats d15 ON d15.dow = d.dow AND d15.hour=15 LEFT OUTER JOIN mystats d16 ON d16.dow = d.dow AND d16.hour=16 LEFT OUTER JOIN mystats d17 ON d17.dow = d.dow AND d17.hour=17 LEFT OUTER JOIN mystats d18 ON d18.dow = d.dow AND d18.hour=18 LEFT OUTER JOIN mystats d19 ON d19.dow = d.dow AND d19.hour=19 LEFT OUTER JOIN mystats d20 ON d20.dow = d.dow AND d20.hour=20 LEFT OUTER JOIN mystats d21 ON d21.dow = d.dow AND d21.hour=21 LEFT OUTER JOIN mystats d22 ON d22.dow = d.dow AND d22.hour=22 LEFT OUTER JOIN mystats d23 ON d23.dow = d.dow AND d23.hour=23 GROUP BY d.dow </code></pre> <p><strong>CONCAT Function Version: ORACLE:</strong></p> <pre><code>CREATE FUNCTION CONCAT_MYSTATS (pDOW IN INTEGER) RETURN VARCHAR2 AS ALIST VARCHAR2(200) DEFAULT ''; BEGIN FOR REC IN ( SELECT h.HR AS HOUR, SUM(NVL(d.mins,0)) AS MINS FROM (SELECT 0 AS Hr FROM dual UNION ALL SELECT 1 AS HR FROM dual UNION ALL SELECT 2 AS HR FROM dual UNION ALL SELECT 3 AS HR FROM dual UNION ALL SELECT 4 AS HR FROM dual UNION ALL SELECT 5 AS HR FROM dual UNION ALL SELECT 6 AS HR FROM dual UNION ALL SELECT 7 AS HR FROM dual UNION ALL SELECT 8 AS HR FROM dual UNION ALL SELECT 9 AS HR FROM dual UNION ALL SELECT 10 AS HR FROM dual UNION ALL SELECT 11 AS HR FROM dual UNION ALL SELECT 12 AS HR FROM dual UNION ALL SELECT 13 AS HR FROM dual UNION ALL SELECT 14 AS HR FROM dual UNION ALL SELECT 15 AS HR FROM dual UNION ALL SELECT 16 AS HR FROM dual UNION ALL SELECT 17 AS HR FROM dual UNION ALL SELECT 18 AS HR FROM dual UNION ALL SELECT 19 AS HR FROM dual UNION ALL SELECT 20 AS HR FROM dual UNION ALL SELECT 21 AS HR FROM dual UNION ALL SELECT 22 AS HR FROM dual UNION ALL SELECT 23 AS HR FROM dual) h LEFT OUTER JOIN mystats d ON d.hour = h.HR WHERE d.dow = pDOW GROUP BY h.HR ORDER BY 1 ) LOOP -- IF NVL(ALIST,' ') !=' ' THEN ALIST := ALIST || ';'; END IF; ALIST := ALIST || REC.HOUR || '=' || REC.MINS; END LOOP; RETURN ALIST; END; / </code></pre> <p>and then</p> <pre><code>SELECT d.dow, CONCAT_MYSTATS(d.dow) FROM (SELECT distinct dow FROM mystats) d </code></pre> <p><strong>SQLSERVER Version</strong></p> <pre><code>CREATE FUNCTION CONCAT_MYSTATS (@pDOW INTEGER) RETURNS VARCHAR(200) AS BEGIN DECLARE @ALIST VARCHAR(200); SET @ALIST=''; DECLARE @QHR INTEGER; DECLARE @QMINS INTEGER; -- DECLARE cREC CURSOR FOR SELECT h.HR AS HOUR, SUM(ISNULL(d.mins,0)) AS MINS FROM (SELECT 0 AS Hr UNION ALL SELECT 1 AS HR UNION ALL SELECT 2 AS HR UNION ALL SELECT 3 AS HR UNION ALL SELECT 4 AS HR UNION ALL SELECT 5 AS HR UNION ALL SELECT 6 AS HR UNION ALL SELECT 7 AS HR UNION ALL SELECT 8 AS HR UNION ALL SELECT 9 AS HR UNION ALL SELECT 10 AS HR UNION ALL SELECT 11 AS HR UNION ALL SELECT 12 AS HR UNION ALL SELECT 13 AS HR UNION ALL SELECT 14 AS HR UNION ALL SELECT 15 AS HR UNION ALL SELECT 16 AS HR UNION ALL SELECT 17 AS HR UNION ALL SELECT 18 AS HR UNION ALL SELECT 19 AS HR UNION ALL SELECT 20 AS HR UNION ALL SELECT 21 AS HR UNION ALL SELECT 22 AS HR UNION ALL SELECT 23 AS HR) h LEFT OUTER JOIN mystats d ON d.hour = h.HR WHERE d.dow = @pDOW GROUP BY h.HR ORDER BY 1; -- OPEN cREC FETCH cREC INTO @QHR,@QMINS; WHILE @@Fetch_Status = 0 BEGIN -- IF @ALIST!='' SET @ALIST=@ALIST + ';'; SET @ALIST=@ALIST + CAST(@QHR AS VARCHAR) + '=' + CAST(@QMINS AS VARCHAR); -- FETCH cREC INTO @QHR,@QMINS; END CLOSE cREC DEALLOCATE cREC RETURN @ALIST; END </code></pre> <p>and then </p> <pre><code> SELECT d.dow, dbo.CONCAT_MYSTATS(d.dow) FROM (SELECT distinct dow FROM mystats) d </code></pre> <p>and finally, for SQLITE all you need to do is</p> <pre><code> SELECT d.dow, GROUP_CONCAT(d.hour || '=' || d.mins,';') FROM mystats d GROUP BY d.dow </code></pre> <p>Working example (SQLITE) at <a href="http://www.datagloop.com/?ACTION=LOGIN&USERNAME=DATAGLOOP/SO_CONCAT" rel="nofollow">http://www.datagloop.com/?ACTION=LOGIN&USERNAME=DATAGLOOP/SO_CONCAT</a> </p>

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