Is it possible to use vharchar value as column

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 2008 Forums

Transact-SQL (2008)

Is it possible to use vharchar value as column

Author

Topic

kashyap.2000
Starting Member

22 Posts

Posted - 2010-09-17 : 08:49:10

For example can i get the same result for the below statements?

1st query) select name from table

2nd query) select cast('name' as column) from table--Returns error

3rd query) select 'a' from table--need to add something for 'a' to ----make it as column

Basically what i am trying to find weather is it possilbe to type cast a vharchar value to sql column?

Thanks in advance

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-17 : 08:51:44

dynamic sql can help you do that. But what are you trying to achieve ??

kashyap.2000
Starting Member

22 Posts

Posted - 2010-09-17 : 09:08:37

quote:
Originally posted by sakets_2000

dynamic sql can help you do that. But what are you trying to achieve ??

my requirement is to build query which can take column name dynamically, its strange but true that i cant hard code the column names, any help will be appreciated

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-17 : 11:36:19

That's a ridiculous requirement! I hope this is for class or some other educational purpose. If not, well, let's just hope it is.

However, if you must, you are going to need dynamic SQL as mentioned above. Here is a link that might help:
http://www.sommarskog.se/dynamic_sql.html

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-17 : 12:20:20

quote:
Originally posted by kashyap.2000

quote:
Originally posted by sakets_2000

dynamic sql can help you do that. But what are you trying to achieve ??

my requirement is to build query which can take column name dynamically, its strange but true that i cant hard code the column names, any help will be appreciated

you mean generate alias dynamically or dynamically determine column to retrive

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kashyap.2000
Starting Member

22 Posts

Posted - 2010-09-20 : 03:27:06

quote:
Originally posted by Lamprey

That's a ridiculous requirement! I hope this is for class or some other educational purpose. If not, well, let's just hope it is.

However, if you must, you are going to need dynamic SQL as mentioned above. Here is a link that might help:
http://www.sommarskog.se/dynamic_sql.html

Its not for some educational purpose, its for official project, i know its not good to use these type of methodologies, but i cant stop myself doing it as its been told me to do rather than my wish, thanks for the quick help.

kashyap.2000
Starting Member

22 Posts

Posted - 2010-09-20 : 03:28:56

quote:
Originally posted by visakh16

quote:
Originally posted by kashyap.2000

quote:
Originally posted by sakets_2000

dynamic sql can help you do that. But what are you trying to achieve ??

my requirement is to build query which can take column name dynamically, its strange but true that i cant hard code the column names, any help will be appreciated

you mean generate alias dynamically or dynamically determine column to retrive

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

The requirement is to get column names dynamically as varchar field and convert it as sql column, i know this is strange, but its the exact thing which i need to implement for the client.

parody
Posting Yak Master

111 Posts

Posted - 2010-09-20 : 08:10:00

do you mean you are trying to force a returned datatype.

select convert(nvarchar(20),'a') as testing123

or do you mean you want a literal value repeated for x number of rows?

if so then use a cte

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 09:30:54

Do you maybe want to present a result set and 'pivot' it, with the values of one of the columns becoming the column names of your result?

If so that's possible: look for dynamic pivot / dynamic cross Tab.

I think you should show us sample data and required process / output.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kashyap.2000
Starting Member

22 Posts

Posted - 2010-09-20 : 10:51:13

quote:
Originally posted by parody

do you mean you are trying to force a returned datatype.

select convert(nvarchar(20),'a') as testing123

or do you mean you want a literal value repeated for x number of rows?

if so then use a cte

No my requirement is to avoid hard coding column names, so its noting but getting column names dynamically at run time.

Failurs will either break you or will make you a better perosn....

kashyap.2000
Starting Member

22 Posts

Posted - 2010-09-20 : 10:52:54

quote:
Originally posted by Transact Charlie

Do you maybe want to present a result set and 'pivot' it, with the values of one of the columns becoming the column names of your result?

If so that's possible: look for dynamic pivot / dynamic cross Tab.

I think you should show us sample data and required process / output.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Oh yes i will try to use pivot to get this done. but i guess as mentioned in this post dynamic sql can be ideal solution

Failurs will either break you or will make you a better perosn....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-21 : 12:20:37

you can also pivot dynamically. see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kashyap.2000
Starting Member

22 Posts

Posted - 2010-09-22 : 03:19:55

quote:
Originally posted by visakh16

you can also pivot dynamically. see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks for the reply, i will try it and get back to you sql experts

Failurs will either break you or will make you a better perosn....

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-09-22 : 04:31:35

Bad use of dynamic SQL but:


DECLARE @Column varchar(50)='Column'
DECLARE @SQL varchar(max)='SELECT ' + Quotename(@Column) + ' FROM Table'
EXEC (@SQL)

Quotename puts squared brackets around it to protect against sql injection.

kashyap.2000
Starting Member

22 Posts

Posted - 2010-09-22 : 06:50:25

quote:
Originally posted by michael.appleton

Bad use of dynamic SQL but:

DECLARE @Column varchar(50)='Column'
DECLARE @SQL varchar(max)='SELECT ' + Quotename(@Column) + ' FROM Table'
EXEC (@SQL)

Quotename puts squared brackets around it to protect against sql injection.

Its a perfect solution which i was looking for. yes this issue is solved now, i am really greatful to you. thanks a lot michael

Failures will either break you or will make you a better perosn....

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-22 : 07:05:24

quote:
Originally posted by kashyap.2000

quote:
Originally posted by michael.appleton

Bad use of dynamic SQL but:

DECLARE @Column varchar(50)='Column'
DECLARE @SQL varchar(max)='SELECT ' + Quotename(@Column) + ' FROM Table'
EXEC (@SQL)

Quotename puts squared brackets around it to protect against sql injection.

Its a perfect solution which i was looking for. yes this issue is solved now, i am really greatful to you. thanks a lot michael

Failures will either break you or will make you a better perosn....

It's what you asked for......

But this is a really bad idea......

but !meh! -- advise was already given. Good luck

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kashyap.2000
Starting Member

22 Posts

Posted - 2010-09-23 : 04:24:48

quote:
Originally posted by Transact Charlie

quote:
Originally posted by kashyap.2000

quote:
Originally posted by michael.appleton

Bad use of dynamic SQL but:

DECLARE @Column varchar(50)='Column'
DECLARE @SQL varchar(max)='SELECT ' + Quotename(@Column) + ' FROM Table'
EXEC (@SQL)

Quotename puts squared brackets around it to protect against sql injection.

Its a perfect solution which i was looking for. yes this issue is solved now, i am really greatful to you. thanks a lot michael

Failures will either break you or will make you a better perosn....

It's what you asked for......

But this is a really bad idea......

but !meh! -- advise was already given. Good luck

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

yes thanks charlie, and thanks for all who helped me. i was dying to get this task done.

Failures will either break you or will make you a better perosn....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources