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 urgent

my table is:

col1 col2 col3 col4 col5 col6
IT3 njfghj 0 0 0 Blue
IT3 njfghj 0 0 Simple 0
IT3 njfghj 0 35 0 0
IT3 njfghj Lee 0 0 0

i want the result as

col1 col2 col3 col4 col5 col6
IT3 njfghj Lee 35 Simple Blue

how is this posible??Try something like this:

SELECT Col1, Col2, MAX(Col3) AS Col3, MAX(Col4) AS Col4, MAX(Col5) AS Col5, MAX(Col6) AS Col6
FROM TableName
GROUP BY Col1, Col2

Regards,
Thomas