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
> --
>
>