Forum > Database
[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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
something like this:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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)
Navigation
[0] Message Index
[#] Next page