I've done both a CTE and a pivot, but never together. I did see a few examples out there and followed them, but mine isn't working. I have four 'tables' within the CTE, and then my final select statement joins all of them and attempts to pivot. My error is:
SELECT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD AS PlannedTY,
ActualSalesAmtUSD
FROM dbo.FactInventoryHistory HIS
INNER JOIN DimLocation LOC ON
His.LocationType_Code = Loc.LocationType_Code
INNER JOIN DimDate Date ON
His.Week_idx = Date.Date_Idx
INNER JOIN DimScenario Scenario ON
His.Scenario_Idx = Scenario.Scenario_Idx
WHERE Scenario_Code = 'FY08P'
GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,
id | value ========== 1,abc 1,def 1,ghi 2,def 2,jkl
I want these values to go horizontally into another table matched on id, to look like this:
id | value ======== 1,abc def ghi 2, def jkl
I built a cursor to parse through it but was taking forever (there's 185,000 records in the reference table). Any idea's on the fastest way to perform this function?
gud day.please help me. im working right now on a case study that willretrieve/produce a simple report on sql. my problem is I dont know howto pivot queries like in access. please help me. thanks
I want to pull data from XLs file and put them in a table.
Source file looks like this.
Month
Mar-07 Apr-07 May-07
Non-Accruals
$304,732,515 $307,051,978 $308,274,921
REO
$115,072,839 $123,957,394 $149,744,174
Home Equity Total NPA
$419,805,354 $431,009,372 $458,019,095
Destination table should look like this. Date(Only the first row ) in XL file is in the following format. DATE(YEAR($O1)-1,MONTH($O1)-1,DAY($O1)). From second row onwords data format is Money type. I hope I need to convert the date row into SQL datetime type too. Otherwise it comes as NULL.
Month Non-Accruals REO Home Equity Total NPA
Mar-07 $ 304,732,515 $ 115,072,839 $ 419,805,354
Apr-07 $ 307,051,978 $ 123,957,394 $ 431,009,372
May-07 $ 308,274,921 $ 149,744,174 $ 458,019,095
Can i create a SSIS package to do this job? if so , How? I'm not sure which transformation should i used and how? Hope some one can help me
Can anybody help me with the following...I want to Pivot the following data
Pcode Year Month Mcode Value
2 2008 March EN10A 56349.1
2 2008 March EN10B 1061.6
2 2008 March EN10C 2.67
2 2008 March EN10D 8370
2 2008 April EN10A 819.31
2 2008 April EN10B 245.09
2 2008 April EN10C 33.38
2 2008 April EN10D 2.31
After Pivot...the data should be like this
Pcode Year Month EN10A EN10B EN10C EN10D
2 2008 March 56349.1 1061.6 2.67 8370
2 2008 April 819.31 245.09 33.38 2.31
Can we use Pivot function or is their a easier way for doing this...Also the MCodes are dynamic so now there are only 4 distinct MCodes but they may be more than four...
I’d like to get some data which includes month values bound to a data grid. The data is stored in a table like so:
Measure Month Value A June 10.00 A July 9.00 A Aug 11.00 B Jun 100.00 B Jul 98.00 B Aug 99.00 C Jun 0.75 C Jul 0.8 C Aug 0.91
I need to report the data like this: Measure Jun Jul August A 10 9 11 B 100 98 99 C 75% 80% 91%
This was simple in classic ASP. Just use two recordsets, create a new table cell for each month using the first recordset then use the second recordset for each row.
But is there a way to “Pivot� or rotate the data so I can use the DataGrid? It only seems possible if each month has its own column field in table. Each month add a new column.
I can restructure the database, if needed.
I thought about creating a Cube, but that seems to have its own limitations. For example what if I want to add a Column for Quarter and year totals? I don’t think it’s possible to show multiple planes like that in an query of a cube.
It seems that this might be resolved in the presentation layer or the data layer. Any Suggestions?
I need some help here in Pivoting the table. I have the table with the Following Columns.. and here is the sample data. Assume that below table will just have one weeks worth of data.
I can write a stored Proc using cursor, but I just want to learn how to do it with using cursors
In the following code examples I got to learn PIVOT, I found an error for SUM. However when this is ran against the AdventureWorks db it works fine. Notice it is using a table variable and not an actual table. What do I need to do to my db to get this to work?
I have a query in which I would like to pivot the resultsI presently have my results displaying something like this. OrderNumber Product OrderQuantity--------------- --------------- ----------------------0608 Prod1 30608 Prod2 120608 Prod3 2 What I am after is for the results to display something like this.OrderNumber Prod1 Prod2 Prod3 --------------- --------- --------- --------- 0608 3 12 2 This is using SQL Server ver 8.0
I'm trying using the GROUP BY CUBE aggregation. Currently I have this working as such:
SELECT ISNULL(CONVERT(VARCHAR,Date), 'Grand Total') Date ,ISNULL([1 Attempt],0) [1 Attempt] ,ISNULL([2 Attempts],0) AS [2 Attempts] ,ISNULL([3 Attempts],0) AS [3 Attempts] ,ISNULL([4 Or More],0) AS [4 Or More]
[Code] .....
Basically this is used to work similar to a Pivot table in excel. My data will look as follows:
Date 1 Attempt2 Attempts3 Attempts4 Or MoreTotal 2012-09-04 239 68 2 8 317
The problem I'm having is the Total column. Although this is summing the line values correctly, the total should be based on the sum not count of attempts i.e. 1 x 239, 2 x 68, 3 x 2, 4 x 8
If I change the FROM select clause to use SUM instead of COUNT
SELECT CONVERT(DATE,[Date]) Date ,ISNULL(AttemptsFlag,'Total') as Attempt ,SUM(NoOfTimes) AS Totals FROM XXXXX GROUP BY CUBE([Date],AttemptsFlag)
It will return the correct Total amount but not the right numbers for the Attempt groupings...
For the Data Driven Subscription in SSRS we are using the following stored procedure
In Step 3 - Create a data-driven subscription
create procedure spRSGetReportSettings
(
@ReportID as integer
) as
begin
set nocount on
declare @t as table(y int not null primary key)
declare
@cols as nvarchar(max),
@y as int,
@sql as nvarchar(max)
set @cols=stuff(
(select N',' + quotename(y) as [text()]
from (select ParameterName as y from Reportsettings where reportid=1) as Y
order by y
For XML Path('')),1,1,N'');
set @sql=N'select * from
(select reportid,parametername, parametervalue from ReportSettings where reportid= ' + Cast(@ReportID as varchar(5)) +' ) as D
pivot(min(parametervalue) for parametername in(' + @cols +N')) as p'
exec sp_executesql @sql
end
Basically the idea is to maintain a single report parameter setting table for multiple reports.
Structure of the table is as given below
ReportID, ParameterName, ParameterValue.
Using Pivot we can generate the ParameterName/ParameterValue combinations for each report. This stored procedure is working fine in query editors(Management Studio)
But, in SSRS it is giving any results.
In Step 4 - Create a data-driven subscription,
Get the value from the database drop down, I am not getting any database columns.
--Table 1 "Employee" CREATE TABLE [MyCompany].[Employee]( [EmployeeGID] [int] IDENTITY(1,1) NOT NULL, [BranchFID] [int] NOT NULL, [FirstName] [varchar](50) NOT NULL, [MiddleName] [varchar](50) NOT NULL, [LastName] [varchar](50) NOT NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [EmployeeGID] ) GO ALTER TABLE [MyCompany].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID] FOREIGN KEY([BranchFID]) REFERENCES [myCompany].[Branch] ([BranchGID]) GO ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch" CREATE TABLE [Mycompany].[Branch]( [BranchGID] [int] IDENTITY(1,1) NOT NULL, [BranchName] [varchar](50) NOT NULL, [City] [varchar](50) NOT NULL, [ManagerFID] [int] NOT NULL, CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED ( [BranchGID] ) GO ALTER TABLE [MyCompany].[Branch] WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID] FOREIGN KEY([ManagerFID]) REFERENCES [MyCompany].[Employee] ([EmployeeGID]) GO ALTER TABLE [MyCompany].[Branch] CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID --generated IDs = GID Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee WHERE EmployeeGID= 39
Well this might look like a very basic error: I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the REFERENCE constraint "FK_Branch_ManagerFID". The conflict occurred in database "MyDatabase", table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
Is there a format function in MDX which will help me solve the following scenario:
One of my measures is Net Sales and I have tried all different formats in Analysis Services so that it shows without the decimal places i.e. instead of showing the Net Sales for any selected combination of Dimensions as 123,456.04, I want to show it as 123,456
When I browse the cube (in Analysis Services), I can meet my requirement. However, in my front-end (Microsoft Data Analyzer), I am still getting it as 123,456.04 (even when the same in Analysis services, i.e. when I browse the cube, is being shown as 123,456).
I do not have much choice at the moment and am stuck with Microsoft Data Analyzer and unfortunately have not been able to solve this :(
Can someone think of a solution/workaround/use of MDX which will help me get the results being displayed without the decimal. Is there a format function that I can use in MDX and how to use it???
Hello,What is uniqueidentifier as a data type?Also what is the data type for setting unique STRINGS ((nchar,nvarchar), for example to be used for emails and user names in a userregistration system).SQL Server does not allow me set primary keys for columns where datatypes are not INT.Thanks in advance.
I've been working on a performance review web application (i.e., employee's annual reviews done via the web). In the process of creating the application I've been teaching myself .NET - maybe not the best way to do it but I've been learning a lot. However, I still feel like I'm not doing something right.On each Page_Load I'm doing database work with a data reader: Reading the data in, displaying it, letting the user add, edit, or delete it, etc. So every Page_Load code behind looks like this: string sql = "SELECT UserID, Passwd, RecID, Name FROM UserList";
SqlConnection myConn = new SqlConnection("Server=BART; Database=WSSD; User ID=sa; Password=wss1231"); SqlCommand cmd = new SqlCommand(sql, myConn); SqlDataReader dr;
myConn.Open(); dr = cmd.ExecuteReader(); And so forth and so on. Now since I re-use this code again and again - I imagine it's a good idea to implement my connection code in a class that I can re-use easily. But I have no idea where to start on something like that. What can I say? I'm a newb. A push in the right direction would be great.
I have the following tablestblUserdatausercode username firstname lastname5 peter peter smith11 john433 john doe15 simonsays Simon SmithtblEventsID postedbycode title eventtext createdate1 5 woodstock 'oldies' 12/12/20082 11 love parade 'dance all night 1/1/20083 11 spring break 'great party' 2/2/2006tblEventVisitorsusercode eventid5 15 311 111 211 3As you can see User John433 is going to 3 events.But I only want to select the one that has the first upcoming startdate bigger than now: getdate()Desired output would be:username firstname lastname eventid title eventtext eventdatepeter Peter Smith 1 woodstock 'oldies' 12/12/2008john433 john doe 2 love parade 'dance all night 1/1/2008simonsays Simon Smith NULL NULL NULL NULLHow can I make such a selection? (perhaps see this thread for similar info: http://forums.asp.net/t/1201266.aspx)Thanks!
I tired the above tips but it appears that if I try to do this in my DTS (which appends data),the logic of the DTS will change. A single arrow also gets added whichI think represents a simple mapping/transformation rather than a append. To clarify my point, please note the attached image which represents that the data is being appended (due to the many sided arrows pointing to the source and destination - visible under the Transformations tab of my DTS).
Sincerely hoping that my post is clear, can someone help me find how to make changes in a DTS (which appends data) and ensuring that thelogic remains the same i.e. it should append data.
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4) RETURNS text AS $BODY$ DECLARE rec record; str text; comstr text; BEGIN str := ''; comstr := ''; FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP str := str || comstr || rec.classname; comstr := ','; END LOOP; RETURN str; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
I am backing up the database using the Database Maintenance Plan.
Everything is in place. I want to save the file name in the format yyyy-mm-dd_finbck.bak. how can i assign the date from here itself ? this should be done through the DB maintenance plan only.
is it possible ? can we assign the date to the backup file through the DB maintenence plan ?
Could anyone guide me to prepare for the Interview in SQL SERVER 2005.
I have studied the relevant things from background knowledge from the SQL Server books. But is there any specific areas in which I have to concentrate for the sake of Interview?
We had been running SQL Server without any control of security (sincethe company is very small -100 employees). All of us know the adminpassword and has been accessing the database as admin. Our databaseserver crashed due to hardware failure twice last month and we lost alot of important data. Now the management is taking the control ofserver access seriously.SQL Enterprise manager is installed on many PCs and any one can deleteany database with a right click.My question is:1. Can the enterprise manager be installed on client's PC with alimited right (or as a user not as admin)?We need to limit the user's access of using the Enterprise Manager.In other words, how can we set this up for different users.2. How can we keep running SQL Server if one server fails?Clustering or Replication or Mirroring? OI would highly appreciate if you could direct me to any website orresources on how to set up security of SQL Server (2000 with the latestservice pack).Thanks a million in advance.Best regards,Mamun
Any idea the cause of this error. I got it while trying to execute a stored proc (in sql 2005). I had just restored database and below error is stucking me to move ahead. Any help please
Error invoking method 'ExecuteQuery' for transaction (Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding)
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4) RETURNS text AS $BODY$ DECLARE rec record; str text; comstr text; BEGIN str := ''; comstr := ''; FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP str := str || comstr || rec.classname; comstr := ','; END LOOP; RETURN str; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
I have a data including "year", "month" and "day" columns and I'm using these columns as parameter to create a report. I'm using the parameters successfully in my report. I import all possible year-month-day combinations but in my data there are some unmatched combinations. (For ex, for 2007 there's just last 3 months of the year, and some days are missing) To make the report more affective, I wanna make the parameters dynamic; when I choose 2007 in year-combobox I wanna see just the related months (not all 12) and the related days after I choose the month. Is it possible in reporting services?
