SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Constructing a column based on other columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

monotony
Starting Member

4 Posts

Posted - 09/17/2010 :  16:28:43  Show Profile  Reply with Quote
Hello all,

I'm trying to figure out how to make a column value based on other columns in the same table. I have a member table with the fields: memTitle, memTitleShow, memFName, memFNameShow, memLName, memCredentials, memCredentialsShow.

The display is: [memTitle][memFName][memLName][, memCredentials]

The columns suffixed by "Show" are bit fields. I would like the computed column to calculate the display based on the bit fields, so if memTitleShow is on, it would pull in the memTitle from the table, if ti is off, memTitle will return as blank. The only column that will show up no matter what is the memLName field. Everythign else can be turned off and off based on those bits.

Can anyone help me create a formula for the Display column that will accomplish this task?

Thanks in advance for any help...

Jeff

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 09/18/2010 :  06:00:44  Show Profile  Visit webfred's Homepage  Reply with Quote
select
case when memTitleShow=1 then memTitle else '' end as memTitle,
case when memFnameShow=1 then memFname else '' end as memFname,
memLName,
...
from ...


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

monotony
Starting Member

4 Posts

Posted - 09/20/2010 :  16:03:45  Show Profile  Reply with Quote
Thank you for the reply webfred. It is not exactly what I was looking for but it is a start.

I understand how to write the select statement for this issue (I've written it about 25 times in various places on the site), what I'm trying to figure out is how to turn it into a computed column in the DB so I don't have to keep writing these super queries, just to display the name info. I don't understand how to turn it into a UDF or something and then make it the default value in a separate column called "memDisplayName" or something like that...

I know this is possible, I just don't know the details... can you show me how to do this?

Thanks!

Jeff
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/20/2010 :  16:09:31  Show Profile  Reply with Quote
ALTER TABLE ADD COLUMN?????

Do you know what books online is?



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





Go to Top of Page

monotony
Starting Member

4 Posts

Posted - 09/20/2010 :  16:23:32  Show Profile  Reply with Quote
X002548, did you even read my post? I know how to alter a table to add a freaking column.

What I do not know is how to make that column's value be a UDF that returns a string that is constructed from the data based on other data in that record.

Do you?

Thanks,
Jeff
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 09/20/2010 :  16:29:07  Show Profile  Visit robvolk's Homepage  Reply with Quote
Take webfred's CASE expressions and add them as computed columns, you don't need to make them UDF's.
Go to Top of Page

monotony
Starting Member

4 Posts

Posted - 09/20/2010 :  16:35:05  Show Profile  Reply with Quote
Rob,

I needed to construct a single column out of the several case statements, would that have worked?

I figured it out using the UDF that I wrote. If anyone is looking to do something similar and needs the syntax (since I couldn't find it anywhere) it goes like this in the formula column:

([dbo].[myUDFName]([myColumnArg]))

Thanks all,
Jeff
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 09/20/2010 :  16:58:25  Show Profile  Visit robvolk's Homepage  Reply with Quote
Yes, you'd simply concatenate each CASE (example):

ALTER TABLE myTable ADD NewColumn AS CASE WHEN A=1 THEN ColA ELSE '' END +
CASE WHEN B=1 THEN ColB ELSE '' END +
CASE WHEN C=1 THEN ColC ELSE '' END
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000