generating month column with dynamic sql

Can someone help me generate the month column by using dynamic sql with this query:

Right now I only have Sept and Oct 08 in the database I hope this makes sense. 

SELECT Sort,Reg,

SUM(CASE WHEN MONTH(DowrDt)=9 AND YEAR(DowrDt)=2008 THEN PctInt Else 0 END) AS [Sep 08],

SUM(CASE WHEN MONTH(DowrDt)=10 AND YEAR(DowrDt)=2008 THEN PctInt Else 0 END) AS [Oct 08],

SUM(CASE WHEN MONTH(DowrDt)=11 AND YEAR(DowrDt)=2008 THEN PctInt Else 0 END) AS [Nov 08],

SUM(CASE WHEN MONTH(DowrDt)=12 AND YEAR(DowrDt)=2008 THEN PctInt Else 0 END) AS [Dec 08],

SUM(CASE WHEN MONTH(DowrDt)=1 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Jan 09],

SUM(CASE WHEN MONTH(DowrDt)=2 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Feb 09],

SUM(CASE WHEN MONTH(DowrDt)=3 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Mar 09],

SUM(CASE WHEN MONTH(DowrDt)=4 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Apr 09],

SUM(CASE WHEN MONTH(DowrDt)=5 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [May 09],

SUM(CASE WHEN MONTH(DowrDt)=6 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Jun 09],

SUM(CASE WHEN MONTH(DowrDt)=7 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Jul 09],

SUM(CASE WHEN MONTH(DowrDt)=8 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Aug 09],

SUM(CASE WHEN MONTH(DowrDt)=9 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Sep 09],

SUM(CASE WHEN MONTH(DowrDt)=10 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Oct 09],

SUM(CASE WHEN MONTH(DowrDt)=11 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Nov 09],

SUM(CASE WHEN MONTH(DowrDt)=12 AND YEAR(DowrDt)=2009 THEN PctInt Else 0 END) AS [Dec 09]

FROM #tempintnc

GROUP BY sort,Reg

Order by sort, reg

 

 

0
JJ297
11/21/2008 6:48:13 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
1110 Views

Similar Articles

[PageSpeed] 28

You need to use DYNAMIC Pivot command for this. There is a thread here there solution for this problem is provided by limno.

http://forums.asp.net/p/1345606/2760642.aspx#2760642


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
11/21/2008 7:40:24 PM

Thanks looked at the thread but I don't know how to change this stored procedure into a pivot table.  I'm a newbie to this could someone help me with it?

 Thanks!

USE [iClaims]

GO

/****** Object: StoredProcedure [dbo].[iClaimsNat] Script Date: 11/25/2008 14:45:13 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[iClaimsNat]

@Rpt char(1),

@Period char(1)

As

SET
NOCOUNT ON

 

select ZipCd = Case when RSDNC_ZIP5 is Null then MAILG_ADDR_ZIP5

else RSDNC_ZIP5 end, convert(char,MonthDt,1) as DowrDt, INET_IND, COSSN

into #tempzip

from iClaims

If @Rpt = '1' and @Period = 'M'

begin

Select Sort='1',Reg='NAT', a.DowrDt, iCnt, tCnt

into #tempint

From

(select DowrDt, Count(COS) as iCnt

from #tempzip

where INET_IND <> 'N'

Group by DowrDt

)a

inner join

(select DowrDt, Count(COS) as tCnt

from #tempzip

Group by DowrDt

)b

on a.DowrDt = b.DowrDt

 

insert #tempint

Select Sort =c.Region,Reg=c.Reg, c.DowrDt, iCnt, tCnt

From

(select Region, Reg, DowrDt, Count(COS) as iCnt

from #tempzip inner join ZipCodes on zipcd = zip

where INET_IND <> 'N'

Group by Region, Reg, DowrDt

)c

inner join

(select Region, Reg, DowrDt, Count(COS) as tCnt

from #tempzip inner join ZipCodes on zipcd = zip

Group by Region, Reg, DowrDt

)d

on c.DowrDt = d.DowrDt and c.Region = d.Region

end

0
JJ297
11/25/2008 7:51:57 PM
Reply:

Similar Artilces:

SQL Injection and Dynamic SQL
The consensus on avoiding SQL injection attacks is to pass all your collected data to a stored procedure. If you have to return a result set from a complex form, the answer seems to be to use dynamic SQL. On further study, I discovered that dynamic SQL has a lot of potential pitfalls and low and behold can still be vulnerable to injection attacks. I know that I am not the first person to ever try and collect data from a web form and return a result set, so what are the best practices? Say I have a complex form with about 25 search criteria (most of which are optional), I go through all t...

Dynamic SQL in Sybase SQL
Im trying to create a bit of Dynamic SQL in a Sybase stored procedure, im used to MS SQL Server so im not sure what im doing wrong in Sybase. Heres my bit of code declare @sql varchar(100) declare @sql2 varchar(100) Select @sql = '''0001/100212'',''0001/100218'',''0001/100240'','0001/100243''' select @sql2 = 'select * from table where table_tra_code in (' + @sql + ')' Exec @sql2 now i would have thought this would have executed @sql2 and returned a result set, but no :(. Any ideas?, id...

Dynamic SQL in Transact SQL
Does Dynamic SQL can be done in transact sql format in stored procedure? If yes, any reference can be read? Thx..... ...

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information
Hi I am getting follwong error when trying to run the code  Private _objCommand As New OleDbCommand() _objConnection.ConnectionString="....."_objConnection.Open() _objCommand.CommandType = CommandType.StoredProcedure _objCommand.CommandText="Sp001"_objCommand.Connection = _objConnection Dim da As New OleDbDataAdapter(_objCommand)Dim cb = New OleDbCommandBuilder(objAdapter) da.InsertCommand = cb.GetInsertCommandda.UpdateCommand = cb.GetUpdateCommandda.DeleteCommand = cb.GetDeleteCommand Dataset ds=da.Fill(ds, "Rule") When I am trying to run this ...

Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.
Hi, i Realy need solution of this problem. I am trying to follow a tutorial ->http://msconline.maconstate.edu/tutorials/ASPNET20/default.htm    , then -> go to  11. Data acces with dataset   I implement  Everything what its saying, but when i am tryning to delete record its saying : Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.Now i have debug my code, its getting result from "name" variable so, Dataset is getting data from table, then why its saying : s...

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
Hey, I changed the database name in the initial cataloge in the web.config conncetion string so that it now connects to other databas that contains same tables as the old one,but now i am getting that error at the update stmt ! thank u in advance Hiba Do you have the primary key and/or unique fields defined in the new database table?  The Update command only knows how to update if it has access to a unique field...  This could explain why it worked in the first database but not the new one.---------------------------------------MCP - Web Based Client Development .NET 2.0...

Is it possible to generate the columns dynamically using SQL SERVER Reporting Services 2005
Hi all, In my report no of columns and column names is not fixed, Is it posssible to generate the columnss dynamically? I have taken care of the query, I wrote a procedure that generates a select statement dynamically depending on a condition.. But how to do the front ent part? Thanks in advance     Yes, this may help you... http://www.codeproject.com/KB/reporting-services/DynamicReport.aspxAgustin M Rodriguez, MCSDHelp me reach the next level, mark my post as the answer if it helped you reach a solutionTambién te puedo ayudar en español Than...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
error Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. It happends when I try execute this code. it highlights < objDA.Update(objDS, "eAndD"); > void save_click(Object s, EventArgs e){dtmDate = myCalendar.SelectedDate;int row;objDV = objDS.Tables["eAndD"].DefaultView;objDV.Sort = "date";row = objDV.Find(dtmDate); //if date exsists then re-set value to current valueif (row != -1){   objDR = objDT.Rows[row];   Response.Write(objDR["discription"].ToString());&n...

Avoiding SQL Injection with Dynamic SQL
I am exclusively using Stored Procedures to access the database, i.e. there are no Ad-Hoc SQL statements anywhere in the C# code. However, one thing I need to be able to do is to allow filtering for data grids on my ASP.NET page. I want to do the filtering in the Stored Procedure using Dynamic SQL to set the WHERE clause. However, one fear of mine is SQL injection from the client. How can I avoid arbitrary SQL injection, yet still allow for a dynamic WHERE clause to be passed into the stored procedure? Jason Pacheco From here http://www.microsoft.com/technet/prodtechnol/sql/2000/maintai...

Linq to SQL. sproc or dynamic sql?
Im of the old school belif that all data should come from a DB via sprocs for many reasons that i wont go into here. Im getting into website creation and want to call data from my db via linq to sql. I will want to have some form of pagination. is this easily possible using sprocs( without modifying the sprocs) or are you better just use dynamic sql and skip and take keywords? Hi, Please refer to the following link below for the information about "LINQ to SQL (Retrieving Data Using Stored Procedures)" http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrie...

generating sql scripts from SQL Anywhere
Is it possible to generate SQL scripts from Sybase Central? If not, what's the best way to do that? Thank you That depends on what you want those scripts to do. For example, the UNLOAD wizard creates a SQL script that completely reloads a database. -- Paul Horan[TeamSybase] "Jim" <jimsjbox@yahoo.com> wrote in message news:41b4e51f@forums-1-dub... > Is it possible to generate SQL scripts from Sybase Central? > > If not, what's the best way to do that? > > Thank you > > I just want to generate a CREATE script for a...

SQL function using dynamic sql.
 Hi all I am trying to pass a column name as a variable in a function but it will not let me.  I am sure it is something stupid. I know that you cannot pass a column in a normal select statement as a variable. You have to execute it dynamically, however you cannot use the exec in a function. Please see code belowALTER FUNCTION dbo.GetData(@ColumnName varchar(50))RETURNS Decimal(18,4)ASBEGINDECLARE @Value Decimal(18,4)SET @Value = (SELECT + @ColumnName + FROM Policy   WHERE Grade = 'Revised') -- Tried this way, does not work need to use ExecSET @Value = Exec(&...

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...

Web resources about - generating month column with dynamic sql - asp.net.sql-datasource

Vogtle Electric Generating Plant - Wikipedia, the free encyclopedia
... / 33.14333; -81.76056 Coordinates : 33°8′36″N 81°45′38″W  /  33.14333°N 81.76056°W  / 33.14333; -81.76056 The Alvin W. Vogtle Electric Generating ...

Ivanpah Solar Electric Generating System - Spring 2013 - a set on Flickr
... Commons Getty Collection Galleries World Map App Garden Camera Finder The Weekly Flickr FlickrBlog Upload Sign In Ivanpah Solar Electric Generating ...

Generating Electricity By Walking - Google Science Fair 2014 - YouTube
Did you know that you can produce electricity by just walking? Here's my documentation of the gizmo that I've conceptualize over the years. It's ...

Generating media attention was the aim of New York Times attack: Akamai
Cyber attacks, such as the recent one on the New York Times, are more about gaining attention than causing damage, according to Akamai.

FourPlay review: Generating momentum with improvisation and surprise
From string quartet into a rock band 19 years ago and FourPlay is presenting `interesting compositions, with finely detailed arrangements', John ...

Brazilian inmates shorten sentences by generating power
An innovative program in Brazil's southeastern Minas Gerais state allows inmates at a prison in to reduce their sentences in exchange for generating ...

Live blog of MagNet2013 Conference: Generating Publicity—The Right Way
Live blog of MagNet2013 Conference: Generating Publicity—The Right Way

Generating interest in homegrown power
Generating interest in homegrown power People's Daily Online ... are dealing with the preliminary paperwork. "The method was absolutely new ...

IHG generating over $20 million a month in revenue from mobile bookings
InterContinental Hotels' emphasis is on developing booking apps and mobile features to engage with guests in every step of their journey.

TIMELINE: Shutdowns at San Onofre Nuclear Generating Station over 13 years
This timeline of events looks at shutdowns at the San Onofre Nuclear Generating plant over the past 13 years.

Resources last updated: 1/15/2016 8:52:26 AM