All Forums Database
prakash5801 8 posts Joined 02/10
21 Jun 2010
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

monisiqbal 119 posts Joined 07/09
22 Jun 2010

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.

prakash5801 8 posts Joined 02/10
23 Jun 2010

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

Jimm 298 posts Joined 09/07
23 Jun 2010

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

Adeel Chaudhry 773 posts Joined 04/08
23 Jun 2010

Hi,

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

Regards,

MAC

-- If you are stuck at something .... consider it an opportunity to think anew.

prakash5801 8 posts Joined 02/10
28 Jun 2010

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

Jimm 298 posts Joined 09/07
28 Jun 2010

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!

You must sign in to leave a comment.