How to select values based on variable name and primary key using SQL

Database
Enthusiast

How to select values based on variable name and primary key using SQL

How could I select the values using "column name" and primary key (row) information of a table using SQL.

For example Table1 contains 3 columns (TrackID, var1 and var2) with values:

Table1
__________________
TrackID var1 var2
1 3 1.2
2 2 1
3 8 2.3

In Table2 have Attribute ("column name of Table 1") and TracID. How could I select the values from Table 1 based on input from Table 2.

Table2
________

Attrbute TrackID
var1 1
var1 2
var1 3
var2 1
var2 2
var2 3

The result will be

Result
----------
3
2
8
1.2
1
2.3

Regards,

Prakash
Tags (2)
6 REPLIES
Enthusiast

Re: How to select values based on variable name and primary key using SQL

Try using the CASE expression when joining both the tables. Of course it won't be a feasible solution if you have too many columns in table 1.
Enthusiast

Re: How to select values based on variable name and primary key using SQL

Thanks monisiqbal, Yeah, I do have lot of columns in Table 1 thus CASE expression may not be the best way of solving the problem
Enthusiast

Re: How to select values based on variable name and primary key using SQL

I would go with CASE first too, but compare it against this plan B (Union) with your volumes:

Select Var1
From Table1
Join Table2
On Table2.TrackId = Table1.TrackId
And Table2.Attrbute = 'Var1'
Union
Select Var2
From Table1
Join Table2
On Table2.TrackId = Table1.TrackId
And Table2.Attrbute = 'Var2'
Order By 1
;

Let me know volumes and outcome.

Thanks
Teradata Employee

Re: How to select values based on variable name and primary key using SQL

Hi,

What sort of data-volume is expected in this table?

Regards,

MAC
Enthusiast

Re: How to select values based on variable name and primary key using SQL

Table 1 consist of 170 column and 500 instances which will result in 85000 rows in Table 2.
Enthusiast

Re: How to select values based on variable name and primary key using SQL

Go for the CASE. Volumes are trivial (if you are not using Teradata Express!) and it is easier to build.
Something like:

.Export Report file = bldextr.sql
.Format Off
.Foldlne on all
.Width 255

Select
CASE WHEN ROW_NUMBER() OVER (ORDER BY Table2.Attrbute, Table2.TrackId) = 1
THEN 'Select CASE' ELSE ' ' END||
' WHEN Table1.TrackId = '|| Trim(Table2.TrackId)||' AND Table2.Attrbute = '''||Trim(Table2.Attrbute)||''' THEN '||Table2.Attrbute||
CASE WHEN ROW_NUMBER() OVER (ORDER BY Table2.Attrbute DESC, Table2.TrackId DESC) = 1
THEN ' END As ResultVar ' ELSE ' ' END (Title '')
From Table2
Order By Table2.Attrbute, Table2.TrackId
;
Select
'From Table1 JOIN Table2 ' (Title '')
,' On Table1.TrackId = Table2.TrackId' (Title '')
, 'Where ResultVar is not Null Order By 1;' (Title '')
;

.Export Reset
.Run File=bldextr.sql

BTW
Have a word with your Data Modellers. Your data structure is third ABnormal form!