Can we PIVOT on more than 1 column in SQL SERVER 2005? For eg,
I have this select using Pivot, but I need to pivot on a second column too. I need to see a fourth column, in the output, that will be the TOTAL across for each requesters.
How can I do that.
select case when requester is not null then requester else 'Unknown' end as Requester,
[Pending],[PENDING - RECORDING INFORMATION] from
(select case when requester is not null then requester else 'Unknown' end as Requester,
case when AMPSSTATUS is not null then ampsstatus else 'Null'end as Ampsstatus,loannum
from TABLEA
)p pivot (count(loannum)
for AMPSSTATUS in
([Pending],[PENDING - RECORDING INFORMATION])) as pvt
I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week. Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server e.g,
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'
I want to grab the resultant pivoted table back into excel. how to do it?
USE [Database_ABC] GO
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'
The program shortcut "Visual Studio 2005 Command Prompt" seems to be mising from the "Visual Studio 2005 Command Prompt" Start menu added by the current Microsoft SQL Server 2005 Express Edition Toolkit. Where is it? How to workaround?
I am trying to do Download details SQL Server 2005 Samples and Sample Databases (April 2006) -> GettingStartedWithSQLSamples.htm which says "a. Open a Microsoft Visual Studio 2005 command prompt. Click Start, point to All Programs, point to Microsoft Visual Studio 2005, point to Visual Studio Tools, and then click Visual Studio 2005 Command Prompt." but I can find no such command prompt within "Visual Studio Tools", only "Visual Studio 2005 Remote Debugger{, Configuration Wizard}".
What's wrong? How to fix or workaround? I'd install .NET SDK 2.0 to get it's Command Prompt but that's about 570MB merely for a command prompt!
I have a query which I want to convert It PIVOT query
SELECTÂ Â Â Â Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName, SUM(CashSalesDetail.Qty) AS QtyFROMÂ Â Â Â Â Â Â Â CashSalesDetail INNER JOINÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â CashSales ON CashSalesDetail.CSNo = CashSales.CSNo INNER JOINÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Parties ON CashSales.PartyID = Parties.PartyIDWHEREÂ Â Â Â (CashSales.TransDate >= CONVERT(DATETIME, '2014-07-01 00:00:00', 102)) AND (CashSales.TransDate <= CONVERT(DATETIME, '2015-06-30 00:00:00', 102))GROUP BY Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName
following is my requirement after summing up qty of each area
ProductName     area a        area b      area c abc                         10               0               20 def                          1                 4              2 ghi                          5                 3              10 jkl                            7                15             3
Note: numeric values are Quantity of each product in each area
The following is a SAMPLE data from an excel spreadsheet. This SAMPLE data has many other fields as date. Here I have only used two date columns i.e. 28 Dec 2006 and 29 Dec 2006 This data needs to be exported into sql server 2005 table which has the fields below where I have placed the data into a table. How can this be done please?
data:
Ref Sector Name 28 Dec 2006 29 Dec 2006 1 Sovereign RUSSIA 05 null 173.21 2 Sovereign RUSSIA 07 102.99 102.22 3 Sovereign RUSSIA 10 114.33 104.63 4 Sovereign RUSSIA 18 115.50 145.50 ...
sql server table
create table tblData ( DataID int, Ref int, Sector varchar(20), Name varchar(20), Date datetime, value decimal(6,2) )
DataID Ref Sector Name Date value 1 1 Sovereign RUSSIA 05 28 Dec 2006 null 2 1 Sovereign RUSSIA 05 29 Dec 2006 173.21 3 2 Sovereign RUSSIA 07 28 Dec 2006 102.99 4 2 Sovereign RUSSIA 07 29 Dec 2006 102.22 5 3 Sovereign RUSSIA 10 28 Dec 2006 114.33 6 3 Sovereign RUSSIA 10 29 Dec 2006 104.63 7 4 Sovereign RUSSIA 18 28 Dec 2006 115.50 8 4 Sovereign RUSSIA 18 29 Dec 2006 145.50 ...
How do you add a specific timestamp to a backup? For example, if the backups are going to the same drive location on disk and you want to retain 3 days worth of backups online, how do you add the timestamp to the filename to make each backup unique?
F:MSSQL.1MSSQLBackup and your user database is <xyz>_<timestamp>.bak The user database dumps each night at 9 PM.
As you know SQL Express by deefault accepts only local connections
For a particular requirement i have to change SQL Server Surface Area Configuration to accept remote connections by dos command Anyone know this command? thanks Marco
What is the correct command line parameters to SQLEXPR32.exe that will default the Log On As: properties for the instance to use the Built-in account of Local System & Local Service?
I need it to be set to Local System immediately on install without having to go into the configuration manager. When I run the SQLEXPR32.exe right now I get the Network Service defaulted.
well as you can see from my thread SQLServer is new to me i am used to mysql and i c'ant find the equivalent anywhere on the net... Seams easy enough but i have been at it for 6 hrs and i give up... I am just making a quick database view tool. So please tell me.
How do i load the all tables of a database via an sql command?
Since it took me a while to find this i thought migth as well slap ip somewhere so here is the command to load all data bases... In any case worked for me so...
- > select * from master.dbo.sysdatabases;
And where in the ... can you find a reference to all sql server commands? ... Please.
I just started using SQL 2005 and having a bit of struggle.
I'm having difficulty trying to use the .ini file to install sql express. This is the command I use in Command Prompt: start /wait expresssetup.exe /settings setup.ini /qn echo %errorlevel%
Then I get an error level of €˜-204€™, which I couldn€™t find the meaning on the internet or anywhere else.
But when I move the parameters from the .ini file to the batch file and the process worked. SQL started installing, until it comes to the SQL Server Database Services component. Then I get the following error message:
SQL Server Setup could not validate the service accounts. Either the service accounts have not been provided for all of the services being installed, or the specified username or password is incorrect. For each service, specify a valid username, password, and domain, or specify a built-in system account.
I've heard that the Standard version supports the use of CommandNotification and that the Express version does not. What about the Workgroup edition? The reason I'm asking is because there is a substantial monthly difference in cost between the two versions if I were to have my site hosted. I've been developing with MS SQL 2005 Express so obviously I have not tested this caching feature. Are command notifications reliable? On paper, it sounds impressive and extremely efficient especially if a site is to have heavy traffic and you can't afford the overhead of heavy database processing that is useless if the data is unchanged. Does the Workgroup edition support automatic change notifications? Have automatic notifications been proven reliable and vastly more efficient than crude polling methods? Thanks in advance.
Hi all,At this moment I'm trying to get database mail working. According tosome people it should be easy. Well...that is not the case for me.I'm having the following error:The mail could not be sent to the recipients because of the mail serverfailure. (Sending Mail using Account 2 (2006-11-24T08:48:15). ExceptionMessage: Cannot send mails to mail server. (Command not implemented.The server response was: Command not Supported).)SQL Server 2005 is installed on a separate server and the SMTP Serveris Lotus Notes. connecting with port 25.Start and stopping the SQL Server and SQL Server agent i tried already.i tried sysmail_stop_sp and sysmail_start_sp.Sometimes when starting or stopping the Mail with the followingcommands :exec sysmail_stop_spGOexec sysmail_start_spGOI will get the following error:Msg 233, Level 20, State 0, Line 0A transport-level error has occurred when sending the request to theserver. (provider: Shared Memory Provider, error: 0 - No process is onthe other end of the pipe.)So any help would be appreciated.Hennie
It installs the SQL Server and Reporting Services. It configures (creates Virtual Directories for report server and database) but it is not initializing the Report Server.
Having done the installation when i check the Report Server Status it was running and everything seems to be OK except it wasn't initialized.
Everything goes on fine until the SQL Server Database Sersvices configuring and a pop-up says the following:
SQL Server Setup could not validate the service accounts. Either the service accounts have not been provided for all of the services being installed, or the specified username or password is incorrect. For each service, specify a valid username, password, and domain, or specify a built-in system account.
I have no idea how to come over this. Any help would be appreciated.
PS I don't know what AGTACCOUNT or AGTPASSWORD is, so maybe the error might be there. If not, anybody willing to explain to me what it is, it would be GREATLY appreciated.
I would be very grateful if someone could take a look at my command line install and offer advice on if I am doing it correctly.
The reason I am asking is because I cannot seem to get the server installed without having errors during the installation: "The SQL Server (MSSQLSERVER) service terminated with service-specific error 17113 (0x42D9)"
Looking at the server error log indicates that master.mdf was not in the data directory (I have however specified a different data directory for ease of backup by our software and this is created during the install but does not have this database in the data directory - it is however in the template data directory!
I also get this error when the SQL Server service is started: "The SQL Server (SQLEXPRESS) service terminated with service-specific error 3417 (0xD59)."
All advice is welcome. Please see below my batch file. Also, if you think I should do this in a different way then please let me know. I basically want to install SQL server on a customer computer and automate the install so the custoemr does not need to enter any information.
The goal of this install is: [1] Deply a custom .Net application and SQL server at the same time on the same computer without user input. [2] Allow remote .Net applications (designed by myself also) to connect over a LAN (and later maybe over a WAN) to the database to the above database.
The command line is this (I have also included the batch file I am usign below). For ease of reading I have put the parameters on a new line below:
SET MODE=/qb SET SETUP_EXE=sqlfilessetup.exe %MODE% SET SQLDATADIR=c:SqlData
SET SET_USERNAME=username="CustomerUsername" SET SET_COMPANYNAME=companyname="OurCompany" SET SET_ADDLOCAL=addlocal="SQL_Engine,SQL_Data_Files,SQL_Replication,Connectivity,SDK" SET SET_DISABLENETWORKPROTOCOLS=disablenetworkprotocols="0" SET SET_INSTANCENAME=instancename="MSSQLSERVER"
SET SET_INSTALLSQLDATADIR=INSTALLSQLDATADIR="%SQLDATADIR%"
SET SET_SAVESYSDB=SAVESYSDB="1" SET SET_USESYSDB=USESYSDB="%SQLDATADIR%"
SET SET_SQLAUTOSTART=SQLAUTOSTART="1" SET SET_SECURITYMODE=SECURITYMODE="SQL"
@REM === RE-INSTALL PARAMS - NOT CURRENTLY BEING USED?? SHOULD I ADD THESE ALSO? === SET SET_REINSTALLMODE=REINSTALLMODE="omus" SET SET_REINSTALL=REINSTALL="SQL_Engine,SQL_Data_Files,SQL_Replication,Connectivity,SDK" SET SET_REBUILDDATABASE=REBUILDDATABASE="1"
I am using installshield to distribute SQL Server 2005 Express. I have the SQLEXPR.EXE file and I want to run it in /qb mode so the user can see the pretty dialogs pop up but not have to click anything.
Hi! I have a table Items(id, name, section, id_format, price) and a table Format(id, format,order) What I want to do is display: Name, Format1, Format2, Format3. How can I acheive that result?? THanks!! -JTM
Has anyone had success using it yet? We've been playing around with it and maybe we're doing it wrong, but so far we can't get it to produce anything useful. I mean, it pivots but we can't get it to do anything useful, like group an aggregate or anything.
I have SQL 2005 and am trying to do a pivot table. I am running into a lot of challenges. The first thing I am running into is it giving me the following error:
The following errors were encountered while parsing the contents of the SQL Pane: The PIVOT SQL construct or statement is not supported.
My table has the following columns:
Client Dollars Billed SlipDates
The there is a slip date for each client for for april 1, then may 1, etc.
SELECT Client, [4/1/2007 12:00:00 AM] AS April2007, [5/1/2007 12:00:00 AM] AS May2007 FROM (SELECT Client, DollarsBilled, SlipDates FROM dbo.MonthlyClientBillables) p PIVOT (SUM(DollarsBilled) FOR Client IN ([4/1/2007 12:00:00 AM], [5/1/2007 12:00:00 AM])) AS pvt ORDER BY Client
1) Production data with column headers: Key, Facility, Line, Time, Output 2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key Facility Line Time Output Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
Can I force the following measure to be visible for all rows in a pivot table?
Sales Special Visibility:=IF( Â Â HASONEVALUE(dimSalesCompanies[SalesCompany]) Â Â ;IF( Â Â Â Â VALUES(dimSalesCompanies[SalesCompany]) = "Sales" Â Â Â Â ;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany])) Â Â Â Â ;[Sales] Â Â ) Â Â ;BLANK() )
FYI, I also have other measures as well in the pivot table that I don't want to affect.
I have a simple pivot table (screenshot below) that has two variables on it: one for entry year and another for 6 month time intervals. I have very simple DAX functions that count rows to determine the population N (denominator), the number of records in the time intervals (numerator) and the simple percent of those two numbers.
The problem that I am having is that the function for the population N is not overriding the time interval on the pivot table when I use an ALL function to do so. I use ALL in other very simple pivot tables to do the same thing and it works fine.
The formula for all three are below, but the one that is the issue is the population N formula. Why ALL would not work, any other way to override the time period variable on the pivot table.
Population N (denominator): =CALCULATE(COUNTROWS(analyticJudConsist),ALL(analyticJudConsist[CurrentTimeInCare1])) Records in time interval (numerator): =COUNTROWS(analyticJudConsist) Percent: =[countrows]/[denominatorCare]
How can I apply "Min" formula under a "new measure" (calculated field) within a pivot table under Power pivot 2010?Can see that neither does it allow me to apply "min" formula directly "formula box" nor could find any other option.Intent formula: "=Min(1,sum(a:b))" this isn't allowed so all I can do is "=sum(a:b)".
I have simple pivot table (below screenshot with info redacted) that displays a population number ("N" below), this is the denominator, a cumulative numerator number (below "#") and a simple cumulative percent that just divides the numerator by the denominator. It cumulates from top to bottom. The numerator and percent are cumulative using the below functions. There are two problems with the numerator and percent:
1. When there is not a number for the numerator, there is no value displayed for both the numerator and the percent..There should be a zero displayed for both values. 2. When there has been a prior number for the numerator and percent (for a prior month interval) but there is no number for the numerator in the current month interval, the prior month number and percent are not displayed in the current month interval--see the 3rd yellow line, this should display "3" and "16.7%" from the second yellow line.Here is the formula for the numerator:
=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))) Here is the formula for the percent: =(CALCULATE(countrows(s1Perm1),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))
I have data in my Powerpivot window which was generated by a sql query. This data includes a field named 'Cost' and every row shows a value for 'Cost' greater than zero. The problem is that when I display this data in the pivot table all entries for Cost display as $0. At first I thought that maybe Cost was set to a bogus data type (such as 'text) but it is set to ''Decimal Number' so that's not the problem.Â
What is happening and how do I fix it so that my pivot table reflects the values for 'Cost'?
I have a data table that contains budget and actual data by month. Â I use the data to create a pivot that shows actual results next to budgeted results. Â I need a column that shows that variance between those columns. Â I think my issue is that the "Type" field contains actual and Budget. Â I sum on "Type". Â I can't seem to create a sum since those items are in the same field or am I missing something?
