Dynamic derivation of Heading - is it possible

Hi
I have a query which produces effectively a pivottable. Is there any way I can dynamically assign the column headings ie the code on each line after AS rather than hard coded as I have currently

Extract of Current SP

CREATE PROC dbo.FairValeSummaryPivot
@.BatchRunID INT
AS
SET NOCOUNT ON
SELECT
MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn1 - Tn0' ,
MIN(CASE WHEN Tn = '0' THEN PVBalance END) AS 'Tn0 - Tn-1' ,
MIN(CASE WHEN Tn = '-1' THEN PVBalance END) AS 'Tn-1 - Tn-2',
MIN(CASE WHEN Tn = '-2' THEN PVBalance END) AS 'Tn-2 - Tn-3',
MIN(CASE WHEN Tn = '-3' THEN PVBalance END) AS 'Tn-3 - Tn-4',
MIN(CASE WHEN Tn = '-4' THEN PVBalance END) AS 'Tn-4 - Tn-5',
-- and so on
FROM FVSummary
WHERE BatchRunID = @.BatchRunID
GO

what I would like would be along the lines of

MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn' + Tn + ' - Tn' + Tn-1, ,

Hope this is clear
CheersDynamic SQL is all that comes to my mind from the SQL perspective, but this is really a presentation issue so I think it should be handled at the client rather than in the SQL itself.

-PatP|||Dynamic SQL is the only method of assigning variable column headers. But I would discourage you from doing this because no reporting application (Crystal, Access, Active Reports...) is going to be able to deal with output that has a different layout for each result set.
There is (almost) never a good reason for doing what you are trying to do, and in essence that is why it is difficult to do.|||Do a google on ags crosstab. Also (if it's still out there) RAC for SQL.

Regards,

hmscott

Dynamic database connectivity?

Dear SSIS experts,

I was wondering if there's such a thing that we could assign the database connection on the fly dynamically? so that I save the connection string or the database name somewhere and SSIS reads it on load time or something..

Thank you

Yes! Look into "package configurations." Examples and more information are in BOL and all over this forum.|||Thanks for ur quick reply; however, I can't find a thing! would u plz give me a link or something? Thanks |||Pull up Google and type in (without the quotes) "ssis package configurations"

The first few links should work very nicely for you.

The official page from Microsoft: http://msdn2.microsoft.com/en-us/library/ms141682.aspx

Dynamic Column Naming

Greetings,
Very simple question, how do i assign a column same name as its value?
declare @.seas varchar(5) set @.seas = 'Donie'
SELECT @.seas as (')
NOT static please, dynamic. So, SELECT @.seas as 'Donie' won't do me any
good.
Much obliged,
Don
*** Sent via Developersdex http://www.examnotes.net ***don larry wrote:
> Greetings,
> Very simple question, how do i assign a column same name as its value?
> declare @.seas varchar(5) set @.seas = 'Donie'
> SELECT @.seas as (')
> NOT static please, dynamic. So, SELECT @.seas as 'Donie' won't do me
> any good.
> Much obliged,
> Don
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
You need to use dynamic sql, which has security implications if you are
using stored procedures.
For example:
declare @.seas varchar(5)
declare @.sql nvarchar(1000)
set @.seas = 'Donie'
set @.sql = N'SELECT [' + @.seas + N'] from dbo.mytable'
Exec sp_executesql @.sql
David Gugick
Imceda Software
www.imceda.com|||The first obvious question would be "Why would you want to do this?"
Assuming there was some reason, you have to use dynamic SQL to get this kind
of
dynamic feature.
Declare @.Sql VarChar(8000)
Declare @.Seas VarChar(5)
Set @.Sql= 'Select ' + QuoteName(@.Seas, '''') + ' As ' + QuoteName(@.Seas)
Exec(@.Sql)
Thomas
"don larry" <donlarry17@.hotmail.com> wrote in message
news:%23mwQMTAXFHA.2776@.TK2MSFTNGP12.phx.gbl...
> Greetings,
> Very simple question, how do i assign a column same name as its value?
> declare @.seas varchar(5) set @.seas = 'Donie'
> SELECT @.seas as (')
> NOT static please, dynamic. So, SELECT @.seas as 'Donie' won't do me any
> good.
> Much obliged,
> Don
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>> how do i assign a column same name as its value? <<
Okay, you missed the whole idea of data modeling, RDBMS, and just about
everything else for the past 35 years in the field. Not a minor
misunderstanding, but all of it at the foundation level. Entities,
attributes and values are TOTALLY DIFFERENT! This is like being in a
math newsgroup and having someone ask "Can I make 2+2=5 If I have
really large values of 2?"
Really bad SQL programmers can use proprietary, dynamic code to kludge
things like this. But if you actually want to be competent, get a good
book on RDBMS and data model, read it and stop programming until you
understand what you are doing.

Dynamic Column Alias Name...

Hello All,
Here is my question; I am trying to dynamically assign a Column Alias in a
Select Statement to a variable, in which case the variable was assigned the
value of another select statement?
This is a simplified version of what I am trying to do, but would compliment
the results I am trying to get too if one would know if this is possible.
Here is the example SQL:
---
Create Table Test(
COL NVArChar(20)
)
Insert Into Test
Values('MyDynamicColumnName')
Go
Declare @.MyCol As NVarChar(20)
Set @.MyCol = (Select COL From TEST)
--Print @.MyVal
Select Top 20 au_id As @.MyCol
From authors
---AFAIK, it can't be done without using dynamic SQL.
Thomas
"Diablo" <Diablo@.discussions.microsoft.com> wrote in message
news:DA69C85C-04D8-42C5-A9FC-8BBDD1C87523@.microsoft.com...
> Hello All,
> Here is my question; I am trying to dynamically assign a Column Alias in a
> Select Statement to a variable, in which case the variable was assigned th
e
> value of another select statement?
> This is a simplified version of what I am trying to do, but would complime
nt
> the results I am trying to get too if one would know if this is possible.
> Here is the example SQL:
> ---
> Create Table Test(
> COL NVArChar(20)
> )
> Insert Into Test
> Values('MyDynamicColumnName')
> Go
> Declare @.MyCol As NVarChar(20)
> Set @.MyCol = (Select COL From TEST)
> --Print @.MyVal
> Select Top 20 au_id As @.MyCol
> From authors
> ---|||Thomas,
Would you happen to have an example of this I could leverage?
Regards...
"Thomas Coleman" wrote:

> AFAIK, it can't be done without using dynamic SQL.
>
> Thomas
>
> "Diablo" <Diablo@.discussions.microsoft.com> wrote in message
> news:DA69C85C-04D8-42C5-A9FC-8BBDD1C87523@.microsoft.com...
>
>|||Try this...just based it off the pubs db so wouldn't need to create a table
and populate...
declare @.val varchar(50)
select @.val = au_lname from authors where au_id = '172-32-1176'
execute ('select au_lname as ' + @.val + ' from authors')
go
HTH
J
"Diablo" <Diablo@.discussions.microsoft.com> wrote in message
news:5409CAC2-76A2-4CF7-8C91-0AFCA01827A3@.microsoft.com...
> Thomas,
> Would you happen to have an example of this I could leverage?
> Regards...
> "Thomas Coleman" wrote:
>|||Declare @.SQL VarChar(8000)
Declare @.ColName VarChar(25)
Set @.SQL = 'Select TOP 20 au_id As ' + QuoteName(@.ColName) + ' From authors'
Exec(@.SQL)
You should obviously be wary of using dynamic SQL for various reasons not th
e
least of which is that (in SQL 2000) users will need to have direct access t
o
the tables using their user account.
Thomas
"Diablo" <Diablo@.discussions.microsoft.com> wrote in message
news:5409CAC2-76A2-4CF7-8C91-0AFCA01827A3@.microsoft.com...
> Thomas,
> Would you happen to have an example of this I could leverage?
> Regards...
> "Thomas Coleman" wrote:
>|||>> I am trying to dynamically assign a Column Alias in a Select Statement to
a variable, in which case the variable was assigned the value of another se
lect statement? <<
You are confusing display, which is a front end function in a tiered
architecture, with a data model. Data elements do not magically change
names (and therefore what they model) at run time in an RDBMS.
The kludge for newbie programmers that don't know anything about RDBMS
is to use Dynamic SQL, which is slow and dangerous.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117048471.627932.101610@.o13g2000cwo.googlegroups.com...
> You are confusing display, which is a front end function in a tiered
> architecture, with a data model.
For a drastically different perspective I suggest you check out
www.alphora.com
which is based on the work of C.Date & al.You know Date don't you?:)
Hopefully the D4 language doesn't threaten you:)|||From the site:
> New version of toolset takes developers beyond
> Rapid Application Development (RAD)
How exciting! This sounds like the perfect tool for the new F.A.D.
development methedology:
http://weblogs.asp.net/alex_papadim.../05/405747.aspx
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"Pike" wrote:

> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1117048471.627932.101610@.o13g2000cwo.googlegroups.com...
> For a drastically different perspective I suggest you check out
> www.alphora.com
> which is based on the work of C.Date & al.You know Date don't you?:)
> Hopefully the D4 language doesn't threaten you:)
>
>