Dynamic column names in T-SQL?

Does anyone know of a clever hack that can be used to generate column names
on the fly, either from local variables or values in a temp table?  

0
Robert
7/24/1998 4:05:22 AM
sybase.sqlserver.general 4269 articles. 0 followers. Follow

3 Replies
689 Views

Similar Articles

[PageSpeed] 11

Robert Gumpertz wrote:
> 
> Does anyone know of a clever hack that can be used to generate column names
> on the fly, either from local variables or values in a temp table?

For what purpose?  I'm not following where you're going with this?
(maybe I need some caffeine?)
-- 
Mark A. Parsons
Iron Horse, Inc.
0
Mark
7/25/1998 9:40:45 PM
This is a reporting application where the user can submit a random list of
states; the result is a count by category within state, which I pivot into
the following column format: Category, State1, State2 etc.  Obviously, I
need to generate the proper column headings for the states.  Clearer?

Mark A. Parsons <iron_horse@compuserve.com> wrote in article
<35BA50DD.602C@compuserve.com>...
> Robert Gumpertz wrote:
> > 
> > Does anyone know of a clever hack that can be used to generate column
names
> > on the fly, either from local variables or values in a temp table?
> 
> For what purpose?  I'm not following where you're going with this?
> (maybe I need some caffeine?)
> -- 
> Mark A. Parsons
> Iron Horse, Inc.
> 
0
Robert
7/27/1998 7:11:20 AM
Robert Gumpertz wrote:
> 
> This is a reporting application where the user can submit a random list of
> states; the result is a count by category within state, which I pivot into
> the following column format: Category, State1, State2 etc.  Obviously, I
> need to generate the proper column headings for the states.  Clearer?
> 
> Mark A. Parsons <iron_horse@compuserve.com> wrote in article
> <35BA50DD.602C@compuserve.com>...
> > Robert Gumpertz wrote:
> > >
> > > Does anyone know of a clever hack that can be used to generate column
> names
> > > on the fly, either from local variables or values in a temp table?
> >
> > For what purpose?  I'm not following where you're going with this?
> > (maybe I need some caffeine?)
> > --
> > Mark A. Parsons
> > Iron Horse, Inc.
> >

2 possible 'solutions' I can think of ...

Run your typical group by query to get a row-based query as you want
then

1) put the results into a 'permanent' temporary table, create the
appropriate dynamic 'select' statement, submit this statement via remote
sql back to the same server (referencing the 'permanent' temporary
table), and, wala!

2) send the row-based query results back to the client and have it pivot
the results for you (e.g., I believe you can do something like this in
PB, and I'm sure I could cook up something with a 'simple' UNIX shell
script).

There are ways to build a pivot table, like you want, with #temp tables
but this requires you know your columns (and how many of them) up
front.  For a dynamic solution (as you've mentioned) it would appear
that you'll need to some how pass the info back to a client (PB? remote
connection right back to the same server ==> ~client ).

-- 
Mark A. Parsons
Iron Horse, Inc.
0
Mark
8/9/1998 6:20:52 PM
Reply:

Similar Artilces:

Column Name with Dynamic SQL
Hi, I use DynamicStagingArea for retrieval of a dynamic table. I there any possibility to get the column names for the table? Roland Sattler ...

dynamic sql with variable column name
I want to give select list(columan names) as variable to the dynamic sql statement.. is this possible ? sql statement that I wrote below : string Sqlstatement,sqlst1,Stringvar decimal decvar Sqlstatement = 'SELECT ? FROM TABLE_NAME WHERE TABLE_NAME.COLUMN1 ="'+variable1+'" and & TABLE_NAME.COLUMN2 = "'+variable2+'" and & TABLE_NAME.COLUMN3 = "'+variable3+'" and & TABLE_NAME.COLUMN3 = "'+variable3+'"' PREPARE SQLSA FROM :Sqlstatement ; DESCRIBE ...

Error Invalid column name (In Sqlserver 2005) While giving Alias column name
ALTER procedure [dbo].[MyPro](@StartRowIndex int,@MaximumRows int) As Begin Declare @Sel Nvarchar(2000)set @Sel=N'Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM Between ' + convert(nvarchar(15),@StartRowIndex) + ' and ('+ convert(nvarchar(15),@StartRowIndex) + '+' + convert(nvarchar(15),@MaximumRows) + ')-1' print @Sel Exec Sp_executesql @Sel End   --Execute Mypro 1,4        --->>Here I Executed  Error Select *,Row_number() over(order by myId) as ROWNUM from My...

Dynamically setting the column name on a SQL query
Can anyone show me or tell if the following is possible. I have a SQL query as shown below: SQL = "SELECT revenue_forecast.oct AS 'revenue' ........... What I need to do is dynamically set the column in this SQL query. So i need to be able to place a variable in the part after the revenue_forecast "." . Can someone show me how to do this? Thanks It depends on your data source, but most data sources do not allow you to parameterize column names. You have a couple of choices. One is to concatenate the name into the string as in... SQL = "SELECT reven...

Is Dynamic SQL Possible in T/SQL
I come from a Microsoft SQL Server environment, so I am used to being able to create dynamic SQL statements within a stored procedure with the EXEC command. It appears that Sybase's equivalent command only recognizes othere stored procedures. Does anyone know of a way to do this in Sybase? ASE 12.0 supports dynamic execution of strings, see "execute" in Ref Manual. In versions ASE 11.5 and higher, you can also use the sp_remotesql against the "local" server, for much the same effect, although there are more limits using this than the "execute imm...

Dynamic SQL and mixing T-SQL/Watcom
SQL Anywhere version 5.5.01 I believe that Watcom SQL is my only option for dynamic SQL execution (i.e. resolving object names at run time) in a procedure using EXECUTE IMMEDIATE. True? However we are likely to want to upgrade to SYBASE and would like to use (the inferior?) T-SQL and its data types for portability. I am currently calling a Watcom proc (using EXECUTE IMMEDIATE) from a T-SQL procedure. Seems to work fine but this seems philosophically wrong and I suspect will lead to problems. True? Ideas, Advice?? Thanks <pc> P.S. Please someone tell me I ca...

Dynamically name columns in dynamically created datagrid
I am using code that I found on the net to take a datatable, drop it into a datagrid, and then export the whole thing as an excel spreadsheet. So far for the small numberes I have tried it works. Currently I have an application where the number of columns ranges between 40 and 70. The problem that I have is I want the first row to have names which are meaningful to the user, but I don't want to rename my datatable columns, only the datagrid columns. Right now the Excel column names are the same as the datatable names. Those names are meaningful to me (the programmer) but meaningless to...

SQL: Column names, can ONLY get "name" instead of "table.name"
Hey all, When iterating through column names returned from a query, I need to get "table.name" rather than just "name" Check it out: If I have an SQL statement "SELECT table1.ID, table2.ID FROM table1, table2;" And table1 has a column name 'ID' and table2 has a column name 'ID' aswell, and i run the query, the dataset will contain column names 'ID' and 'ID' instead of 'table1.ID' and 'table2.ID' It is extremely important that I can differenciate between the 2 fields, and know where each of them come from. The same thing happen...

How to use a full column name (table name and column name) with a DataReader
Hi, I have a problem retrieving the data with a DataReader from a stored proc that performs a join of two table with the same column's names but different data. Let me explain with an example: I have two tables: Table1: Id, Name Table2: Id, Name, IdTable1 The sproc is something like this: CREATE PROCEDURE [dbo].[SearchByName] (@Name nvarchar(40)) AS BEGIN SELECT * FROM dbo.Table1, dbo.Table2 WHERE Table1.Id = Table2.IdTable1 AND Table1.Name LIKE '%' + @Name + '%' END And my c# code is something like this: Database db = Databas...

How does exposed Dynamic T-SQL allow a SQL injection attack?
I am reading an article on stored procedures that create other stored procedures. It is discussing dynamic T-SQL used with EXECUTE to generate a stored proc. Then there is this warning (excerped below). I don't quite understand how dynamic T-SQL would be used to cause a SQL injection attack. I am -not- looking for direction to create an attack; rather what exactly should I avoid doing? In the example below, how is dynamic T-SQL different from any other proc where variables are introduced. Thanks in advance, Glenn ----- excerpt ----- Let's start by examining a simple e...

SQL query returns 'No column name' as a column heading
 hi,I'm trying to execute this query in SQL Server 2005 Express Edition, the problem is in the column heading of the result for the majorName fieldSELECT studentID,fname,mname,lname,familyName,gpa ,(SELECT majorName AS major FROM major WHERE majorID=(SELECT majorID FROM studentmajorWHERE studentID='0000000'))FROM student WHERE studentID='0000000'; Why the result of this query returns 'No column name' as a label for the majorName field? I use the AS clause to allows the column to be named, the column heading of the result table still 'No column name&...

HOW CAN I GET THE COLUMN TYPE WHEN I DON'T HAVE COLUMN NAME
Hi , We have developed one application, now we would like to change the format/edit mask depends on the parameters. To do so I should know the column type. My question is HOW CAN I GET THE COLUMN TYPE ? WHEN I DON'T HAVE COLUMN NAME. In other words if I have array of columns in a datawindow and I want to check the column type one by one for each column no. If I code like this , it gives compilation error. long ll_col_count , ll_kount string ls_col_type ll_col_count = integer(dw_1.object.datawindow.column.count) for ll_kount = 1 to ll_col_count ls_col_...

Dynamic column names
How to add column names for a data window object at run time ? ( The datawindow object has already been created) If you mean, assigning new names to existing columns at runtime, I don't believe you can do that. If you mean creating a new one, here's a snippet from the DWSyntax utility: <DW Control Name>.Modify(& "create column( id=<an integer> tabsequence=<an integer> accelerator='<a single letter>' moveable=<0 - False, 1 - True> resizeable=<0 - False, 1 - True> pointer='<Arrow!, Cross!, HourGlass!, IBeam!, Ico...

Dynamic column name?
Hi All, I am having one doubt which is mentioned below. My doubt is i want to store a column name in the instance variable. By using the instance variable i want to check the validation in itemchanged event.Is it Possible.....? Eg: In the itemchanged event i want check the column like this. This.Object.is_name[row] //where is_name contains some column name. You could use the older Describe method. Dot notation requires a fixed name. On 22 May 2004 08:25:55 -0700, Kalyan wrote: >Hi All, > >I am having one doubt which is mentioned below. > >My doubt is...

Web resources about - Dynamic column names in T-SQL? - sybase.sqlserver.general

Dynamics - Wikipedia, the free encyclopedia
Brownian dynamics , the occurrence of Langevin dynamics in the motion of particles in solution (e.g. a grain in water, as was first seen by Brown); ...

Datacenter Dynamics
Datacenterdynamics for data center information, conferences, news, videos, whitepapers and reports globally

Dynamic Chiropractic - chiropractic, news, articles, research & information for chiropractors - Find ...
Dynamic Chiropractic - chiropractic, news, articles, research & information for chiropractors - Find a Chiropractor

Walmart's Everyday Low Prices Face Amazon's Dynamic Price Push
... low prices" strategy is becoming an everyday price-adjustment strategy. In what seems to be a direct response to Amazon's sophisticated dynamic ...

The colorful, dark, dynamic art of life: 2015 BioArt winners
In an embryonic mouse torso, researches closely track how the parallel paths of blood vessels (gray) and nerve cells (red) develop. Understanding ...

General Dynamics Corporation Delivers The Future of American Shipping
American Petroleum Tankers receives delivery of first ECO class tanker from General Dynamics\' NASSCO.

Yolanda Foster explains her divorce: ‘chronic illness changed our dynamic’
... is extremely challenging, not only for the person that’s sick, but also for the caregiver,” Foster admitted. “It for sure changed the dynamic ...

Microsoft Dynamics AX delivers the power of the cloud to businesses
Microsoft has announced that the next release of its flagship ERP solution, Microsoft Dynamics AX, will be generally available in the first quarter ...

Ford's 'Dynamic Shuttle' tests out a competitor for Uber
... group transportation options, its next competition could come from Ford. Today at an event in Dearborn, the carmaker showed off the Dynamic ...

Yolanda Foster Says Lyme Disease 'Changed the Dynamic' of Her Marriage to David Foster
Yolanda Foster Says Lyme Disease 'Changed the Dynamic' of Her Marriage to David Foster

Resources last updated: 12/12/2015 11:39:03 AM