adding a dynamic column in a table....

Question

1.00/5 (1 vote)

See more:

is there any way to add a column to a table during runtime. That is suppose i have a table containing 4 columns...i have written a stored procedure for insert,del,upd operations also. now what i want is without using alter statement when i write select query automatically i get a additional column and it should have values corresponding to empid..ie for empid 100,102 i get first,first and so on...
tried it but struck in it for two days ...help me out.thanks in advance.

[EDIT - content moved from comment]
sir i have a table that have 4 columns named empid,emp_name,emp_ADD,emp_Phno.now i have created a procedure to insert,delete,update,search operation. my table is as follows:

empid	emp_name  Add	emp_phno
100	rachit	 516-a	8696953366	
101	ush	 25/a	7737708131
105	unnet	 sfgcsy	986547896 	
104	bobby	 76A	7899877899	
103	dharam	 2519	7765477654	
102	kishor	 77/A	8989689896

Now without using any alter command ..i want this when use select * from employee

empid	emp_name  Add	emp_phno      grp_emp
100	rachit	 516-a	8696953366	first
101	ush	 25/a	7737708131      first
105	unnet	 sfgcsy	986547896 	second
104	bobby	 76A	7899877899	second
103	dharam	 2519	7765477654	third
102	kishor	 77/A	8989689896	third

i want to add this last column on runtime without using alter statement.I think this is sufficient for u sir.thanks.

[/EDIT]

Posted 28-May-13 23:30pm

Rambo_Raja

Updated 28-May-13 23:59pm

Maciej Los

v2

Add a Solution

Comments

Maciej Los 29-May-13 5:33am

Please, be more specific and provide more details about your needs (example data, expected output).

Rambo_Raja 29-May-13 5:51am

sir i have a table that have 4 columns named empid,emp_name,emp_ADD,emp_Phno.now i have created a procedure to insert,delete,update,search operation. my table is as follows:
empid emp_name Add emp_phno

100 rachit 516-a 8696953366
101 ush 25/a 7737708131
105 unnet sfgcsy 986547896
104 bobby 76A 7899877899
103 dharam 2519 7765477654
102 kishor 77/A 8989689896
Now without using any alter command ..i want this when use select * from employee
empid emp_name Add emp_phno grp_emp

100 rachit 516-a 8696953366 first
101 ush 25/a 7737708131 first
105 unnet sfgcsy 986547896 second
104 bobby 76A 7899877899 second
103 dharam 2519 7765477654 third
102 kishor 77/A 8989689896 third
i want to add this last column on runtime without using alter statement.I think this is sufficient for u sir.thanks.

Maciej Los 29-May-13 6:15am

Next time, please, use "Improve question" widget.
Based on which condition do you want to divide employees on groups?

Rambo_Raja 29-May-13 6:28am

any condition sir. i am not getting key to solve this question. could you please guide me to approach this and this type of questions.thanks.

Maciej Los 29-May-13 6:33am

Are you joking?

Rambo_Raja 29-May-13 6:35am

why?

Maciej Los 29-May-13 6:43am

You need to know criteria on which you want to group employees. For "any" criteria, see my answer.

Rambo_Raja 29-May-13 6:49am

ah not getting your point. i am novice in this field .if u don't mind please be patient and specific as well.thank u.

Maciej Los 29-May-13 6:56am

I'm patient ;)
I don't know the rules of grouping employees and i'm trying to get it from you...
"Any condition/rule" is not informative at all, true?

Rambo_Raja 29-May-13 7:10am

ya...will let u know if code worked for me..thank u.also sorry if u find me rude.

Maciej Los 29-May-13 7:20am

;)

Aarti Meswania 29-May-13 6:16am

how you decide grp for employee ?

Aarti Meswania 29-May-13 5:42am

"i want is without using alter statement when i write select query automatically i get a additional column"
Not possible without alter table how could you add column

As, I could guess from the partial information, you want pivot table
please give input output examples

Sudhakar Shinde 29-May-13 6:53am

Computed column can be used in case there is some relation to compute grp_emp on the basis of existing column but then only select * from tablename won't work.

2 solutions

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)

Rambo_Raja · Accepted Answer · 2013-05-29T02:00:00

Solution 2

SQL

select empid, (case when  empid between 1 and 2 then 'first'  when  empid=3 then 'second' else 'other' end ) as groupname   from employee_test

This can be tried also. Both solutions worked for me.

Posted 29-May-13 2:00am

Rambo_Raja

Updated 29-May-13 2:15am

v2

Comments

Maciej Los 29-May-13 8:07am

A 5!

Maciej Los · Accepted Answer · 2013-05-29T00:41:00

Solution 1

I'm not sure what you want to achieve...

Maciej Los - wrote:

Based on which condition do you want to divide employees on groups?

R_sharma - ago wrote:

any condition sir. i am not getting key to solve this question. could you please guide me to approach this and this type of questions.thanks

Example for "any" (where "any" means: empid modulo 2)condition to group employees:

SQL

SELECT empid, emp_name, [Add], emp_phno, CONVERT(INT, (empid % 2) +1 ) AS grp_emp
FROM @emp
ORDER BY CONVERT(INT, (empid % 2) +1 )

Result:

100	rachit	516-a	8696953366	1
104	bobby	76A	7899877899	1
102	kishor	77/A	8989689896	1
103	dharam	2519	7765477654	2
101	ush	25/a	7737708131	2
105	unnet	sfgcsy	986547896	2

Posted 29-May-13 0:41am

Maciej Los

Comments

Rambo_Raja 29-May-13 7:21am

yes Maciej you have got right answer for me.It worked!Thanks again.

Maciej Los 29-May-13 7:33am

You're welcome ;)

Rambo_Raja 29-May-13 7:41am

last one. can i use this :
select empid, (case when empid between 1 and 2 then 'first' when empid=3 then 'second' else 'other' end ) as groupname from employee_test

Maciej Los 29-May-13 7:45am

Of course, you can ;) Test it.
Have a look here: CASE (T-SQL)[^]

Rambo_Raja 29-May-13 7:49am

Great!Thank u ...can u please be my mentor in this field?:$

Maciej Los 29-May-13 8:14am

Yes ;)
4 stars for "Great" and "working solution"? ;(

Rambo_Raja 29-May-13 8:18am

lol...sorry.

gvprabu 29-May-13 9:28am

Nice way to get Points... :-) Just kiting
Nice work...

Maciej Los 29-May-13 9:29am

Thank you, my Friend ;)