Getting the query based on Cols

Question

Getting the query based on Cols

pawan.boyina

SSC-Addicted

Points: 494
More actions
March 9, 2011 at 9:50 am

#233091

Please provide a query which would be similar to......which would be helpful for table containing more number of columns and you want to remove few.
select * from a table where column_name not in('col1','col2','col3')

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

pawan.boyina (3/9/2011)
Please provide a query which would be similar to......which would be helpful for table containing more number of columns and you want to remove few.
select * from a table where column_name not in('col1','col2','col3')

What it means?

What you want to remove the above question is not understandable.

Thanks
Parthi

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

pawan.boyina (3/9/2011)
Please provide a query which would be similar to......which would be helpful for table containing more number of columns and you want to remove few.
select * from a table where column_name not in('col1','col2','col3')

Are you trying to only select certain columns? In SSMS go to the object explorer and click and drag the "Columns" folder under the table you want to a query window. It will drop all the column names there.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pawan.boyina SSC-Addicted Points: 494 More actions · Answer 3

for a table containing n cols (col1,col2.....coln) i want to exclude col1,col2

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Use the technique I showed you above and then just remove the columns you don't want to show.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Can NOT be done as u were saying ; WHERE clause filters ROWS, not COLUMNS. You will have to manually write the required columns in the SELECT list.

Else, if the data set is not big (nor huge nor humongous) , then u can UNPIVOT and then PIVOT back the results; That involves a looooooot of resource wastage and time .

If u are interested, i can show u a sample.

bvsc Ten Centuries Points: 1258 More actions · Answer 6

If you want You can eliminate the column values by using the condition in where clause... but not columns.

select col1,col2,col3 from tablename where (condition will be here)

If you want eliminate particular column(for example col2) then dont call that in your select statement use query some thing like....

select col1,col3 from tablename (use where clause as per requirement)

That is what I understood, If not

what you exactly looking for ?

Reo Hall of Fame Points: 3774 More actions · Answer 7

Hi,

It is possible and look like :

DECLARE @STR Varchar(max)

DECLARE @TableName Varchar(256)

SET @TableName = 'YourTableName'

SELECT @STR= ISNULL(@Str,'') + ', ' + Column_Name

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TableName

AND Column_Name NOT IN ('col1','co2')

SET @STR = 'SELECT ' + SubString(@str,2,LEN(@str)) + ' FROM ' + @TableName

EXECUTE (@str)

Ram
MSSQL DBA