** I need Select Query except one value

Question

3.00/5 (2 votes)

See more:

, +

Hi friends,

i've 60 columns in my table

i need to select all values except one value.

SQL

select * from mytable  will select all 60 columns.

But i need 59 columns only. for example i want to neglect the 60th column value.

so i need to write a query like

SQL

select column1,column2....column59 from mytable

or some other easy way is there??

Plz tell ur suggestion Friends..

Posted 21-Jun-13 23:34pm

VIP Venkatesan

Updated 25-Jun-13 0:31am

gvprabu

v2

Add a Solution

5 solutions

Solution 2

You need to write query like select column1,column2....column59 from mytable.

It is a best practice to always name the columns in a query. This helps when new columns are added or old columns are deleted.

With SELECT *, a new column recently added to the table would be retrieved and the program may not be able to handle it properly.

With SELECT *, if a column is deleted from the table, the application may fault in strange ways and require debugging. When SELECT column1,column2,column3 format is used the SQL Query will fail with a missing column error.

Posted 21-Jun-13 23:52pm

Mike Meinz

Comments

VIP Venkatesan 22-Jun-13 5:59am

but sql has a keyword "except". what is the use of that. we can't do this using "except" keyword ah?

f0r example select * from mytable except(column60)..

i know this query is wrong. but am asking you we can't done this using except keyword ah??

Mike Meinz 22-Jun-13 6:21am

The documentation says that the EXCEPT clause can only be used with JOINs and it is to be used for a different purpose than your stated purpose.

Solution 4

Hi,

better you select columns in the traditional way like select col1,col2,.... from table
(as it will be faster then the SP below)
or
you can try following sp,

Create PROCEDURE [dbo].[TEST]   
 
 @Schema varchar(10),
 @Table varchar(20),   
 @ColumnExclude Varchar(20),
 @condition Varchar(max)
AS  
Begin

declare @column varchar(max)
declare @query varchar(Max)

set @column=(SELECT COLUMN_NAME +',' 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @Table AND TABLE_SCHEMA=@Schema and COLUMN_NAME !=@ColumnExclude 
FOR XML PATH(''))

set @column= LEFT(@column,LEN(@column)-1)

set @query = 'select '+@column+' from ' +@Schema+'.'+@Table+'(nolock)'+'where '+@condition
exec( @query)

END

and then use it like

exec test 'Schemaname','TableName','ColumnName_toexclude','condition'

for example

exec test 'dbo','emp','salary','name=''john'''

hope it will help...

Happy coding.. :)

Posted 24-Jun-13 0:40am

Adarsh chauhan

Comments

VIP Venkatesan 24-Jun-13 8:49am

create proc sp_test
as
declare @column nvarchar(50)

set @column= (SELECT COLUMN_NAME +','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'machine' AND TABLE_SCHEMA='dbo' and COLUMN_NAME !='mid'
FOR XML PATH(''))

declare @query nvarchar(max)
set @query = 'select distinct '+@column+' from machine'

i've tried this query. execution was successful..
thn i did "exec sp_test" but can't view my data..

and my xml values looks like column1,column2,column3,

so my @query looks like select column1,column2,column3, from machine
"so we will face error near from bcoz of ',' ".

need solution for this..

i hope u will solve my problem..

Adarsh chauhan 25-Jun-13 1:03am

create proc sp_test
as
declare @column nvarchar(50)
set @column= (SELECT COLUMN_NAME +',' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'machine' AND TABLE_SCHEMA='dbo' and COLUMN_NAME !='mid'
FOR XML PATH('')) declare @query nvarchar(max)

set @column= LEFT(@column,LEN(@column)-1)// this will remove the, from the last and ur prob. will be solved

set @query = 'select distinct '+@column+' from machine'

VIP Venkatesan 25-Jun-13 6:55am

Thank u!

Adarsh chauhan 25-Jun-13 7:06am

welcome buddy :)

Solution 1

you need to provide ID or some condition to remove the 60th table

SQL

select * from mytable where mytable.ID=60

hope this helps you..

Posted 21-Jun-13 23:35pm

salah9

Comments

VIP Venkatesan 22-Jun-13 5:49am

i can't get you..

let my table like

column1 column2 column3 column 4 ................. column 59 column 60
1 2 2 2 3 3
1 2 2 2 3 4

from the above table and values
1) i don't want column 60th values.
2) i need distinct values only.

if i use a query like "select distinct * from mytable" it will give 2 rows values.

i don't want 60th value.
so,
now i'm using a query like "select distinct column1,column2....column59 from mytable". it returns 1 row oly.

i don't want to write all the 59columns.. is there any way to select all except column60???

Solution 3

Here you go. This is the sample data from "vvexample(td).txt" (Copy and paste it)

1 1 0 1 1 1 1 0 1 1 1 0 0 0 1 0 0 0 1 1 0 0 1 0 1 0 0 0 0 1 0 1 1 1 0 0 0 1 0 0 1 1 0 1 0
2 1 0 1 1 1 1 0 1 1 1 0 0 0 1 0 0 0 0 0 0 1 1 1 1 1 1 0 1 1 0 0 1 0 0 1 0 1 1 0 1 0 1 1 0
3 1 0 1 1 1 1 0 1 1 1 0 0 0 0 1 1 1 0 1 1 0 1 0 1 1 0 1 1 0 0 0 0 0 0 0 0 1 0 1 0 1 1 0 0
4 1 0 1 1 1 1 0 1 1 1 0 0 0 0 1 1 1 0 1 0 1 1 0 1 0 0 1 0 0 1 0 1 1 0 0 1 0 0 0 0 0 0 0 1
5 1 0 1 1 1 1 0 1 1 1 0 0 0 0 1 0 1 1 1 0 0 1 1 1 0 1 1 0 0 1 0 0 0 1 1 0 1 0 0 1 1 1 0 0
6 1 0 1 1 1 1 0 1 1 1 0 0 0 0 1 0 1 0 0 0 1 0 0 0 0 1 0 1 0 0 0 1 0 1 1 0 1 1 1 0 1 0 1 1
7 1 0 1 1 1 1 0 1 1 0 1 0 0 1 1 1 1 0 1 1 0 1 0 1 1 0 1 0 1 0 1 1 1 1 0 0 1 0 0 0 1 0 1 1
8 1 0 1 1 1 1 0 1 1 0 1 0 0 1 1 1 0 1 1 0 0 1 0 1 0 1 1 0 1 0 1 1 1 0 0 0 0 0 0 0 1 1 0 1
9 1 0 1 1 1 1 0 1 1 0 1 0 0 1 1 0 1 1 1 1 0 1 1 1 0 0 1 0 1 1 0 1 0 0 1 1 1 1 1 1 1 1 1 0
10 1 0 1 1 1 1 0 1 1 0 1 0 0 1 1 0 0 1 0 0 0 1 1 0 0 1 0 1 0 0 1 1 1 1 1 1 0 0 1 1 1 1 1 1

Mind you, those zeroes should be tab-delimited (td), so go ahead and inject some {tab} in there before trying the next couple of blocks.
Next CREATE a table:

USE [cpqaAnswers]
GO
CREATE TABLE [cpqa].[tbl_VV_AllExceptOne_](
	[45] [int] NULL,
	[44] [int] NULL,
	[43] [int] NULL,
	[42] [int] NULL,
	[41] [int] NULL,
	[40] [int] NULL,
	[39] [int] NULL,
	[38] [int] NULL,
	[37] [int] NULL,
	[36] [int] NULL,
	[35] [int] NULL,
	[34] [int] NULL,
	[33] [int] NULL,
	[32] [int] NULL,
	[31] [int] NULL,
	[30] [int] NULL,
	[29] [int] NULL,
	[28] [int] NULL,
	[27] [int] NULL,
	[26] [int] NULL,
	[25] [int] NULL,
	[24] [int] NULL,
	[23] [int] NULL,
	[22] [int] NULL,
	[21] [int] NULL,
	[20] [int] NULL,
	[19] [int] NULL,
	[18] [int] NULL,
	[17] [int] NULL,
	[16] [int] NULL,
	[15] [int] NULL,
	[14] [int] NULL,
	[13] [int] NULL,
	[12] [int] NULL,
	[11] [int] NULL,
	[10] [int] NULL,
	[09] [int] NULL,
	[08] [int] NULL,
	[07] [int] NULL,
	[06] [int] NULL,
	[05] [int] NULL,
	[04] [int] NULL,
	[03] [int] NULL,
	[02] [int] NULL,
	[01] [int] NULL
) ON [PRIMARY]

This is ugly to look at but is useful to provide example. Now fill the table:

BULK INSERT [cpqaAnswers].[cpqa].[tbl_VV_AllExceptOne_]
	FROM 'C:\Users\VV\vvexample(td).txt'

Note how I'm referencing two things a SCHEMA and a DATABASE ([cpqa] and [cpqaAnswers] ... substitute what you've got where necessary, right? I'll continue to use what I call my example "origin" for ease of use from this end:

USE [cpqaAnswers]
GO
--CREATE SCHEMA [cpqa]

DECLARE @intFixed [int]
SET @intFixed = 2

DECLARE @intCountColumn [int]
SET @intCountColumn = (SELECT count(*) FROM information_schema.columns WHERE table_name = 'tbl_VV_AllExceptOne_')

DECLARE @intVariable [int]
SET @intVariable = 0

DECLARE @strHead [nvarchar](MAX)
SET @strHead = 'SELECT '

DECLARE @strTail [nvarchar](MAX)
SET @strTail = 'FROM [cpqaAnswers].[cpqa].[tbl_VV_AllExceptOne_]'

DECLARE @strConcat [nvarchar](MAX)
SET @strConcat = ''

DECLARE @intLoop [int]
SET @intLoop = 1

DECLARE @strExec [nvarchar](MAX)
SET @strExec = '' 

DECLARE @strGrow [nvarchar](MAX)
SET @strGrow = ''

That's the preamble to the WHILE clause, the loop where the stuff happens. This is basic brute force string concatenation:

WHILE @intLoop < @intCountColumn
	BEGIN
		SET @strConcat = @strHead + '[' + LEFT(REPLICATE('0',@intFixed),@intFixed-LEN(@intLoop))+CAST(@intLoop As [nvarchar](32)) + '] ' + @strTail
		SET @strGrow = @strConcat + ',' + @strGrow
		SET @intLoop = @intLoop + 1	
       END
	SET @strExec = @strHead + LEFT(@strGrow,LEN(@strGrow)-1) + @strTail 
	EXECUTE (@strExec)

The requirement which is made really happens at the beginning of the WHILE where @intCountColumn doesn't get the extra "1" added to it to keep @intLoop going until all columns are accounted for in the concatenation @str. Stopping short essentially keeps the return to n-1.

Posted 22-Jun-13 9:11am

RedDk

Updated 22-Jun-13 9:16am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Accepted Answer · 2013-06-25T00:31:00

Hi...

Check the below sample Query.

SQL

-- Table Creation
CREATE TABLE Test(Col1 INT,Col2 INT,Col3 INT,Col4 INT,Col5 INT)
-- Local variable Declaration
DECLARE @TableName VARCHAR(100), @SqlQuery NVARCHAR(1000), @ExcludeColPos INT

-- Assign default values
SELECT @TableName='Test',	@ExcludeColPos=2 -- Here use 1 to 5

-- Insert Sample data
INSERT INTO Test (Col1,Col2,Col3,Col4,Col5)
SELECT 1,2,3,4,5,
UNION ALL
SELECT 6,7,8,9,10
UNION ALL 
SELECT 11,12,13,14,15

-- Combine all columns except exclude column
SELECT @SqlQuery = COALESCE(@SqlQuery+',','')+Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name=@TableName AND Ordinal_position <> @ExcludeColPos

-- Frame SQL Select Statement
SELECT @SqlQuery='SELECT ' + @SqlQuery +' FROM '+ @TableName
SELECT @SqlQuery=@SqlQuery+' SELECT COUNT(1) FROM '+ @TableName

PRINT @SqlQuery

-- Execute the Query 
EXECUTE sp_executesql @SqlQuery

-- Drop sample table
DROP TABLE Test

Regards,
GVPrabu