Varying Column Name
-
I have a table containing columns for Financial periods (Act01, Act02, ..Act12). I would like to be able to select the column based on the correct month. How can I use variable in select statement? I have tried
declare @Period as tinyint
set @Period=month(getdate())
select Act+@Period from tbl but this does not work
I am new to T-SQL.
TIA
dean
-
you can't do that. 2 solutions:
1: create procedure myproc @pd int
as
declare @cmd varchar(80)
select @cmd = 'select act' + cast(@pd as varchar) + ' from mytable'
return
2: (preferred)
create procedure myproc @pd int
as
if @pd = 1
select act01 from mytable
else if @pd = 2
select act02 from mytable
....
#2 is preferred since it doesn't use dynamic sql and clearly handles the input for each month. YOu might add logic to automatically figure out the correct month. However it does have a maintenance cost.
-
Or:
Declare @period int
SELECT @period = 3
Select (Case
When @period = 1 then Acnt01
When @period = 2 then Acnt02
When @period = 3 then Acnt03
When @period = 4 then Acnt04
When @period = 5 then Acnt05
When @period = 6 then Acnt06
When @period = 7 then Acnt07
When @period = 8 then Acnt08
When @period = 9 then Acnt09
When @period = 10 then Acnt10
When @period = 11 then Acnt11
When @period = 12 then Acnt12
ELSE NULL End) as Amount
From
tbl
I am not sure what are you trying to accomplish but this will get you going
* Noel -
Any chance to change the underlying table design? It really looks like you have something like this
year 01 02 03 04 05
2003 ...some numbers
2002 ...some numbers
If so, that should be the presentational result from your data not the table design itself. I would rather create a table with a column for the date and another column for the number. Then you can, for example, insert something like
01.01.2004 30,234.93
01.02.2004 50,234,12
Now for getting into your presentation format, you can use a crosstab query (you'll find them here in the script section) and to this at the client app.
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Any chance to change the underlying table design? It really looks like you have something like this
year 01 02 03 04 05
2003 ...some numbers
2002 ...some numbers
If so, that should be the presentational result from your data not the table design itself. I would rather create a table with a column for the date and another column for the number. Then you can, for example, insert something like
01.01.2004 30,234.93
01.02.2004 50,234,12
Now for getting into your presentation format, you can use a crosstab query (you'll find them here in the script section) and to this at the client app.
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hmph, obviously the new forum software does not have fllod control or it is not activated. Sorry for posting twice. Too much coffein got me nervous fingers
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Steve, thanks for the quick answer, how come you prefer the option that does not use dynamic sql? I used dynamic sql alot in MS Access and would like to know why this is not recommended in SQL server.
-
The data is dumped in this format from the corporate financial mainframe system. I am creating a datawarehouse for reporting purposes and trying to decide how much effort we should put into rearranging the data structures. I think that we could do this using DTS, but since I am new SQL server, there is alot to learn and I trying to stay focused on getting some reports created that present accurate data, in the future we have an opportunity to implement a properly designed OLAP system. Do you have any suggestions that could help me learn more about these issues?
-
If you want to know about the cons and pros of dynamic sql take alook at this
* Noel -
Hey noeld, that's my reference link
Actually, although both URL are the same you'd better use this one http://www.sommarskog.se/dynamic_sql.html
It's easier to remember as the other.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Do you have any suggestions that could help me learn more about these issues?
First of all try not to mimic some kind of Excel Pivot table stuff in SQL Server. You will certainly get this to work, but I bet after some time painfully become aware of the limitations of this approach as to scalability and performance. Also I guess your data integrity will be blown up within a very short time. Here's one very bad example from my company.
I' m not going to argue about it, because our Chief Marketing Officer is the creator of the table (...and he's very proud of it, so arguing might be dangerous )if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BeantragtesGeschäft]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BeantragtesGeschäft]
GO
CREATE TABLE [dbo].[BeantragtesGeschäft] (
[ZwNr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vorjahr] [int] NULL ,
[1] [smallint] NULL ,
[2] [smallint] NULL ,
[3] [smallint] NULL ,
[4] [smallint] NULL ,
[5] [smallint] NULL ,
[6] [smallint] NULL ,
[7] [smallint] NULL ,
[8] [smallint] NULL ,
[9] [smallint] NULL ,
[10] [smallint] NULL ,
[11] [smallint] NULL ,
[12] [smallint] NULL ,
[13] [smallint] NULL ,
[14] [smallint] NULL ,
[15] [smallint] NULL ,
[16] [smallint] NULL ,
[17] [smallint] NULL ,
[18] [smallint] NULL ,
[19] [smallint] NULL ,
[20] [smallint] NULL ,
[21] [smallint] NULL ,
[22] [smallint] NULL ,
[23] [smallint] NULL ,
[24] [smallint] NULL ,
[25] [smallint] NULL ,
[26] [smallint] NULL ,
[27] [smallint] NULL ,
[28] [smallint] NULL ,
[29] [smallint] NULL ,
[30] [smallint] NULL ,
[31] [smallint] NULL ,
[32] [smallint] NULL ,
[33] [smallint] NULL ,
[34] [smallint] NULL ,
[35] [smallint] NULL ,
[36] [smallint] NULL ,
[37] [smallint] NULL ,
[38] [smallint] NULL ,
[39] [smallint] NULL ,
[40] [smallint] NULL ,
[41] [smallint] NULL ,
[42] [smallint] NULL ,
[43] [smallint] NULL ,
[44] [smallint] NULL ,
[45] [smallint] NULL ,
[46] [smallint] NULL ,
[47] [smallint] NULL ,
[48] [smallint] NULL ,
[49] [smallint] NULL ,
[50] [smallint] NULL ,
[51] [smallint] NULL ,
[52] [smallint] NULL ,
[ø 2000] [int] NULL ,
[ø 1999] [int] NULL
) ON [PRIMARY]
GO
There is a column for each week in a year to store some numeric information. A far better approach would be
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BeantragtesGeschäft]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BeantragtesGeschäft]
GO
CREATE TABLE [dbo].[BeantragtesGeschäft] (
[ZwNr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
some_date datetime not null,
some_number smallint not null
) ON [PRIMARY]
GO
Much easier code to retrieve and modify the data. Now when it comes to presenting the data, you can of course do this in the way you are used to, that is some kind of cross tab.
You might find it useful to read some fundamentals on database design and normalization. For example this http://www.sqlservercentral.com/columnists/bkelley/normalization.asp
Several examples of data models can you find here
http://www.databaseanswers.com/data_models/
But as you are using this for an OLAP system, I think there are other valid rules as for an OLTP. I'm not familiar with OLAP stuff, but someone else here surely is.
HTH a bit
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I learned form the Master
* Noel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply