.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Converting an Access pivot query to SQL 2005

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server

Hi, I am new to table pivot and would like to know how to go about converting the following query in Access to SQL Server 2005:


SELECT tbl_Proj_Budget.PROJ_ID,
FROM tbl_Proj_Budget 
GROUP BY tbl_Proj_Budget.PROJ_ID 

1) what does the 1st line mean?


2) How would I conver this to TSQL ?




View Complete Post

More Related Resource Links

Help converting an Acesss SQL query to SQL Server 2005 query


I am trying to convert this microsoft access query to sql 2005 query. Thank you for taking the time to look.

(This is taking a markup field (MultiplierCode) with a value like 1.45/1.5 and splitting into 2 numbers (1.45 would be the regular markup (based on the pay rate) and the 1.5 would be the overtime markup of the Bill Rate. Then updating orders and assignments based on that.)

UPDATE Order_Root INNER JOIN assignmentroot ON Order_Root.OrderID = assignmentroot.OrderID SET Order_Root.Bill = [order_root]![pay]*(Left([order_root]![MultiplierCode],(Len(Left([order_root]![MultiplierCode],InStr([order_root]![MultiplierCode],"/"))))-1)), Order_Root.OBillRate = [order_root]![pay]*(Left([order_root]![MultiplierCode],(Len(Left([order_root]![MultiplierCode],InStr([order_root]![MultiplierCode],"/"))))-1))*(Right([order_root]![MultiplierCode],Len([order_root]![MultiplierCode])-InStr(1,[order_root]![MultiplierCode],"/"))), Order_Root.DBillRate = [pay]*(Left([order_root]![MultiplierCode],(Len(Left("order_root]![[MultiplierCode]",InStr([order_root]![MultiplierCode],"/"))))-1)*2), assignmentroot.BillRate = [payrate]*(Left([order_root]![MultiplierCode],(Len(Left([order_root]![MultiplierCode],InStr([order_root]![MultiplierCode],"/"))))-1)), assignmentroot.OBillRate = [payrate]*(Left([order_root]![MultiplierCo

Converting Crystal Report 2005 to 2008 - Please Tell Me I'm Stupid :)


Hello peeps,

  I have a series of Crystal Reports that I can view/edit using Visual Studio 2005 with no issues.  The method I'm using is to simply click on the *.rpt file and it opens in the VS crystal editor with no issues.  The problem I'm having is that we are upgrading to Visual Studio 2008, and when I open the *.rpt file in 2008 it opens as a binary file.  I guess I'm used to everything else in Visual Studio prompting me to convert the older version to the newer version, so I'm not sure what to do in this case.

  Is this normal?  Is there an easy conversion method that I'm simply overlooking?  Am I screwed?  Who invented liquid soap, and why?

Answers to any of these questions would be greatly appreciated...

Pivot w/o aggregation...query not working

Hi all--Given this query, I need to make this query into a pivot table: select Personnel_id, First Name, Last Name, E-mail, Division, Region, Country, Manager, Question, Answer, Date Completed from #pivot where Question Answer 01. Received File No 02. Read File Yes 03. Signed File Yes ... Needs to look like this: Personnel_id First Name ... 01. Received File 02. Read File 03. Signed File ... Test01 Test No Yes Yes I have the following pivot query: SELECT [Personnel_id] ,[First Name] ,[Last Name] ,[Email] ,[Division] ,[Region] ,[Country] ,[Manager] ,[Question] ,[Answer] ,[Date Completed] FROM (SELECT [Personnel_id] ,[First Name] ,[Last Name] ,[Email] ,[Division] ,[Region] ,[Country] ,[Manager] ,[Question] ,[Answer] ,[Date Completed] FROM #pivot) topvt PIVOT (MAX([Personnel_id]) FOR Answer in ([1],[2], [3], [4], [5], [6], [7], [8] , [9], [10])) as pvt ORDER BY pvt.[Personnel_id] I get this error: Msg 207, Level 16, State 1, Line 83 Invalid column name 'Personnel_id'. Any ideas? Thanks!

SQL Reporting Service 2005 - share schedule report performace T-SQL query?

Hi, I have SQL 2005 reporting services Shared Schedules and each schedule has its own subscribed report. I would like to have T-SQL 2005 to find out performance loading on each schedule. i.e. MySchedule_1 has 10 reports in it and AVEGARE report eaxecutiontime is like 3mins 5sec      MySchedule_2 has 7 reports in it and AVEGARE report eaxecutiontime is like 4mins 9sec Pls can I have T-SQL 2005 on ReportServer database to find out load on each schedules (and more drill-down to each report level for execution time)?

Access the SQL Command Query Value Property from the OLEDB Container within a Script Task

Hello, I'd like to Access the SQL Command Query Value Property from the OLEDB Container within a Script Task so I can audit the queries which are executing. What is the syntax for this or does anyone have a sample on how to do this? Thank you

How to access and query SDF file?

I get a sdf file.  I try to access it using SSMS 2005 and I get this error: You are trying to access an older version of a SQL Server Compact Edition database. If this is a SQL Server CE 1.0 or 2.0 database, run upgrade.exe. If this is a SQL Server Compact Edition 3.0 or later database, run Compact / Repair. [ Db version = 3505053,Requested version = 3004180,File name = D:\test.sdf ] (SQL Server Compact Edition ADO.NET Data Provider) How can I find out the edition of the SDF?

T-SQL 2005 query for Group BY and not GROUP in same query for SQL reporting service use purpose?

Hi, I have SQL 2005 table like bellow @OrderTable I want to display all row data and GROUP BY data as well for SQL Reporting Service Matrix purpose...   declare @OrderTable TABLE (OrderID varchar(10),OrderType varchar(20),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06','O1',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06','O2',105.48,'2010-06-12') INSERT INTO @OrderTable VALUES('P07','O3',555.00,'2010-06-09') INSERT INTO @OrderTable VALUES('P08','O1',10.22,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O1',55.66,'2010-03-17') INSERT INTO @OrderTable VALUES('P06','O1',45.44,'2010-03-17') INSERT INTO @OrderTable VALUES('P07','O3',477.81,'2010-03-18') INSERT INTO @OrderTable VALUES('P07','O3',78.85,'2010-03-18') INSERT INTO @OrderTable VALUES('P06','O1',78.08,'2010-04-09') INSERT INTO @OrderTable VALUES('P07','O2',899.90,'2010-04-22') INSERT INTO @OrderTable VALUES('P08','O3',25.33,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',859.01,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',7433.89,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O1',1005.41,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O2',455.20,'2010-06-09') INSERT INTO @OrderTable VALUES('P07','O3',85.30,'2010-06-12') INSERT INTO @OrderTable VALUES

T-SQL 2005 table query

Hi, I have SQL 2005 table like below one declare @OrderTable TABLE (OrderID varchar(10),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',455.85,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',866.45,'2010-01-25') INSERT INTO @OrderTable VALUES('P06',749.61,'2010-01-28') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-03-23') Final result I am looking for is SET NULL value in SELECT query Whenever Same OrderID and OrderDate is found... OrderID  OrderValue   OrderDate P06         25.22   2010-01-24 P06        NULL             2010-01-24 P06      NULL             2010-01-24 P06        NULL             2010-01-24 P06      NULL              2010-01-24 P06 &

How to change connection string of a pivot table pointing to SSAS 2005 cube using excel 2003?

Hi All,I am not sure if I should have posted this query to Excel 2003 forum. But posting it here as it applies to SSAS 2005 as well.Ok, let me give the background before I tell the actual problem.We have users on ABC domain and the SSAS server is also on ABC domain. Users on this domain can acess the excel pivots by connecting to cube to browse the data. They leave the Userid & password field blank while they setup the connection string and it works fine. Thanks to windows authentication that takes the credentials of user logged in. Let's say I have two users A and B, they login to ABC domain with their own windows ids.  Now when user A creates a excel file having a cube pivot and then sends this file to user B, user B can refresh and modify the same excel file (he can select new measures to pivot, new hierarchies in filters and so on).Now, let's say I have another user, user C. He has excel 2003 installed on his PC and cannot migrate to excel 2007. He is on different domain XYZ but have a valid windows userid on domain ABC. The domain ABC & XYZ can not be setup to have trusted relationship. Now, when user A sends the same excel file to user C. When user C opens the file and try to refresh it or try to modify the pivot by selecting/deselecting any elements, he gets below error prompt:" An error was encountered in the transport layer." and "Errors in the

Sql Server 2005 ( remove header information from query output file "Job 'bustd_Daily_Query_Output_1

Dear All,I have scheduled job for querying output & asve it into particuler folder....but in the top of file i getting the message like "Job 'bustd_Daily_Query_Output_13July09' : Step 1, 'Query1' : Began Executing 2009-07-13 16:32:06"I want to remove this & want output in txt file format without above header....RegardsRavendra Cindia

Connect SQL Server 2005 to Ms Access on another computer

I'm trying to create a "Linked Server" on my SQL Server 2005.  This linked server is a reference to an Access File on a remote computer.  I've set up all the permissions and shared resources.  I've created an ODBC DSN to connect to the database.  If i open the DSN in excel, i can retrieve data.  If I open a linked server connected to the DB I get the following error when i try to run a query against it.   ------------------ OLE DB provider "MSDASQL" for linked server "LS_Specialty" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". OLE DB provider "MSDASQL" for linked server "LS_Specialty" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". OLE DB provider "MSDASQL" for linked server "LS_Specialty" returned message "[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'.  It is already opened exclusively by another user, or you need permission to view its data.". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LS_Specialty". ---------------------   Why does this DSN work in Excel and Access, but not in SQL

SQL Server 2005 Express - Does it require a Client Access License (CAL)

Do you need a Client Access License (CAL) for each device that will connect to a SQL Server 2005 Express edition database?

Converting a Stored Proc to a query

I have a stored procedure that I want to convert to a query that I can email to the DBAs to run.  The stored proc has 2 parameters (start and end date) as I need them to run the query in date chunks (i.e. 1/1/2010 - 1/31/2010) due to the size of the table. *** Please allow me to mark threads as answered and I will, Thank you ***

Access Control List tables in sql server 2005

Hi all, Can anybody help me in creating Acl tables.how to create them in sqlserver 2005 and how to use in my .net application

How to query the Active directory using SQL Server 2005

Hi, I am trying to query Active server from Sql Server 2005 and getting error I used following script to create link server EXEC sp_addlinkedserver 'ADSI3', 'Active Directory Services 111', 'ADSDSOObject', 'adsdatasource' and then trying to connect using following statement SELECT   * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://DC=local.mycompany.ca'' WHERE objectCategory=''User'' ') and getting following error An error occurred while preparing the query "SELECT * FROM 'LDAP://DC=local.resound.ca' WHERE objectCategory='User' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI". Please let me know if you know How to query the Active directory using SQL Server 2005 Thanks, Raj    Dreaming a world without any war in anywhere

Access Control List tables in sql server 2005

Hi all, Can anybody help me in creating Acl tables.how to create them in sqlserver 2005 and how to use in my .net application

SQL 2005 Query Assistance needed

Question: I need some assistance with the below mentioned code. Essentially what I need to do is insert a new row for an off-setting A/R account. For Example, I will have two rows (one for the charge and one for the adjustment each related by the PatientVisitId (distinct and primary key)). An example of this would look like this minus a lot of my fields, but to give you a fast visual: Charge: Credit = $225.00, Debit = $0.00 Adjustment: Credit = $0.00, Debit = $65.00 What I need is a new row for the difference. In this instance, I would get a new row "A/R" with a Credit = $0.00, Debit = $160.00 SET NOCOUNT ON ; WITH CTE AS ( --- Bring in the Charges SELECT 'Charge' AS [Type], pv.TicketNumber, pv.PatientVisitId, b.[Entry] AS BatchEntry, '100' AS [Fund], LEFT(ISNULL(fin.Ledger , '') , 5)AS [GL#], LEFT(ISNULL(fac.Ledger , '') , 4)AS [Prog], LEFT(ISNULL(ic.Ledger , '') , 4) AS [Srce], '' AS [Func], --- Need case statement here for Place Of Service LEFT(ISNULL(doc.Ledger , '') , 4)AS [EE], '99999' AS [Seg 1], '99999' AS [Seg 2], ' ' AS [Debit], (CONVERT(varchar, SUM(pvp.totalfee))) AS [Credit], ISNULL(ic.ListName,'Self') + SPACE(2) + fac.ListName + SPACE(2) + 'A/R' AS [Description] FROM PatientVisit pv INNER JOIN DoctorFacility comp ON pv.CompanyI
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend