asela115 (2/24/2013)
Hi,How can i select columns of a table dynamically through a select statement if the column name need to be changed based on the value selected in a Combo Box in a different form.
it's like select column A from table X if combo box selection is A, select column B from same table if combo box selection is B etc..
Please assist
Regards,
asela115
I would use dynamic sql like this:
First some sample data
IF OBJECT_ID('#TestTable') IS NOT NULL
DROP TABLE #TestTable
CREATE TABLE #TestTable (
ID INT IDENTITY(1,1) NOT NULL,
EmplID INT NULL,
MgrID INT NULL,
CustID INT NULL,
LocID INT NULL,
PRIMARY KEY (ID))
INSERT INTO #TestTable
SELECT 111,1011,10001,789 UNION
SELECT 222,1022,10002,890 UNION
SELECT 333,1033,10003,789 UNION
SELECT 444,1044,10004,890 UNION
SELECT 555,1055,10005,789 UNION
SELECT 666,1066,10006,890 UNION
SELECT 777,1077,10007,789 UNION
SELECT 888,1088,10008,890 UNION
SELECT 999,1099,10009,678
SELECT * FROM #TestTable
The code:
DECLARE
@Response1 VARCHAR(50)
,@Response2 VARCHAR(50)
,@LocID INT
,@strSQL VARCHAR(MAX)
SET @Response1 = 'EmplID'
SET @Response2 = 'CustID'
SET @LocID = 890
SET @strSQL = '
SELECT
t.ID
,'+@Response1+' AS '+@Response1+'
,'+@Response2+' AS '+@Response2+'
FROM
#TestTable AS t
WHERE
LocID = '+CAST(@LocID AS VARCHAR(5))+'
'
EXEC(@strSQL)
The output:
IDEmplIDCustID
222210002
444410004
666610006
888810008