[CLOSED] Firebird-SQL

[egsuh]

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][+][-]

1. CREATE TABLE SAMPLES(
2.   PID INTEGER,
3.   RID INTEGER,
4.   LOGIN_ID VARCHAR(20),
5.   PW VARCHAR(20),
6.   EMAIL VARCHAR(25),
7.   PHONE VARCHAR(20),
8.   FIRST_NAME VARCHAR(20),
9.   LAST_NAME VARCHAR(20)
10. );
11.  
12. CREATE TABLE SAMPLE_DATA(
13.   PID INTEGER,
14.   RID INTEGER,
15.   KEY_NAME VARCHAR(20),
16.   KEY_VALUE VARCHAR(120)
17. );
18.  
19. /* select statement */
20. SELECT s.*, d.key_value Gender, e.Key_value age, f.Key_value occp FROM SAMPLES s
21.  
22.     LEFT JOIN (SELECT pid, rid, key_value FROM sample_data WHERE key_name='Gender') d
23.     ON s.pid=d.pid AND s.rid=d.rid
24.    
25.     LEFT JOIN (SELECT pid, rid, key_value FROM sample_data WHERE key_name='Age') e
26.     ON s.pid=e.pid AND s.rid=e.rid
27.    
28.     LEFT JOIN (SELECT pid, rid, key_value FROM sample_data WHERE key_name='Occp') f
29.     ON s.pid=f.pid AND s.rid=f.rid
30.  

[Zvoni]

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]

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]

Same result, but using indexes on pid, rid and key_name in sample_data, if exists.

Code: Pascal  [Select][+][-]

1. SELECT s.*
2.   , d.key_value Gender, e.Key_value age, f.Key_value occp
3. FROM SAMPLES s
4.  
5.     LEFT JOIN sample_data d  ON (s.pid=d.pid AND s.rid=d.rid AND d.key_name='Gender')
6.    
7.     LEFT JOIN sample_data e ON (s.pid=e.pid AND s.rid=e.rid AND e.key_name='Age')
8.    
9.     LEFT JOIN sample_data f ON (s.pid=f.pid AND s.rid=f.rid AND f.key_name='Occp')
10.  

[paweld]

something like this:

Code: SQL  [Select][+][-]

1. SELECT s.*, CASE WHEN sd.key_name='Gender' THEN sd.key_value ELSE NULL END Gender,
2. 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
3. FROM SAMPLES s LEFT JOIN sample_data sd ON (s.pid=d.pid AND s.rid=d.rid)

[egsuh]

I‘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.

It 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][+][-]

1.     SELECT s.*,
2.         (SELECT key_value FROM sample_data WHERE pid=s.pid AND rid=s.rid AND key_name='Gender') Gender,
3.         (SELECT key_value FROM sample_data WHERE pid=s.pid AND rid=s.rid AND key_name='Age') Age,
4.         (SELECT key_value FROM sample_data WHERE pid=s.pid AND rid=s.rid AND key_name='Occp') Occp
5.         FROM samples s

[af0815]

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]

@egush: I forgot to group

Code: SQL  [Select][+][-]

1. SELECT s.*, MAX(CASE WHEN sd.key_name='Gender' THEN sd.key_value ELSE NULL END) Gender,
2. 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) occp
3. FROM SAMPLES s LEFT JOIN sample_data sd ON (s.pid=d.pid AND s.rid=d.rid)
4. GROUP BY s.PID, s.RID, s.LOGIN_ID, s.PW, s.EMAIL, s.PHONE, s.FIRST_NAME, s.LAST_NAME

[Zvoni]

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]

hen 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]

"JOIN" allows a little simpler syntax.

Code: SQL  [Select][+][-]

1.   SELECT s.*, Gender, Age, Occp
2.         FROM SAMPLES s
3.         LEFT JOIN (SELECT pid, rid, key_value Gender FROM sample_data WHERE key_name='Gender') USING (pid, rid)
4.         LEFT JOIN (SELECT pid, rid, key_value Age FROM sample_data WHERE key_name='Age') USING (pid, rid)
5.         LEFT JOIN (SELECT pid, rid, key_value Occp FROM sample_data WHERE key_name='Occp') USING (pid, rid);

     

[rvk]

"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]

"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]

Does 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][+][-]

1. WITH CTE AS (
2. SELECT DISTINCT
3.   KEY_NAME,
4.   '(SELECT KEY_VALUE FROM SAMPLE_DATA Y WHERE Y.PID=X.PID AND Y.RID=X.RID AND Y.KEY_NAME=''' || KEY_NAME || ''') ' ZZ
5. FROM SAMPLE_DATA
6. )
7. SELECT 'SELECT X.*,'
8.    || CAST( LIST( CTE.ZZ) AS VARCHAR(3000))
9.    ||' FROM SAMPLES X'
10. 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][+][-]

1. SELECT
2.   X.*,
3.   (SELECT KEY_VALUE FROM SAMPLE_DATA Y WHERE Y.PID=X.PID AND Y.RID=X.RID AND Y.KEY_NAME='Age') ,
4.   (SELECT KEY_VALUE FROM SAMPLE_DATA Y WHERE Y.PID=X.PID AND Y.RID=X.RID AND Y.KEY_NAME='Gender') ,
5.   (SELECT KEY_VALUE FROM SAMPLE_DATA Y WHERE Y.PID=X.PID AND Y.RID=X.RID AND Y.KEY_NAME='Occp')
6. 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]

No, 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