Returning columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Returning columns

Author

Topic

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-12-20 : 11:57:44

Gurus
I have a table with 20 cols..now i want to return only those cols whose value =1.Do we use any system SP?
Please help
Regards
Nitin

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-20 : 12:06:43

Columns do not have values. Specific column rows have values.

Please explain exactly what you are trying to do.

CODO ERGO SUM

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-12-20 : 12:11:29

Hi
Suppose i have a table with 20 columns...and i have only one row in the table.now i want to retrive only those columns whose value is 1..

Like
id empid deptid salarid
1 0 1 1

Now i want to return all those colukmns where value =1
the output should be id,deptid,salarid where value =1

Regards
Nitin Gupta

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-20 : 12:52:05

Didn't we just do this?

Can you give us an explanation of why you would want to do this?

In addition, read the hint llink in my sig..it asks you to post DDL, sample data and expect results

If you can't explain that, we can't help you

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 12:54:40

In this case, you should normalize your table.
Otherwise, check this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76527

Peter Larsson
Helsingborg, Sweden

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-12-20 : 12:56:30

Hi
This is the script to create a table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TBL_ALL_FIELDS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TBL_ALL_FIELDS]
GO

CREATE TABLE [dbo].[TBL_ALL_FIELDS] (
[Unique_Id] [int] IDENTITY (1, 1) NOT NULL ,
[Server_Id] [int] NULL ,
[Card_Num] [bit] NULL ,
[First_Name] [bit] NULL ,
[Last_Name] [bit] NULL ,
[Company] [bit] NULL ,
[Building] [bit] NULL ,
[Clear_Id1] [bit] NULL ,
[PreReg_Level] [bit] NULL ,
[Facility_Code] [bit] NULL ,
[Phone] [bit] NULL ,
[Fax] [bit] NULL ,
[email] [bit] NULL ,
[Active_Date] [bit] NULL ,
[Expire_Date] [bit] NULL ,
[PreReg_Name] [bit] NULL ,
[PreReg_Pass] [bit] NULL ,
[Disabled] [bit] NULL ,
[Deleted] [bit] NULL ,
[BUILDING_ADDRESS] [bit] NULL ,
[BUILDING_CITY] [bit] NULL ,
[BUILDING_STATE] [bit] NULL ,
[BUILDING_ZIP] [bit] NULL ,
[Pin] [bit] NULL ,
[ttes] [bit] NULL ,
[testccure] [bit] NULL
) ON [PRIMARY]
GO

Now this table has got only 1 row...
and it has got vlaues in 0 and 1 in all the columns
Now i want to retrive only those columns where value =1

Regards
Nitin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 13:11:36

quote:
Originally posted by X002548

Didn't we just do this?

Bad habits travel fast around the world.
Good habits are forgotten equal fast.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 13:12:40

I think this is due to newbies read other newbies solutions and use them because they can understand them.
They have a harder time understanding good coding solutions.

Peter Larsson
Helsingborg, Sweden

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-12-20 : 13:14:53

Hi Peter
Can you please elaborate,what are you trying to say?
Regards
Nitin

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-20 : 13:33:43

Not sure what you are ultimately trying, but as everyone here is telling you, your design is very flawed. Databases store multiple values in ROWS, not COLUMNS. We have no idea what the bit values in your table represent, since there is no way to present that info -- is it for columns that are valid? selected? Active? visible? With a correct schema, you can represent this correctly, your data is clear, and it is much easier to read and manipulate.

If you have a table like this:

create table AllFields (FieldName varchar(100) primary key not null, Selected bit)

then you can insert values into this table using simple INSERT statements, change with simple UPDATES, and have as many columns as you want that indicate exactly what the data you are storing for each column represents. Now your data is normalized, and you can simply select from the table like this:

select FieldName
from AllFields
where Selected = 1

And so on. I doubt this is exactly what you need, but I hope you get the overall idea. The columns in tables and resultsets should be CONSTANTS -- they should not change depending on the contents of the data, they only change when the way the data itself is stored is changed.

- Jeff

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-12-20 : 13:52:27

Hey Jeff
Thanks a Ton.Much Appreciated

Regards
Nitin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources