[CLOSED] Firebird-SQL lazarus / 63-Database ---------------------------------------------------------------------- egsuh · 2023-04-03 04:26 I have two tables in Firebird. Is there any more concise SQL that'll do what I'd like to do? It's like a pivot-table, and select statement and result is in the image. Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE SAMPLES(  PID INTEGER,  RID INTEGER,  LOGIN_ID VARCHAR(20),  PW VARCHAR(20),  EMAIL VARCHAR(25),  PHONE VARCHAR(20),  FIRST_NAME VARCHAR(20),  LAST_NAME VARCHAR(20)); CREATE TABLE SAMPLE_DATA(  PID INTEGER,  RID INTEGER,  KEY_NAME VARCHAR(20),  KEY_VALUE VARCHAR(120)); /* select statement */SELECT s.*, d.key_value Gender, e.Key_value age, f.Key_value occp FROM SAMPLES s      LEFT JOIN (SELECT pid, rid, key_value FROM sample_data WHERE key_name='Gender') d    ON s.pid=d.pid AND s.rid=d.rid        LEFT JOIN (SELECT pid, rid, key_value FROM sample_data WHERE key_name='Age') e    ON s.pid=e.pid AND s.rid=e.rid        LEFT JOIN (SELECT pid, rid, key_value FROM sample_data WHERE key_name='Occp') f    ON s.pid=f.pid AND s.rid=f.rid ---------------------------------------------------------------------- Zvoni · 2023-04-03 06:55 I‘m not even going to pretend understanding that design. That‘s EAV Antipattern if i ever saw one. To op‘s question: instead of subselects i‘d use CTE‘s but that‘s personal preference, and maybe a coalesce to get rid of the null‘s ---------------------------------------------------------------------- af0815 · 2023-04-03 08:48 It is IMHO a pivot tranformation I know this for firebird and it says it is not dynamic possible https://stackoverflow.com/questions/55449169/is-there-any-way-to- pivot-rows-to-columns-dynamically-without-a-specific-no-of-c MS-SQL have in the meantype such a feature https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/ in the past i have done this with dynamic TSQL. This means i have computed the whole SQL-Statement by code and executed this. It was not effiency, because it was not 'pre' compilable (and only runtime optimation) for the mssql server. (Or i was complete wrong with the pivot :-) ) ---------------------------------------------------------------------- ttomas · 2023-04-03 09:42 Same result, but using indexes on pid, rid and key_name in sample_data, if exists. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT s.*  , d.key_value Gender, e.Key_value age, f.Key_value occp FROM SAMPLES s     LEFT JOIN sample_data d  ON (s.pid=d.pid AND s.rid=d.rid AND d.key_name='Gender')       LEFT JOIN sample_data e ON (s.pid=e.pid AND s.rid=e.rid AND e.key_name='Age')       LEFT JOIN sample_data f ON (s.pid=f.pid AND s.rid=f.rid AND f.key_name='Occp') ---------------------------------------------------------------------- paweld · 2023-04-03 11:07 something like this:Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT s.*, CASE WHEN sd.key_name='Gender' THEN sd.key_value ELSE NULL END Gender, CASE WHEN sd.key_name='Age' THEN sd.Key_value ELSE NULL END age, CASE WHEN sd.key_name='Occp' THEN sd.Key_value ELSE NULL END occp FROM SAMPLES s LEFT JOIN sample_data sd ON (s.pid=d.pid AND s.rid=d.rid) ---------------------------------------------------------------------- egsuh · 2023-04-03 13:24 QuoteI‘m not even going to pretend understanding that design. Yes, this design is ugly. I've not implemented yet. Just thinking over. Problem is the "property" names may change, so that I cannot fix fields at design time. QuoteIt is IMHO a pivot tranformation I know this for firebird and it says it is not dynamic possible You are right. This is pivot transformation. I know that MS-dbs support this functionality. But I do not need dynamic capability. I can make the SQL statement at Lazarus and query it. @paweld: Your suggestion is interesting. But not does not operate correctly for this case, I'm afraid. It produces three rows if all additional three columns are not null. I have came up with another SQL statement. Cannot decide on which one is better between below and previous one using JOIN.  First principle: the simpler, the better! But which one is simpler? ^^ Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     SELECT s.*,         (SELECT key_value FROM sample_data WHERE pid=s.pid AND rid=s.rid AND key_name='Gender') Gender,         (SELECT key_value FROM sample_data WHERE pid=s.pid AND rid=s.rid AND key_name='Age') Age,         (SELECT key_value FROM sample_data WHERE pid=s.pid AND rid=s.rid AND key_name='Occp') Occp         FROM samples s ---------------------------------------------------------------------- af0815 · 2023-04-03 14:18 For me the working with case and a good sourcecode formatting is ok. If you want it more dynamic and query the possible values first, to generate a case statement on the fly is more simplier and can be better maintained IMHO. ---------------------------------------------------------------------- paweld · 2023-04-03 14:30 @egush: I forgot to groupCode: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT s.*, MAX(CASE WHEN sd.key_name='Gender' THEN sd.key_value ELSE NULL END) Gender,MAX(CASE WHEN sd.key_name='Age' THEN sd.Key_value ELSE NULL END) age, MAX(CASE WHEN sd.key_name='Occp' THEN sd.Key_value ELSE NULL END) occpFROM SAMPLES s LEFT JOIN sample_data sd ON (s.pid=d.pid AND s.rid=d.rid)GROUP BY s.PID, s.RID, s.LOGIN_ID, s.PW, s.EMAIL, s.PHONE, s.FIRST_NAME, s.LAST_NAME ---------------------------------------------------------------------- Zvoni · 2023-04-03 14:55 Quote from: egsuh on April 03, 2023, 03:24:05 pm Yes, this design is ugly. I've not implemented yet. Just thinking over. Problem is the "property" names may change, so that I cannot fix fields at design time. Then define a column in the basetable and save that stuff as a JSON- Record ---------------------------------------------------------------------- egsuh · 2023-04-04 07:07 Quotehen define a column in the basetable and save that stuff as a JSON-Record Yes. Actually I came up with this idea from reading mongoDB stuff. I can save the definitions as JSON-format or simply I can save TStrings.Text (key=value pairs). This has no problem in using Lazarus applications. What I need is to retrieve a subset of records, like samples under 35 years old, etc. So I'd like to define a view for every pid (because the key-names should be the same under a pid), and then use queries on it. Thank you for your concerns. ---------------------------------------------------------------------- egsuh · 2023-04-08 04:44 "JOIN" allows a little simpler syntax. Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   SELECT s.*, Gender, Age, Occp         FROM SAMPLES s        LEFT JOIN (SELECT pid, rid, key_value Gender FROM sample_data WHERE key_name='Gender') USING (pid, rid)        LEFT JOIN (SELECT pid, rid, key_value Age FROM sample_data WHERE key_name='Age') USING (pid, rid)        LEFT JOIN (SELECT pid, rid, key_value Occp FROM sample_data WHERE key_name='Occp') USING (pid, rid); ---------------------------------------------------------------------- rvk · 2023-04-08 06:07 Quote from: egsuh on April 08, 2023, 06:44:39 am"JOIN" allows a little simpler syntax. Your Fiddle https://dbfiddle.uk/WWKXDUap But this doesn't work if there are an unknown number values of key_value. For that you would need to have real PIVOT or use an elaborate EXEC BLOCK. ---------------------------------------------------------------------- Zvoni · 2023-04-08 06:35 Quote from: rvk on April 08, 2023, 08:07:59 amQuote from: egsuh on April 08, 2023, 06:44:39 am"JOIN" allows a little simpler syntax. Your Fiddle https://dbfiddle.uk/WWKXDUap But this doesn't work if there are an unknown number values of key_value. For that you would need to have real PIVOT or use an elaborate EXEC BLOCK. Does Firebird have inhouse JSON-capabilities? Because then it’s easy just to look for the key. Can you even use parameters for that? ---------------------------------------------------------------------- rvk · 2023-04-08 17:16 Quote from: Zvoni on April 08, 2023, 08:35:18 amDoes Firebird have inhouse JSON-capabilities? Because then it’s easy just to look for the key. Can you even use parameters for that? No, not that I'm aware of. But what use would that be to do PIVOT? I was more thinking about something like this: https://dbfiddle.uk/AUKBciR2 Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i WITH CTE AS (SELECT DISTINCT   KEY_NAME,  '(SELECT KEY_VALUE FROM SAMPLE_DATA Y WHERE Y.PID=X.PID AND Y.RID=X.RID AND Y.KEY_NAME=''' || KEY_NAME || ''') ' ZZFROM SAMPLE_DATA)SELECT 'SELECT X.*,'   || CAST( LIST( CTE.ZZ) AS VARCHAR(3000))   ||' FROM SAMPLES X'FROM CTE Which generates a SQL statement regardless of the number of distinct KEY_NAME values. It will result in this: https://dbfiddle.uk/LcK0YtsZ with SQL statement: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT  X.*,  (SELECT KEY_VALUE FROM SAMPLE_DATA Y WHERE Y.PID=X.PID AND Y.RID=X.RID AND Y.KEY_NAME='Age') ,  (SELECT KEY_VALUE FROM SAMPLE_DATA Y WHERE Y.PID=X.PID AND Y.RID=X.RID AND Y.KEY_NAME='Gender') ,  (SELECT KEY_VALUE FROM SAMPLE_DATA Y WHERE Y.PID=X.PID AND Y.RID=X.RID AND Y.KEY_NAME='Occp')FROM SAMPLES X(I see I still need to add fieldnames but you get the idea) And then let that resulting SQL execute in a EXECUTE statement. I'm only not sure how to execute a generated statement in a block and returning the resultset. ---------------------------------------------------------------------- Zvoni · 2023-04-08 22:23 Quote from: rvk on April 08, 2023, 07:16:48 pmNo, not that I'm aware of. But what use would that be to do PIVOT? I was more thinking along the lines that he could stow those records from his detail table as a JSON-Record in a single field of his master table, with only one entry per master record. To pivot that thing he would query that same field 3 times in his select statement though with a different key. And if it‘s possible to parametrize the key, he could avoid the EAV- Antipattern completely ---------------------------------------------------------------------- rvk · 2023-04-08 22:40 He could also just create fields for those KEY_NAMEs in SAMPLES itself if for every record in SAMPLES there is only one record per KEY_NAME. But that goes against the principle of normalizing the database ---------------------------------------------------------------------- Zvoni · 2023-04-09 04:13 I think he wrote early on, that he can‘t do that since the „column“-name (key) might change ---------------------------------------------------------------------- rvk · 2023-04-09 07:10 Quote from: Zvoni on April 09, 2023, 06:13:42 amI think he wrote early on, that he can‘t do that since the „column“-name (key) might change Yes Quote from: egsuh on April 03, 2023, 03:24:05 pmYes, this design is ugly. I've not implemented yet. Just thinking over. Problem is the "property" names may change, so that I cannot fix fields at design time. But when that happens... ANY SQL with fixed values of KEY_NAME will become invalid too. Yes, you can put those fields in a json blob, but you'll loose the ability to do a select on them (if you used the values). And putting only the key_names there is kind of useless because a simple Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT DISTINCT KEY_NAME FROM SAMPLE_DATAwill retrieve the key names too after which you can build the SQL. ---------------------------------------------------------------------- egsuh · 2023-04-10 05:25 Thanks a lot for all the advices. There would be many projects, and the key_names will differ from project to project, but the same within each project. I can manage it by creating or altering a view for each project. Record numbers would not be "very" large. Possibly there might be several hundred K projects, but in most cases hundreds or thousands, at most. Only have to write "saving" procedures. Many thanks again.