Cross-tab Query

Jan 2, 2007

Hi all.

we have a department that has an access database with a bunch of queries in it. They want us to convert it to sql server. One of the queries is a cross-tab query. Is there an easy way to create this in sql? the column headings are the value of column from a table. This could change each month that they run it. How do I make the column heading a variable? I'm guessing a stored procedure would be best. Does anyone have any suggestions?

Thanks so much.
ODaniels

View 6 Replies


ADVERTISEMENT

Cross Tab Query

Sep 7, 2007

 Hi!
I m using sql 2005.I have a table as showing below.










year
Region
loan_amt
pur_id
purpose


1981
Andhra pradesh
$20,000.00
     1
Animal Husbandary

1981
Arunachal Pradesh
$110,000.00
     1
Animal Husbandary

1981
Assam
$240,000.00
     1
Animal Husbandary

1981
Bihar
$75,000.00
     1
Animal Husbandary

1981
Chhattisgarh
$55,500.00
     1
Animal Husbandary

1981
Gujarat
$77,500.00
     1
Animal Husbandary

1982
Goa
$44,888.00
     1
Animal Husbandary

1982
Himachal pradesh
$4,000.00
     1
Animal Husbandary

1982
Himachal pradesh
$20,000.00
     1
Animal Husbandary

1982
Jammu and kashmir
$30,000.00
     1
Animal Husbandary

1882
Jharkhand
$35,000.00
     1
Animal Husbandary

1982
Karnataka
$40,000.00
     1
Animal Husbandary

1982
Kerala
   $20,000.00   
     1
Animal Husbandary

1982
Madhya pradesh
$5,000.00
     1
Animal Husbandary
 
 
I want to produce report as  by using crosstab query as showing bellow
 
Year      Asam     Hyadrabad   goa  arunachal pardesh etc............
1981      1000.00    2000.00       8000.2  00000        000000 ......
1981
'
'
Is it possible by crosstab query ?or please suggest me another way as early as possible.
 
Thanx in advance.
Abhishek
 
 
 
 
 

View 6 Replies View Related

Hi Need Help In Cross Tab Query

Apr 14, 2008

I wanna develope a report using cross  tab query. I have table wit the name spend in which i have spenddate and there is also a amount field which will show the sum of amount field against months, i wanna develop repport in this format
merchant name jan 2007 feb 2007 dec 2007 jan 2008 feb2008
test                   45          50            25          10            11
 
Please can any one help me how to do this this is very urgent ?

View 2 Replies View Related

Cross-Tab Vs SQL Query

Sep 15, 2006

Hi,
I have a report like this....

Date Sam Tom Harry 2006 2005
02/01/2006 4 1 1 6 2
02/02/2006 3 1 1 4 5
02/03/2006 2 2 0 4 4
-------
-------


02/12/2006 1 1 1 3 1
------------------------------------
Total -- -- -- -- --

Now I have all the data required for that in one table.

CREATE TABLE t1(
indate DATETIME
,agname VARCHAR(60)
,Polnumber VARCHAR(20)
)


So I have 2 options :
I have to do in in Cross tab or I have to create a query for this.
Is it possible to built a query like this,I mean assigning data as column?

Any help will be greatly appreciated.
Thanks!!

View 2 Replies View Related

Cross Query

Oct 11, 2007

I would like to make 3x3 table

as following
john | mike | wendy
< 100€ | 3 0 0
100-200€ | 0 2 0
> 200€ | 1 0 1

table:
name (john, mike, wendy)
money (numer)


how should i do it?

i tried with inner join and subquery and a case but it doesn't work.

thank you

View 4 Replies View Related

Cross Tab Query

Jul 20, 2005

Hi - I have what I think is a "simple problem".We currently have a database table that stores ItemProperties byItemID, PropertyID and Value. (The PropertyID references another tablefor property names and types.)This ItemProperties table is indexed and provides a flexible way ofstoring our item metadata. However, I would now like to returnrecordsets to the application layer that list these properties incolumn fashion, grouped by ItemIDI have seen that most cross-tab queries examples assume numerical dataand are based around using SUM and the GROUP BY phrase. But our datais a mixture of string and numbers (of various formats) and so GROUPBY is not an obvious solution. I have tried using CASE in the selectlist but this returns one row for each property with one column havingthe correct value, and all the other colums are NULL. I cannot thinkof how to combine these into one full record!!I could achieve the desired resultset by using a SELECT sub-statementfor every column, but I was hoping there was a more efficient method.Can anyone offer advice on this? It would be most appreciated.Best,Bill

View 1 Replies View Related

Cross Tab Query

Aug 7, 2006

Is there any simple cross tab query template, which can used by general user?

I need it so that I can provide it to our Power users, so that they can use it themselves, instead of coming back to the IT Department.

(something similar like the one found in the Access)

Tomal

View 6 Replies View Related

Cross-database Query From ASP.NET

Feb 7, 2005

How do you write a SQL SELECT statement for a cross-database query in ASP.NET (ADO.NET). I understand the server.database.owner.table structure, but the command runs under a connection. How do I run a query under two connections?

View 1 Replies View Related

Complicated Cross-Tab Query

Feb 15, 2006

I have the following table and data:tblDepartments: (each department can only have a maximum of 3 sections attached to it)Columns: DepartmentName , SectionName Row1: dep1, sec1.0Row2: dep1, sec1.1Row3: dep1, sec1.2Row 4: dep2, sec2.0Row 5: dep3, sec3.0Row 6: dep3, sec3.1I need to derive the following table from tblDepartments :Columns: DepartmentName, SectionName1, SectionName2,  SectionName3Row1: dep1, sec1.0, sec1.1, sec1.2Row2: dep2, sec2.0, '', ''Row3: dep3, sec3.0, sec3.1, ''Any ideas?

View 3 Replies View Related

How To Develop Cross-Tab Like Query

Sep 7, 1999

I have a table which stores the following data:

Col1 Col2
A Apple
A Orange
B Pear
C Banana
C Tomato
C Apple
C Pear

I want to display it in the following manner using a SELECT statement.
Col1 Col2 Col3 Col4 Col5
A Apple Orange
B Pear
C Banana Tomato Apple Pear

Can anyone help me figure out this SQL Statement.

Thanks,
Tim

View 2 Replies View Related

Simple Cross Query

Aug 20, 2004

I am trying run a select and insert in query analyzer on another database across servers. They are both running win 2003 one with SQL2003 Enterprise(the server name of this one is svr-sql-ams6401 and I am running this query while connected to this database in Analyzer) and one with SQL2k Standard(svr-amed-sql). Below is the query with results:

insert into s_msfreq (freqgroup, freqcode, descrip)
select freqgroup, freqcode, descrip
from [svr-amed-sql].ams.dbo.s_msfreq
where freqgroup = 'PRSERC'

I get

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: The stored procedure required to complete this operation could not be found on the server (they were supplied with the ODBC setup disk for the SQL Server driver). Please contact your system administrator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBSchemaRowset::GetRowset returned 0x80004005: ].

I am pretty sure these servers are linked.... but how do I tell and how do I link the two if they are not? Or, is this not the problem?

Thanks guys!!

Spencer

View 5 Replies View Related

Cross Query Between 2 Sources

Apr 21, 2008

Let say, i've 2 server. Server A having Adb database and Server B having Bdb database.

Adb having tblStaff as follow
ID | Department | Name
------------------------
001 | F41 | James

Bdb having tblDepartment as follow
Department ID | Department Name
-----------------------------------
F41 | Emgineering
F44 | Human Resources

Is it possible to create query between them? Con you consult me. Tq.

View 2 Replies View Related

Cross Server Query

Jan 31, 2008

Hi all,

My support team inform me that my servers have been updated and rebooted on monday. Now I need to run some cross db queries today and I dont seem to be able to connect anymore. I am getting the (dreaded apparant but not surprisingly) Msg 17, Level 16, State 1, Line 1
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Now first thing I did was try to drop both linked servers and add again. I am thinking that the particular server has a dynamic ip which I have determined. Ive noticed a work around on MSFT but it say about using a connection string with IP.

Can anyone help me work this out? id appreciate it.

"Impossible is Nothing"

View 7 Replies View Related

Some Sort Of Cross Tab Query In Sql

Jul 23, 2005

I have three tables:tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCodetblBookRange has the fields bookRangeID, bookRangeDescriptiontblBookSubject has the fields bookSubjectID, bookSubjectDescriptionso some typical data in tblBook might be:1, 1, 1, B1HBSCI2, 1, 2, B2HBFRE13, 1, 3, B3HBGER4, 2, 1, B4PBSCI5, 2, 2, B5PBFRE6, 2, 3, B6PBGER7, 3, 1, B7CDSCI8, 3, 2, B8CDFRE9, 3, 3, B9CDGER110, 3, 3, B10CDGER211, 1, 2, B11HBFRE2tblBookRange would be:1, HardBack2, PaperBack3, CD RomtblBookSubject would be:1, Science2, French3, GermanI'd like to create a query which will return me the subjects along thetop, the book range down the side, and the bookcodes in the cells, abit like this:BookRange , Science, French, GermanHardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGERPaperBack , B4PBSCI, B5PBFRE, B6PBGERCD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2Does that make any sense? So basically I'd like to get some kind ofdynamic SQL working which will do this kind of thing. I don't want tohard code the subjects in or the book ranges. I get the feeling thatdynamic SQL is the way forward with this and possibly using a cursor ortwo too, but it got quite nasty and convoluted when I tried variousattempts to get it working. (one of the ways I tried included workingout each result in a dynamic script, but it ran out of characters asthere were too many "subjects".)If anyone has any nice but quite dynamic solutions, I'd be delighted tohear.(and I know some of you have already told me you don't like tablesbeginnig with tbl, but I'm not hear for a lecture on namingconventions, I'm hear to learn and share ideas :o) )

View 2 Replies View Related

Cross Tab Query In SqlServer

Nov 26, 2006

Hi,I have three tables with there fields given in brackets:User: [userId as int] (PK), [userName as navarchar]Divisions: [divisionId as int] (PK), [divisionName as nvarchar]DivisionsOfUsers: [userId as int],[divisionId as int]the "DivisionsOfUsers" tables has many-to-many relationships betweenuserid and divisionId.I would like to generate a result something like this:Division1 Division2 Division3User1 1 0 0User2 0 0 1User3 1 1 0User4 0 0 0and so on....where "1" indicates that the given User-Division combination exists and"0" denotes that it doesnt in the "DivisionOfUsers" table.I have tried all sorts of joins to get this data. But was unable to dothis.I have been told that this is possible by a cross-tab query. I dontknow how to generate this query.Can anybody give me a solution for this to be used in Sqlserver 2000 aswell as Sqlserver 2005.Thanks in advance

View 1 Replies View Related

Sub-Query / Cross Join / Or Something Else?

Mar 21, 2007

I have the following 2 tables:(BATCHES)BatchID [int] KEYID [int]OrderID [int]Action1DateTime [datetime]Action2DateTime [datetime]Action3DateTime [datetime]Action4DateTime [datetime]Action5DateTime [datetime]Action6DateTime [datetime]Action7DateTime [datetime]Action8DateTime [datetime](ORDERS)OrderID [int] KEYProductionLineID [int]RecipeID [int]OrderAmount [int]Batches.Action1DateTime to Batches.Action8DateTime can have several entrieseach day.I need a query to count all Batches.Action1DateTime to allBatches.Action8DateTime for each day in a specified period.I also need to specifically use where clauses for Orders.OrderID and/orOrders.RecipeID.I need the data to draw a graph for each ActionXDateTime as a function ofdate.Any help appreciated./Henrik

View 3 Replies View Related

Cross-tab Query In SQL Server

Oct 14, 2006

I have a table with results from several inspection tests. The Pass/Fail parameter in the cross-tab output would be Pass if all tests for that serial number are Pass, and Fail if any of the results for that serial number are Fail.

How would I create a cross-tab query in SQL Server?

Table for input to cross-tab query: [Inspection Data]
SerialNumber ParamName Result Pass/Fail

001 Test1 3.43 Pass

001 Test2 3.27 Pass

001 Test3 2.97 Pass

002 Test1 2.88 Pass

002 Test2 3.01 Pass

002 Test3 4.22 Fail

003 Test1 3.11 Pass

003 Test2 2.91 Pass

003 Test3 3.28 Pass

Cross-tab Query Output:

Test1 Test2 Test3 Pass/Fail
001 3.43 3.27 2.97 Pass
002 2.88 3.01 4.22 Fail
003 3.11 2.91 3.28 Pass
Thanks,
Sam

View 4 Replies View Related

Cross Database Query

Feb 18, 2008

I have Two databases e.g. PDC,STB(has a table named "user" and fields of the table is "name","time_of_login").i want to create a stored procedure in PDC database which will insert data into STB's "user" table.

OR

how to run query from QueryAnalyzer by setting database from combobox at top as PDC and run select * from STB.user;

AS

in oracle we can connect via one user
and can select the tables of another user.
like
connect internal/oracle
select * from scott.emp;

View 4 Replies View Related

Cross-Join Query

May 21, 2008





Hi folks, this is my first time here... i hope that you can help me with this query

I have two query and one table

Queries
Q1-Training - List of training
Q2-Group - Groups that must take the training
Table
T1 - Training_Group

Here a query example using SELECT * FROM

Q1
Code Name
-----------------------------------
AD0041 Advance SQL
BE0012 Beginner SQL
NE0001 Newbie SQL
...

Q2
Group_Name Group_Code
-----------------------------------
Administration ADMIN
Directors 456
John Smith 555666
...

T1
Code Level Group_Code
-----------------------------------
AD0041 1 ADMIN
AD0041 2 456
AD0041 3 555666
BE0012 2 456
NE0001 1 ADMIN

Here's my problem :

I need to make a report that look like that

(as training) (Group_Code) --> dynamic column
^^^^^^ ^^^^^^
Training ADMIN 456 555666
-------------------------------------
AD0041 1 1 1
BE0012 0 1 0
NE0001 1 0 0

I did it in ACCESS with TRANSFORM and PIVOT.

But how i do that in SQL Server 8.0 ?^

Thanks in advance for your solution or hints... i'll be back tomorrow morning

Best regards
David

View 5 Replies View Related

Urgent Help With Query (Cross Join)

Sep 28, 2006

I have the following tables:members--------------member_idmember_tpc_id ( = tpc.tpc_id)tpc------tpc_idcourse------------course_idtpc_assignment---------------------------tpc_assignment_idcourse_idenrollment-------------------member_idcourse_idenrollment_status Now I want to select all members where member_tpc_id>0 and get the enrollment_status of each member in each course where course_id IN (Select course_id From tpc_assignment)Now what i did was get all the members and then all the courses and did a cross join between them. There are about 1900 members and 80 courses and when I do a cross join I get 1900*80 rows (152000) and the status of each member for all the 80 courses. If not enrolled it returns Not Enrolled (i have a UDF which takes a member_id and a course_id and returns the status). The BIG problem is that its taking about 6-8 mins to run the query and as a result its timing out on the aspx page. Can someone please tell me how I can do what i am trying to do without using the cross join because I suspect its the culprit here. The query I came up with is  Select
*, dbo.returnStatus(temp1.user_id, temp2.course_id) As Status,
(Select tpc_title From tpc Where tpc_id = temp1.member_tpc_id) As Tpc_Title
From
(Select member_id As user_id, member_name, member_tpc_id
From members Where member_tpc_id> 0 And organization_id = '1'
)temp1
cross join
(Select course_id As course_id, course_title As course_title
From course Where course_id IN
(Select course_id From tpc_assignment Where tpc_requirement_id IN
(Select tpc_requirement_id From tpc_requirement) And course_id<>0 And organization_id = '1')
)temp2
Order By member_name, Tpc_TitlePlease help. Thank you. 

View 1 Replies View Related

CREATE Cross Tab Query Using Sql Server 6.5

Feb 1, 1999

hi, here I create a cross tab query in access 97 this is the code

TRANSFORM Count([1].ORD_NBR) AS [The Value]
SELECT [1].SRMGR_NAME, Count([1].ORD_NBR) AS [Total Of ORD_NBR]
FROM 1
GROUP BY [1].SRMGR_NAME
PIVOT [1].ASR_SUPP;

I export this table to sqlerver 6.5 and run the same code. IT DIDNOT WORK
My question is how can I implement crosstab query in sql server 6.5?

any one can help I really do appreciate

Ali

View 2 Replies View Related

Cross-server SELECT Query

May 2, 2006

Hi,

Recently the powers-that-be migrated the largest databases from one server to another, more powerful server while keeping some support data on the original server. My problem: I need to run queries on tables spanning both database servers. Unfortunately, I can't find any documentation on how to do this. Does anyone have any ideas?

Thanks!

View 1 Replies View Related

Cross Table Multiplication Query

Mar 30, 2012

I am currently building a website to deal with different product information and sales with php. I am using SQL to sort the database and pull out information.

The final thing i need to do is work out the total revenue of each product however the problem i am having is that the 'Price' column and 'SalesVolume' column are in two different tables and they need to be multiplied together.

The two tables and column headings are as follows:

Product
ID
Name
Price

MonthlySales
ID
ProductCode
Month
Year
SalesVolume

(ID and ProductCode are linked together in a relationship)

I cannot see anything wrong with the syntax in my query however i believe there is.

Here is the query I am using:

Code:
"SELECT SUM(Products.Price * SUM(MonthlySales.SalesVolume)) as revenue FROM Products
INNER JOIN MonthlySales ON(Products.ProductCode = MonthlySales.id) GROUP BY Products.ProductCode";

View 8 Replies View Related

Cross Join/? Query In A View Help

Feb 4, 2004

Hey All...
Got a View question.
Have 2 tables:
#1 Currencies
|CCY_Name|CCY_Code|

#2 Rates
|CCY1|CCY2|CCY3|...etc|Active|
-> where the Columns CCY# = the Records in #1

How do I build a View to Select the ONE record in #2 where Active=Y, having the CCY_Name from #1 based on #2.CCY1 (Column NAME) = #1.CCY_Code (Record).

Thanks

robbied111

View 2 Replies View Related

Dynamic Cross-Tab Query Too Long?

Jul 20, 2005

I am using the Dynamic Cross-Tab code supplied in an article from SQLServer Magazine (http://www.winnetmag.com/SQLServer/...608/15608.html).I modified the script to generate a temp table inside the storedprocedure, and then use this temp table as the source for thecross-tab. However, the problem seems to be that the dynamic SQLstring generated by the script is longer than what can be stored inthe @SQL variable. The Cross-tab works great, so long as the amount ofdata to be pivoted is small.Is there any way around this? E.g. a User defined type, or anotherdata type which can store more characters?Thanks,TimCREATE procedure CBN_CrossTab@StudyID varchar(100), --Model ID passed from web app - Only one modelcan be selected@Level int --The level to which the taxonomy should be rolled upAsDECLARE@Table as sysname, --Table to crosstab@OnRows as nvarchar(128), --Groupuing key values (on rows)@OnRowsAlias as sysname, --Alias for grouping cloumn@OnCols as nvarchar(128), --destination columns (on columns)@SumCol as sysname, --data cels@SQL AS varchar(8000), -- String to hold generated SQL String@NEWLINE as char(1) --Holds the New Line Character for the codeSET @OnRowsAlias = NullSET @SumCol = NullSET @NEWLINE = CHAR(10)-- Generate the Temp table for the taxa and countsCREATE TABLE #RefOrganisms (sampleid int, txtTaxa varchar(75),fltCount float)INSERT INTO #RefOrganisms(sampleid, txtTaxa, fltCount)SELECT dbo.tblsampledata.sampleid,dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @Level, " ") AS Taxa,SUM(dbo.tblbenthic.[count] /dbo.tblsitedetail.numberofreps) AS SumCountFROM dbo.tblstudylist INNER JOINdbo.tblsite ON dbo.tblstudylist.studyid =dbo.tblsite.study_id INNER JOINdbo.tblsitedetail ON dbo.tblsite.siteid =dbo.tblsitedetail.site_id INNER JOINdbo.tblsampledata ONdbo.tblsitedetail.sitedetailsid = dbo.tblsampledata.sitedetails_idINNER JOINdbo.tblbenthic ON dbo.tblsampledata.sampleid =dbo.tblbenthic.sample_id INNER JOINdbo.iter_intlist_to_table(@StudyID) i ONdbo.tblstudylist.studyid = i.number INNER JOINdbo.tblbenthictaxa ON dbo.tblbenthic.organism_tsn =dbo.tblbenthictaxa.tsnWHERE (dbo.tblsampledata.qaqc = 0) AND (dbo.tblsampledata.status =2) AND (dbo.tblbenthictaxa.rank_id >= @Level)GROUP BYdbo.tblsampledata.sampleid,dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @Level, " ")-- Identify the Temp table info for the CrossTabSELECT @Table = '#RefOrganisms'SELECT @OnRows = 'sampleid'SELECT @OnCols = 'txtTaxa'SELECT @OnRowsAlias = NullSELECT @SumCol = 'fltCount'--STEP1 BEGININNING OF SQL STRINGSET @sql = 'SELECT'+ @newline +' '+ @onrows +CASEWHEN @ONROWSALIAS IS NOT NULL THEN ' AS ' + @ONROWSALIASELSE ''ENDCREATE TABLE #KEYS(KEYVALUE NVARCHAR(100)NOT NULL PRIMARY KEY)DECLARE @KEYSSQL AS VARCHAR (1000)SET @KEYSSQL = 'INSERT INTO #KEYS ' + 'SELECT DISTINCT CAST(' +@ONCOLS + 'AS NVARCHAR(100)) ' + 'FROM ' + @TABLEEXEC (@KEYSSQL)DECLARE @KEY AS NVARCHAR(100)SELECT @KEY = MIN(KEYVALUE) FROM #KEYSWHILE @KEY IS NOT NULLBEGINSET @SQL = @SQL + ' ,'+ @NEWLINE +' SUM(CASE CAST(' + @ONCOLS +' AS NVARCHAR(100))' + @NEWLINE +' WHEN N''' + @KEY +''' THEN '+ CASEWHEN @SUMCOL IS NULL THEN '1'ELSE @SUMCOLEND + @NEWLINE +' ELSE 0' + @NEWLINE +' END) AS [' + @KEY + ']'SELECT @KEY = MIN(KEYVALUE) FROM #KEYSWHERE KEYVALUE > @KEYENDSET @SQL = @SQL + @NEWLINE +'FROM ' + @TABLE + @NEWLINE +'GROUP BY ' + @ONROWS + @NEWLINE +'ORDER BY ' + @ONROWSPRINT @SQL --+ @NEWLINE --FOR DEBUGEXEC (@SQL)GO

View 1 Replies View Related

SQL Express And Cross Table Query

Jul 25, 2006

Is it possible to create cross table query via SQL Express 2005 since it's possible to do with MS Access?

Sample query in MS Access:

TRANSFORM Count([tip]) AS [The Value]
SELECT [sifra], [naziv]
FROM naselja
GROUP BY [sifra], [naziv]
PIVOT [opstina]
Is there any soultion how to make same or at least identical SQL query expression which will behive like MS Access ones?

Thanks!

View 1 Replies View Related

Translating Access Cross-Tab Query Into T-SQL?

Mar 23, 2006

Hello,

I am wondering if someone could give some advice about how I could translate the following query in MS Access into T-SQL.  Any help is greatly appraciated.

Thanks

------------------------------------------------------------------------------------

TRANSFORM Avg(A.Response) AS AvgOfResponse

SELECT A.QuestText, A.SubQuestText

FROM TableName A

WHERE A.Response Is Not Null

GROUP BY A.QuestText, A.SubQuestText

ORDER BY A.QuestText, A.SubQuestText

PIVOT A.[Ethnicity Group]

-------------------------------------------------------------------------------------------------------------------------------

View 1 Replies View Related

Problem With Cross Apply Query

Nov 10, 2006

Hey guys. This is one of the queries pasted from BOL. I'm having problems excuting this query. The problem lies in the CROSS APPLY part. When I copy this query and run it in SSMS, it gives me an error saying 'Incorrect syntax near .' It doesn't like the qs.sql_handle part. If I remove that and pass the actual handle in for some query, it works. Can someone please tell me what I'm doing wrong?????? Also, I've sp1 installed on my SQL Server 2005 Enterprise, just in case if this matters. Below is the query pasted which is giving me problems. Thank you.

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],

SUBSTRING(st.text, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY total_worker_time/execution_count DESC;

View 3 Replies View Related

Cross Database Query Performance

Feb 1, 2008

What is the relative performance cost of peforming joins, inserts across databases as opposed to performing them on tables within the same physical database? I can't find much on this at all..

Thanks

View 12 Replies View Related

Cross Database Query Performance

Feb 17, 2008

Hello All,

I have an issue where my SQL query is taking an exceedingly long time to execute. It joins tables in 2 different databases. When I import the tables from both my databases into a single database and execute the query, it executes instantly!

I had a look at the execution plans of the same query when executed within a single database and the query when executed with tables across databases and there were differences in the execution plan. The single database query had parallelism whereas the query for cross database joins didnt have any parallelism.

Both the databases are in the same server.

I havent seen a case where cross database joins impact query performance so much. Any pointers?

Thanks
Arun

View 4 Replies View Related

Help With (Pivot/Cross-Join???) Query To Select A Result Set

Jan 20, 2005

I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.

Current structure is:

Colour Size Quantity
-----------------------
Red 10 100
Red 12 200
Red 14 300
Blue 10 400
Blue 12 500
Blue 14 600
Green 10 700
Green 12 800
Green 14 900
Green 16 1000

I want to produce this result set:

Colour Size10 Size12 Size14 Size16
-------------------------------------
Red 100 200 300 0
Blue 400 500 600 0
Green 700 800 900 1000

There could be any number of sizes or colours.

Is this possible? Can anyone give me any pointers?

Thanks in advance

greg

View 8 Replies View Related

SQL Server 2014 :: Writing A Cross Join Query With One Table?

Jul 19, 2015

writing a cross join query with one table:

Cities(City_name, X_coordinate, Y_coordinate)

the result should be all combinations without reverse column returns

SELECT * FROM [dbo].[Cities] as P1
Cross JOIN [dbo].[cities] as p2
where (p1.City_name != p2.City_name) and ???

for example if there are three Cities as A,B,C the result should be: A->B, A->C, B->C (without the returns B->A, C->A, C->B)

View 8 Replies View Related

SQL Server 2014 :: Column Store Query Reverting To Row Mode With CROSS JOIN

May 20, 2015

I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.

-- The purpose of this script is to demonstrate that
-- two queries against a columnstore index that each execute in batch mode
-- will continue to execute in batch mode when inner joined.
-- However, one of the queries will execute in row mode when cross-joined.

-- Create function to return 0 to n rows
IF OBJECT_ID('dbo.IntCount') IS NOT NULL
DROP FUNCTION dbo.IntCount;

[Code] .....

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved