How to Set dynamic column name or Change column name dynamicly

How to Change column name dynamically
Declare @.Column_name Varchar(30)
Set @.Column_name = (Select Name from Customer where ...)
Create table #temp
(
Name Varchar(30)
Date datetime
Sales Money
)
EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
It failsThis only seems to work on physical tables, i.e. not temporary ones.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"M" <mxchen@.hotvoice.com> wrote in message
news:upIZpUkFGHA.2212@.TK2MSFTNGP15.phx.gbl...
> How to Change column name dynamically
> Declare @.Column_name Varchar(30)
> Set @.Column_name = (Select Name from Customer where ...)
> Create table #temp
> (
> Name Varchar(30)
> Date datetime
> Sales Money
> )
> EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
> It fails
>
>|||M wrote:
> How to Change column name dynamically
> Declare @.Column_name Varchar(30)
> Set @.Column_name = (Select Name from Customer where ...)
> Create table #temp
> (
> Name Varchar(30)
> Date datetime
> Sales Money
> )
> EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
> It fails
Posting guidelines: http://vyaskn.tripod.com/posting.htm
The code you posted will work if you are in the context of tempdb (where the
temp table is located). You may be able to execute the change from the
context of another database using dynamic SQL (see EXEC and sp_executesql in
BOL for more information)
David Gugick
Quest Software|||Don't do that.
You are trying to mix data with metadata, which is a definite No-No
Roji. P. Thomas
http://toponewithties.blogspot.com
"M" <mxchen@.hotvoice.com> wrote in message
news:upIZpUkFGHA.2212@.TK2MSFTNGP15.phx.gbl...
> How to Change column name dynamically
> Declare @.Column_name Varchar(30)
> Set @.Column_name = (Select Name from Customer where ...)
> Create table #temp
> (
> Name Varchar(30)
> Date datetime
> Sales Money
> )
> EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
> It fails
>
>|||No one else asked this: why? Why so you think you need to change schema on
the fly?
ML
http://milambda.blogspot.com/|||I want a stored procudure to return a recordset which column name set on the
fly.
Declare @.Column_name1 Varchar(30)
Declare @.Column_name2 Varchar(30)
Set @.Column_name1 = (Select Name from Customer where ...)
Set @.Column_name2 = (Select Name from Customer where ...)
Create #Temp (
@.Column_name1 Varchar(30) -- Set @.Column_name1 as Column name as column
namename
@.Column_name2 Varchar(30) --will not work but I want actual customer
[Date] datetime
)
While
Begin
Select ... into #Temp from .. where
...
End
Select * from #Temp
"ML" <ML@.discussions.microsoft.com> wrote in message
news:DFC9E35F-FEB7-4451-B78F-DF84D2B28951@.microsoft.com...
> No one else asked this: why? Why so you think you need to change schema on
> the fly?
>
> ML
> --
> http://milambda.blogspot.com/
>|||Holy guacamole! Have you ever read anything in Books Online? Read through
this, please:
http://msdn.microsoft.com/library/d...r />
_9sfo.asp
I see no reason for the temporary table. Never use "select *" in production
code!
You need to explicitly list all columns you want in the result-set and asign
an appropriate ALIAS to set specific column names. In your specific case thi
s
is best done on the client, since you'd have to use dynamic SQL on the
server. Anyway, here goes...
You'd need something like this:
declare @.statement nvarchar(4000)
declare @.Col1Name sysname
declare @.Col2Name sysname
set @.Col1Name = 'Jim'
set @.Col2Name = 'Bob'
set @.statement = 'select <col1> as ' + @.Col1Name + '
,<col2> as ' + @.Col2Name + '
from <table>
where <conditions>'
Which constitutes:
select <col1> as Jim
,<col2> as Bob
from <table>
where <conditions>
You then have to execute the statement using EXEC:
exec (@.statement)
ML
http://milambda.blogspot.com/|||M (mxchen@.hotvoice.com) writes:
> How to Change column name dynamically
> Declare @.Column_name Varchar(30)
> Set @.Column_name = (Select Name from Customer where ...)
> Create table #temp
> (
> Name Varchar(30)
> Date datetime
> Sales Money
> )
> EXEC sp_rename '#temp.Name', @.Column_name, 'COLUMN'
Apart from the very dubious in this, this works:
Declare @.Column_name Varchar(30)
Set @.Column_name = 'nisse'
Create table #temp
(
Name Varchar(30),
Date datetime,
Sales Money
)
EXEC tempdb..sp_rename '#temp.Name', @.Column_name, 'COLUMN'
select *from #temp
You need to add tempdb.. to set the context for sp_rename.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to sequentially SORT numbers that also have a letter attached?

Try:
declare @.t table
(
x varchar (5) primary key
, y as case when patindex ('%[a-z]', x) = 0 then ''
else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
, z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
)
insert @.t values ('1')
insert @.t values ('1a')
insert @.t values ('1b')
insert @.t values ('1c')
insert @.t values ('2')
insert @.t values ('2a')
insert @.t values ('2b')
insert @.t values ('2c')
insert @.t values ('3')
insert @.t values ('3a')
insert @.t values ('3b')
insert @.t values ('3c')
insert @.t values ('11a')
insert @.t values ('21c')
select x from @.t
order by z, y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ALI" <kismet110@.yahoo.co.uk> wrote in message
news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
Hi
I'm hoping this will be straightforward for you guys to point me in the
right direction.
Friend of mine emailed me asking the following:
We have a table with a house plot number on it, which is varchar(5).
Example values are:
1
1a
1b
1c
2
2a
2b
2c
3
3a
3b
3c
11a
21c
When we sort these they come out as follows:
1
1a
1b
1c
11a
2
2a
2b
2c
21c
3
3a
3b
3c
Is there an easy way to get them to sort as per the first example? I.e.
1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
I thought it'd be simple but not so! Would appreciate your advice on
the simplest way of achieving this please.Hi
I'm hoping this will be straightforward for you guys to point me in the
right direction.
Friend of mine emailed me asking the following:
We have a table with a house plot number on it, which is varchar(5).
Example values are:
1
1a
1b
1c
2
2a
2b
2c
3
3a
3b
3c
11a
21c
When we sort these they come out as follows:
1
1a
1b
1c
11a
2
2a
2b
2c
21c
3
3a
3b
3c
Is there an easy way to get them to sort as per the first example? I.e.
1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
I thought it'd be simple but not so! Would appreciate your advice on
the simplest way of achieving this please.|||Try:
declare @.t table
(
x varchar (5) primary key
, y as case when patindex ('%[a-z]', x) = 0 then ''
else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
, z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
)
insert @.t values ('1')
insert @.t values ('1a')
insert @.t values ('1b')
insert @.t values ('1c')
insert @.t values ('2')
insert @.t values ('2a')
insert @.t values ('2b')
insert @.t values ('2c')
insert @.t values ('3')
insert @.t values ('3a')
insert @.t values ('3b')
insert @.t values ('3c')
insert @.t values ('11a')
insert @.t values ('21c')
select x from @.t
order by z, y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ALI" <kismet110@.yahoo.co.uk> wrote in message
news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
Hi
I'm hoping this will be straightforward for you guys to point me in the
right direction.
Friend of mine emailed me asking the following:
We have a table with a house plot number on it, which is varchar(5).
Example values are:
1
1a
1b
1c
2
2a
2b
2c
3
3a
3b
3c
11a
21c
When we sort these they come out as follows:
1
1a
1b
1c
11a
2
2a
2b
2c
21c
3
3a
3b
3c
Is there an easy way to get them to sort as per the first example? I.e.
1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
I thought it'd be simple but not so! Would appreciate your advice on
the simplest way of achieving this please.|||Thanks for your help Tom, hopefully this will get them what they want,
I don't feel totally stupid now too for thinking it would be _really_
simple!
Tom Moreau wrote:
> Try:
> declare @.t table
> (
> x varchar (5) primary key
> , y as case when patindex ('%[a-z]', x) = 0 then ''
> else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
> , z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
> else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
> )
> insert @.t values ('1')
> insert @.t values ('1a')
> insert @.t values ('1b')
> insert @.t values ('1c')
> insert @.t values ('2')
> insert @.t values ('2a')
> insert @.t values ('2b')
> insert @.t values ('2c')
> insert @.t values ('3')
> insert @.t values ('3a')
> insert @.t values ('3b')
> insert @.t values ('3c')
> insert @.t values ('11a')
> insert @.t values ('21c')
> select x from @.t
> order by z, y
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "ALI" <kismet110@.yahoo.co.uk> wrote in message
> news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
> Hi
> I'm hoping this will be straightforward for you guys to point me in the
> right direction.
> Friend of mine emailed me asking the following:
> We have a table with a house plot number on it, which is varchar(5).
> Example values are:
> 1
> 1a
> 1b
> 1c
> 2
> 2a
> 2b
> 2c
> 3
> 3a
> 3b
> 3c
> 11a
> 21c
> When we sort these they come out as follows:
> 1
> 1a
> 1b
> 1c
> 11a
> 2
> 2a
> 2b
> 2c
> 21c
> 3
> 3a
> 3b
> 3c
> Is there an easy way to get them to sort as per the first example? I.e.
> 1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
> I thought it'd be simple but not so! Would appreciate your advice on
> the simplest way of achieving this please.|||Thanks for your help Tom, hopefully this will get them what they want,
I don't feel totally stupid now too for thinking it would be _really_
simple!
Tom Moreau wrote:
> Try:
> declare @.t table
> (
> x varchar (5) primary key
> , y as case when patindex ('%[a-z]', x) = 0 then ''
> else right (x, len (x) - patindex ('%[a-z]', x) + 1) end
> , z as case when patindex ('%[a-z]', x) = 0 then cast (x as int)
> else cast (left (x, patindex ('%[a-z]', x) - 1) as int) end
> )
> insert @.t values ('1')
> insert @.t values ('1a')
> insert @.t values ('1b')
> insert @.t values ('1c')
> insert @.t values ('2')
> insert @.t values ('2a')
> insert @.t values ('2b')
> insert @.t values ('2c')
> insert @.t values ('3')
> insert @.t values ('3a')
> insert @.t values ('3b')
> insert @.t values ('3c')
> insert @.t values ('11a')
> insert @.t values ('21c')
> select x from @.t
> order by z, y
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "ALI" <kismet110@.yahoo.co.uk> wrote in message
> news:1150111751.001833.272460@.f14g2000cwb.googlegroups.com...
> Hi
> I'm hoping this will be straightforward for you guys to point me in the
> right direction.
> Friend of mine emailed me asking the following:
> We have a table with a house plot number on it, which is varchar(5).
> Example values are:
> 1
> 1a
> 1b
> 1c
> 2
> 2a
> 2b
> 2c
> 3
> 3a
> 3b
> 3c
> 11a
> 21c
> When we sort these they come out as follows:
> 1
> 1a
> 1b
> 1c
> 11a
> 2
> 2a
> 2b
> 2c
> 21c
> 3
> 3a
> 3b
> 3c
> Is there an easy way to get them to sort as per the first example? I.e.
> 1 first, then 1a, 1b, 1c followed by 2, 2a etc and not 11a?
> I thought it'd be simple but not so! Would appreciate your advice on
> the simplest way of achieving this please.