Asp Forum - how to select only the last x columns from a table

rsjrny

3/21/2007 1:43:00 PM

Please bear with me I am just learning SQL

I have a table that contains the following data, these are monthly
useage counts

Prod Oct2007 Nov2006 Dec2006 Jan2007 Feb2007
Mar2007
XXXXV031 52 9 18 8 18 102
XXXXV011 0 0 0 0 0 0
XXXXV040 7 0 0 0 0 5

Each month a new column is dynamically added to the table and access
counts are
maintained in the new month column. So next month a new column Apr2007
will be
added and the counts will increment there.

I am unsuccessfully trying to write a select that will select only the
last 4
columns and maintain the actual column headers. So my results would
look like:

Prod Dec2006 Jan2007 Feb2007 Mar2007
XXXXV031 18 8 18 102
XXXXV011 0 0 0 0
XXXXV040 0 0 0 5

And next month if I executed the select I would see:

Prod Jan2007 Feb2007 Mar2007 Apr2007
XXXXV031 8 18 102 n
XXXXV011 0 0 0 n
XXXXV040 0 0 5 n

any help would be greatly appreciated

6 Answers

David Portas

3/21/2007 1:54:00 PM

On 21 Mar, 13:42, "rsjrny" <russlil...@yahoo.com> wrote:
> Please bear with me I am just learning SQL
>
> I have a table that contains the following data, these are monthly
> useage counts
>
> Prod Oct2007 Nov2006 Dec2006 Jan2007 Feb2007
> Mar2007
> XXXXV031 52 9 18 8 18 102
> XXXXV011 0 0 0 0 0 0
> XXXXV040 7 0 0 0 0 5
>
> Each month a new column is dynamically added to the table and access
> counts are
> maintained in the new month column. So next month a new column Apr2007
> will be
> added and the counts will increment there.
>
> I am unsuccessfully trying to write a select that will select only the
> last 4
> columns and maintain the actual column headers. So my results would
> look like:
>
> Prod Dec2006 Jan2007 Feb2007 Mar2007
> XXXXV031 18 8 18 102
> XXXXV011 0 0 0 0
> XXXXV040 0 0 0 5
>
> And next month if I executed the select I would see:
>
> Prod Jan2007 Feb2007 Mar2007 Apr2007
> XXXXV031 8 18 102 n
> XXXXV011 0 0 0 n
> XXXXV040 0 0 5 n
>
> any help would be greatly appreciated

Obviously this is a very silly design for a table. Your task would be
much easier if you added a single date column instead of extending the
design each month.

As it is you'll have to construct yet more dynamic SQL I'm afraid. You
could do that by querying the information_schema.columns view and
picking the columns that match the months you require.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

xyb

3/21/2007 1:55:00 PM

On 3?21?, ??9?42?, "rsjrny" <russlil...@yahoo..com> wrote:
> Please bear with me I am just learning SQL
>
> I have a table that contains the following data, these are monthly
> useage counts
>
> Prod Oct2007 Nov2006 Dec2006 Jan2007 Feb2007
> Mar2007
> XXXXV031 52 9 18 8 18 102
> XXXXV011 0 0 0 0 0 0
> XXXXV040 7 0 0 0 0 5
>
> Each month a new column is dynamically added to the table and access
> counts are
> maintained in the new month column. So next month a new column Apr2007
> will be
> added and the counts will increment there.
>
> I am unsuccessfully trying to write a select that will select only the
> last 4
> columns and maintain the actual column headers. So my results would
> look like:
>
> Prod Dec2006 Jan2007 Feb2007 Mar2007
> XXXXV031 18 8 18 102
> XXXXV011 0 0 0 0
> XXXXV040 0 0 0 5
>
> And next month if I executed the select I would see:
>
> Prod Jan2007 Feb2007 Mar2007 Apr2007
> XXXXV031 8 18 102 n
> XXXXV011 0 0 0 n
> XXXXV040 0 0 5 n
>
> any help would be greatly appreciated

you can use dynamic sql like this:
declare @sql nvarchar(4000)
select @sql = ''
select @sql = @sql + '
select ' +
month(dataadd(month,getdate(),-4))+year(dataadd(month,getdate(),-4))
....
+'from
table
'

xyb

3/21/2007 1:58:00 PM

On 3?21?, ??9?53?, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
> On 21 Mar, 13:42, "rsjrny" <russlil...@yahoo.com> wrote:
>
>
>
>
>
> > Please bear with me I am just learning SQL
>
> > I have a table that contains the following data, these are monthly
> > useage counts
>
> > Prod Oct2007 Nov2006 Dec2006 Jan2007 Feb2007
> > Mar2007
> > XXXXV031 52 9 18 8 18 102
> > XXXXV011 0 0 0 0 0 0
> > XXXXV040 7 0 0 0 0 5
>
> > Each month a new column is dynamically added to the table and access
> > counts are
> > maintained in the new month column. So next month a new column Apr2007
> > will be
> > added and the counts will increment there.
>
> > I am unsuccessfully trying to write a select that will select only the
> > last 4
> > columns and maintain the actual column headers. So my results would
> > look like:
>
> > Prod Dec2006 Jan2007 Feb2007 Mar2007
> > XXXXV031 18 8 18 102
> > XXXXV011 0 0 0 0
> > XXXXV040 0 0 0 5
>
> > And next month if I executed the select I would see:
>
> > Prod Jan2007 Feb2007 Mar2007 Apr2007
> > XXXXV031 8 18 102 n
> > XXXXV011 0 0 0 n
> > XXXXV040 0 0 5 n
>
> > any help would be greatly appreciated
>
> Obviously this is a very silly design for a table. Your task would be
> much easier if you added a single date column instead of extending the
> design each month.

:)
i always want to know why they make such design :)

> As it is you'll have to construct yet more dynamic SQL I'm afraid. You
> could do that by querying the information_schema.columns view and
> picking the columns that match the months you require.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
> --- ??????? -
>
> - ??????? -

Anith Sen

3/21/2007 2:01:00 PM

Change you table structure along the lines of :

CREATE TABLE tbl (
Product_id INT NOT NULL
REFERENCES Products ( product_id )
Period DATETIME NOT NULL,
Usage INT NOT NULL
DEFAULT ( 0 )
PRIMARY KEY ( Product_id, Period ) ) ;

--
Anith

=== Steve L ===

3/21/2007 11:36:00 PM

On Mar 21, 6:42 am, "rsjrny" <russlil...@yahoo.com> wrote:
> Please bear with me I am just learning SQL
>
> I have a table that contains the following data, these are monthly
> useage counts
>
> Prod Oct2007 Nov2006 Dec2006 Jan2007 Feb2007
> Mar2007
> XXXXV031 52 9 18 8 18 102
> XXXXV011 0 0 0 0 0 0
> XXXXV040 7 0 0 0 0 5
>
> Each month a new column is dynamically added to the table and access
> counts are
> maintained in the new month column. So next month a new column Apr2007
> will be
> added and the counts will increment there.
>
> I am unsuccessfully trying to write a select that will select only the
> last 4
> columns and maintain the actual column headers. So my results would
> look like:
>
> Prod Dec2006 Jan2007 Feb2007 Mar2007
> XXXXV031 18 8 18 102
> XXXXV011 0 0 0 0
> XXXXV040 0 0 0 5
>
> And next month if I executed the select I would see:
>
> Prod Jan2007 Feb2007 Mar2007 Apr2007
> XXXXV031 8 18 102 n
> XXXXV011 0 0 0 n
> XXXXV040 0 0 5 n
>
> any help would be greatly appreciated

the table design has violated the basic normalization rule for a
relational database. you should consider changing it. the 'fix' is not
a long term solution. maybe you inherit it from someone has no
knowledge of best practices and am stuck with it.

rsjrny

3/22/2007 8:27:00 PM

Thanks to everyone that replied. I was able to come up with the
following and using it in a loop to change the Ordinal_Position from
-0 to -n I am able to retrieve the data from the last x columns.

select @col1 =
( SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'PATUseage'
and ORDINAL_POSITION = (select MAX(ORDINAL_POSITION) - 0
FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME =
'PATUseage') )

set @SQL = 'select ProdVer,'+ @Col1 + ' from PATUseage Order by
ProdVer'
EXEC sp_executesql @sql

microsoft.public.sqlserver.programming

how to select only the last x columns from a table

rsjrny

David Portas

xyb

xyb

Anith Sen

=== Steve L ===

rsjrny

x Login to ForumsZone