Query using dynamic column names

Hi All,
I have a table DATA with following 20 columns.
COL1, COL2, COL3, COL4,..... COL20
Another table TEMPLATE has one column COLNAME. The table contains 5 rows with the data COL2, COL5, COL6, COL9, COL12 in the column COLNAME.
Can someone help me to build sql statement to select data from DATA table for only columns listed in the TEMPLATE table. I want to do this using only one select statement. (I know how to do this with stored procedure but thats not what I want)
Also it is possible that row in TEMPLATE table may change.
Hope to get some positive replies.
Reagrds,
HK
If all the columns are of compatible types then you can do it like this:
CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGER
NOT NULL UNIQUE)
INSERT INTO Template VALUES ('COL1',1)
INSERT INTO Template VALUES ('COL2',2)
INSERT INTO Template VALUES ('COL3',3)
SELECT MIN(CASE
WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
/* .. etc */
END),
MIN(CASE
WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
END),
MIN(CASE
WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
END),
...
FROM Data AS D
CROSS JOIN Template AS T
GROUP BY D.col1, D.col2, D.col3, ... other cols
Otherwise use IF statements or dynamic SQL. You could also consider building
the SELECT statement client-side or retrieve all the columns but display
only the required ones to the user.
David Portas
SQL Server MVP
|||Thanks for your help David. But this will not work for me. The example I gave was to explain my problem. But in reality, I will not know all column names in the DATA table, only TEMPLATE table is the guide which has list of fields to select from DATA tabl
e.
Regards,
HK
"David Portas" wrote:

> If all the columns are of compatible types then you can do it like this:
> CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGER
> NOT NULL UNIQUE)
> INSERT INTO Template VALUES ('COL1',1)
> INSERT INTO Template VALUES ('COL2',2)
> INSERT INTO Template VALUES ('COL3',3)
> SELECT MIN(CASE
> WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
> /* .. etc */
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
> END),
> ...
> FROM Data AS D
> CROSS JOIN Template AS T
> GROUP BY D.col1, D.col2, D.col3, ... other cols
> Otherwise use IF statements or dynamic SQL. You could also consider building
> the SELECT statement client-side or retrieve all the columns but display
> only the required ones to the user.
> --
> David Portas
> SQL Server MVP
> --
>
>

Query using dynamic column names

Hi All,
I have a table DATA with following 20 columns.
COL1, COL2, COL3, COL4,..... COL20
Another table TEMPLATE has one column COLNAME. The table contains 5 rows wi
th the data COL2, COL5, COL6, COL9, COL12 in the column COLNAME.
Can someone help me to build sql statement to select data from DATA table fo
r only columns listed in the TEMPLATE table. I want to do this using only on
e select statement. (I know how to do this with stored procedure but thats n
ot what I want)
Also it is possible that row in TEMPLATE table may change.
Hope to get some positive replies.
Reagrds,
HKIf all the columns are of compatible types then you can do it like this:
CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGER
NOT NULL UNIQUE)
INSERT INTO Template VALUES ('COL1',1)
INSERT INTO Template VALUES ('COL2',2)
INSERT INTO Template VALUES ('COL3',3)
SELECT MIN(CASE
WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
/* .. etc */
END),
MIN(CASE
WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
END),
MIN(CASE
WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
END),
..
FROM Data AS D
CROSS JOIN Template AS T
GROUP BY D.col1, D.col2, D.col3, ... other cols
Otherwise use IF statements or dynamic SQL. You could also consider building
the SELECT statement client-side or retrieve all the columns but display
only the required ones to the user.
David Portas
SQL Server MVP
--|||Thanks for your help David. But this will not work for me. The example I gav
e was to explain my problem. But in reality, I will not know all column name
s in the DATA table, only TEMPLATE table is the guide which has list of fiel
ds to select from DATA tabl
e.
Regards,
HK
"David Portas" wrote:

> If all the columns are of compatible types then you can do it like this:
> CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGE
R
> NOT NULL UNIQUE)
> INSERT INTO Template VALUES ('COL1',1)
> INSERT INTO Template VALUES ('COL2',2)
> INSERT INTO Template VALUES ('COL3',3)
> SELECT MIN(CASE
> WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
> /* .. etc */
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
> END),
> ...
> FROM Data AS D
> CROSS JOIN Template AS T
> GROUP BY D.col1, D.col2, D.col3, ... other cols
> Otherwise use IF statements or dynamic SQL. You could also consider buildi
ng
> the SELECT statement client-side or retrieve all the columns but display
> only the required ones to the user.
> --
> David Portas
> SQL Server MVP
> --
>
>

Query using dynamic column names

Hi All,
I have a table DATA with following 20 columns.
COL1, COL2, COL3, COL4,..... COL20
Another table TEMPLATE has one column COLNAME. The table contains 5 rows with the data COL2, COL5, COL6, COL9, COL12 in the column COLNAME.
Can someone help me to build sql statement to select data from DATA table for only columns listed in the TEMPLATE table. I want to do this using only one select statement. (I know how to do this with stored procedure but thats not what I want)
Also it is possible that row in TEMPLATE table may change.
Hope to get some positive replies.
Reagrds,
HKIf all the columns are of compatible types then you can do it like this:
CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGER
NOT NULL UNIQUE)
INSERT INTO Template VALUES ('COL1',1)
INSERT INTO Template VALUES ('COL2',2)
INSERT INTO Template VALUES ('COL3',3)
SELECT MIN(CASE
WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
/* .. etc */
END),
MIN(CASE
WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
END),
MIN(CASE
WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
END),
...
FROM Data AS D
CROSS JOIN Template AS T
GROUP BY D.col1, D.col2, D.col3, ... other cols
Otherwise use IF statements or dynamic SQL. You could also consider building
the SELECT statement client-side or retrieve all the columns but display
only the required ones to the user.
--
David Portas
SQL Server MVP
--|||Thanks for your help David. But this will not work for me. The example I gave was to explain my problem. But in reality, I will not know all column names in the DATA table, only TEMPLATE table is the guide which has list of fields to select from DATA table.
Regards,
HK
"David Portas" wrote:
> If all the columns are of compatible types then you can do it like this:
> CREATE TABLE Template (colname VARCHAR(10) PRIMARY KEY, ordinal_pos INTEGER
> NOT NULL UNIQUE)
> INSERT INTO Template VALUES ('COL1',1)
> INSERT INTO Template VALUES ('COL2',2)
> INSERT INTO Template VALUES ('COL3',3)
> SELECT MIN(CASE
> WHEN T.ordinal_pos = 1 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 1 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 1 AND T.colname='col3' THEN col3
> /* .. etc */
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 2 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 2 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 2 AND T.colname='col3' THEN col3
> END),
> MIN(CASE
> WHEN T.ordinal_pos = 3 AND T.colname='col1' THEN col1
> WHEN T.ordinal_pos = 3 AND T.colname='col2' THEN col2
> WHEN T.ordinal_pos = 3 AND T.colname='col3' THEN col3
> END),
> ...
> FROM Data AS D
> CROSS JOIN Template AS T
> GROUP BY D.col1, D.col2, D.col3, ... other cols
> Otherwise use IF statements or dynamic SQL. You could also consider building
> the SELECT statement client-side or retrieve all the columns but display
> only the required ones to the user.
> --
> David Portas
> SQL Server MVP
> --
>
>

query two tables

hi,

I have two tables,

t1 t2

col1 col1 col2

1 1 0
2 1 0
3 1 1
4 2 0
5 2 0
6 3 1
7 3 1
8 4 1
9 4 1
10 4 1
11 4 1

t2.col1 is the key from t1.col1

I want to retrieve all t1.col1 records which equal to t2.col1 and tb2.col2 has ONLY "1"

the result should be: 3, 4

I try:

select tb1.col1 from t1 as tb1 where tb1.col1 in (select col1 from t2 where col1=tb1.col1 and col2=1 and ...?.)

any help?

my idea is to have it like this:

SELECT top 2 start at 3 t1.col1 from t1 left join t2.col1 on t1.col1 = t2.col2 where t2.col2='1'

hope it helps.

Cheers,

CLIPER

|||

This should do the trick. I don't think you want to use "TOP" as there may be a different number of results each time.

SELECT DISTINCT
tb1.col1
FROM
tb1
INNER JOIN
tb2
ON
tb2.col1 = tb1.col1
GROUP BY
tb1.col1
HAVING
AVG(tb2.col2) = 1

|||

well, just been curious of what he says: "the result should be: 3, 4"

so your query would not be valid if he wants only 3 and 4. :)

Cheers,

CLIPER

|||

Hi CLIPER - try my query - it returns 3 and 4.

|||

thanks for the reply.

Sohnee's trick: AVG(tb2.col2) = 1

this is not valid, as tb2.col2 is "bit" data type

1 = true, 0 = false.

nice try, but still need better solution...|||

well, I should look into it later. :) thanks!

|||

If it's a bit type, this should work.

SELECT DISTINCT
tb1.col1
FROM
tb1
INNER JOIN tb2 ON tb2.col1 = tb1.col1
WHERE
tb1.col1 NOT IN (SELECT tb2.col1 FROM tb2 WHERE tb2.col2 = 0)

|||

Thanks, Sohnee, you just save my day...