select certain columns

[ter79]

I have two tables that I need to use to create a specific select statement.

One table (AllData) contains approx 45 columns and data in each column. The other table (ColumnList) holds the names of the columns and a flag.

What I need to do is create a select statement against AllData that will only use the columns indicated in ColumnList

Example
AllData table

ColumnNames:
FirstName
LastName
DOB
Gender
State
Zip


ColumnList Table
ColumnName Use
FirstName True
LastName True
DOB False
Gender True


Therefore my select statement would be something like

select FirstName, LastName from AllData

since the "Use" flag can change I need to make this dynamic

Hope this makes sense

 

[ter79]

I forgot to add this part... I still need to return the PK from each row, even though that field is not defined in the ColumnList table

 

[bborissov]

DECLARE @sql nvarchar(max)-- change it 4000 if you use SQL 2000

SET @sql = N'SELECT PrimaryKey'
SELECT @sql = @sql + ','+ColumnName
FROM ColumnList
WHERE [Use] = 1 -- bit field, isn't it?

SET @sql = @sql + N' FROM AllData'
EXEC sp_executesql @sql

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ter79]

Thanks... Worked perfectly!!!!