Dynamic SELECT result column names

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Dynamic SELECT result column names

Staffan Tylen
I'm struggling with a solution that allows me to dynamically define the
name of a result column. Consider the following:

CREATE TABLE TAB1 (A1,B1);
INSERT INTO TAB1 VALUES('AA1','BB1');
WITH PREFIX(PFX) AS (SELECT 'A')
SELECT (SELECT PFX||'1' FROM PREFIX) FROM TAB1;

What I want to happen here is that the values in column A1 ('AA1' in this
case) be returned but instead I get 'A1' in return.

Is there a way to achieve this?

Staffan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SELECT result column names

Igor Tandetnik-2
On 5/26/2014 10:09 AM, Staffan Tylen wrote:
> What I want to happen here is that the values in column A1 ('AA1' in this
> case) be returned

You can't do that. SQL allows you to manipulate values, but not names.
All table and column names are set in stone when the query is prepared
and the execution plan is determined.

You are effectively doing " select 'A1' from TAB1; " which of course is
very different from " select A1 from TAB1; "
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SELECT result column names

Staffan Tylen
Thanks, that was what I suspected. Well, well, life is not perfect ;)



On Mon, May 26, 2014 at 4:50 PM, Igor Tandetnik <[hidden email]> wrote:

> On 5/26/2014 10:09 AM, Staffan Tylen wrote:
>
>> What I want to happen here is that the values in column A1 ('AA1' in this
>> case) be returned
>>
>
> You can't do that. SQL allows you to manipulate values, but not names. All
> table and column names are set in stone when the query is prepared and the
> execution plan is determined.
>
> You are effectively doing " select 'A1' from TAB1; " which of course is
> very different from " select A1 from TAB1; "
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SELECT result column names

Simon Slavin-3
In reply to this post by Igor Tandetnik-2

On 26 May 2014, at 3:50pm, Igor Tandetnik <[hidden email]> wrote:

> You can't do that. SQL allows you to manipulate values, but not names. All table and column names are set in stone when the query is prepared and the execution plan is determined.

And the fact that you're even trying to do it may be a sign that your database needs refactoring.  If column names are variable then they should be column values.  So a table like

ID COL1 COL2 COL3 COL4
1 876 67 68 54
2 54 875 46 45
3 57 445 67 4

should actually be

ID PROP VALUE
1 1 876
1 2 67
1 3 68
1 4 54
2 1 54
...

with a primary key of (ID, PROP).

That means you can select anything you want and you don't have to try to treat a fixed thing as variable.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SELECT result column names

Staffan Tylen
I fully agree with this. The fact is that I'm using a database from a third
party.

TBL1
PFX1_COL1  PFX1_COL2  PFX3_COL3 etc

TBL2
PFX2_COL1  PFX2_COL2  PFX3_COL3 etc.

The first column has the name of the table. The next  x number of columns
contain data universal to all tables, such as date and time stamps. The
rest of the columns are unique for each table. I've built a 'master' table
containing the 1st and the common columns which I use for selecting data.
But at occasions I need to get hold of data from the original table and the
only way to know where that data can be found is to look at the first
column which contains the name of the original table. And to make things
even worse, the column names are all different so joining tables is not
doable - to do that you need to know the name of the tables to join and the
name of the columns to join on, and all this dynamically. The number of
tables is close to 100.

This is why I've tried to get SQL to dynamically resolve the table and
column names using the example I posted. But as that doesn't work I need to
look for a different solution. I hope all this is not too vague to
understand.

Staffan





On Mon, May 26, 2014 at 7:04 PM, Simon Slavin <[hidden email]> wrote:

>
> On 26 May 2014, at 3:50pm, Igor Tandetnik <[hidden email]> wrote:
>
> > You can't do that. SQL allows you to manipulate values, but not names.
> All table and column names are set in stone when the query is prepared and
> the execution plan is determined.
>
> And the fact that you're even trying to do it may be a sign that your
> database needs refactoring.  If column names are variable then they should
> be column values.  So a table like
>
> ID      COL1    COL2    COL3    COL4
> 1       876     67      68      54
> 2       54      875     46      45
> 3       57      445     67      4
>
> should actually be
>
> ID      PROP    VALUE
> 1       1       876
> 1       2       67
> 1       3       68
> 1       4       54
> 2       1       54
> ...
>
> with a primary key of (ID, PROP).
>
> That means you can select anything you want and you don't have to try to
> treat a fixed thing as variable.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SELECT result column names

Simon Slavin-3

On 26 May 2014, at 9:17pm, Staffan Tylen <[hidden email]> wrote:

> This is why I've tried to get SQL to dynamically resolve the table and
> column names using the example I posted. But as that doesn't work I need to
> look for a different solution. I hope all this is not too vague to
> understand.

No.  That was a good explanation.  The only solution I can suggest is that you don't use a fixed SQL command but instead remember that the SQL command you pass to _exec() or _prepare() is just another C string.  You can use C commands to concatenate that string from components.  It doesn't have to be fixed in your program.  So do (untested pseudocode)

theCommand = "SELECT PFX"
theCommand += thePFXNum
theCommand += "_COL"
theCommand += theCOLNum
theCommand += " FROM "
theCommand += theTableName

and then pass that to _prepare() or _exec() or whatever you're doing.

If your legacy tables don't change often (or if you have plenty of runtime available each time on changes) I might just make a conversion routine which takes data from the legacy tables and makes some tables with it which have a more convenient schema.  Then run that routine when needed.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SELECT result column names

R Smith
In reply to this post by Staffan Tylen

On 2014/05/26 22:17, Staffan Tylen wrote:

> I fully agree with this. The fact is that I'm using a database from a third
> party.
>
> TBL1
> PFX1_COL1  PFX1_COL2  PFX3_COL3 etc
>
> TBL2
> PFX2_COL1  PFX2_COL2  PFX3_COL3 etc.
>
> The first column has the name of the table. The next  x number of columns
> contain data universal to all tables, such as date and time stamps. The
> rest of the columns are unique for each table. I've built a 'master' table
> containing the 1st and the common columns which I use for selecting data.
> But at occasions I need to get hold of data from the original table and the
> only way to know where that data can be found is to look at the first
> column which contains the name of the original table. And to make things
> even worse, the column names are all different so joining tables is not
> doable - to do that you need to know the name of the tables to join and the
> name of the columns to join on, and all this dynamically. The number of
> tables is close to 100.
>
> This is why I've tried to get SQL to dynamically resolve the table and
> column names using the example I posted. But as that doesn't work I need to
> look for a different solution. I hope all this is not too vague to
> understand.

Nope, understood perfectly. It's a classic case of trying to fix bad table design with worse SQL statements and it is not uncommon
because a lot of people are in your shoes viz. inheriting badly designed databases to work from and try to make sense of the layouts.

As others have already pointed out, this isn't SQL's intention or job and you can't do the thing you want to do like that. May I
suggest something that could possibly work for you?

Interestingly, the original badly designed DB with all those non-sensible column names is a much better design than your newer table
which has the column name as a field entry IF they keep only the same kinds of data in the same columns (which I believe they do if  
understood you correct). What really you should do is simply extract it all to a translation table with really proper names.

You can even do this with a View by simply adding proper column names so that if you have a DB like this: (I'm going to steal
Simon's example for laziness):

ID COL1 COL2 COL3 COL4
1 876 67 68 54
2 54 875 46 45
3 57 445 67 4

You can declare a view like this:

CREATE VIEW TView AS SELECT ID, COL1 AS DeviceName, COL2 AS Position, COL3 AS DeviceStatus, COL4 AS UserName FROM BadTable;

(or whatever else you want those column names to be)

And then run your queries like this:
SELECT DeviceName, DeviceStatus, etc. FROM TView WHERE DeviceStatus=xxx;

Which is more sensible and will cause you a LOT less work in future.

If you have already put in a great deal of effort to use your new slimmer table, then the above will require a lot of reworking and
options are bleak.

A last option (which is silly but will work if this is a post-traumatic fix rather than a design decision)  would be a query with a
very large CASE statement which checks the column (in the new Table) for the name of the column and then compares or extracts that
specific column from the Old table. Keeping with the examples above and imagining your new table has a column called "PropertyName"
which specifies what column the data is from:

SELECT ID,
   CASE N.PropertyName WHEN 'DeviceName' THEN O.COL1 WHEN 'Position' THEN O.COL2 WHEN 'DeviceStatus' THEN O.COL3 ELSE 'Invalid
Column Name' END AS PropertyValue,
   (other columns...)
FROM TheNewTable AS N
LEFT JOIN TheOldTable AS O ON O.ID=N.ID
WHERE xxx;

But this will be ugly SQL, it will be convoluted (especially for 100 columns, although you may not need all 100) and I am unsure as
to how fast it will execute - but worth a shot if it's just a quick fix needed.

Ref:
http://www.sqlite.org/lang_expr.html#case


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SELECT result column names

Staffan Tylen
Thanks both for the tips. Well, it looks like I'm barking up the wrong tree
here, the suggestions might work in principle but unfortunately wont handle
changes to the underlying database without requiring changes to whatever
solution is applied.


> But this will be ugly SQL, it will be convoluted (especially for 100
> columns, although you may not need all 100) and I am unsure as to how fast
> it will execute - but worth a shot if it's just a quick fix needed.
>

Yes, this gets ugly especially as we are talking about almost 100 tables
with probably 100+ columns in each. I guess I need to sleep on this for a
moment.

Staffan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users