Hi, I'm just starting to learn SQL, just on the querying side and have what I think is quite a tricky function. It may be simple for you guys.
I want to make a report based on three tables. Enrolment, Course and Student. Its pretty easy until I need to crosstab it.
It's more complex than this (more fields but this gives the gist) Basically I want AcademicYearId, StudentID, coursecode, completionstatusid which would give the following.
I have managed to group it by academicyearid, coursecode and completionstatusid and done the counts ok but I want to twist the completionstatusid so each type of status has its own column like this
thought my question was misinterpreted.actual table can be smaller or larger. but,within a procedure need to turn a created #temptablesimilar to(examid is in asc order):(id, p/f, examid, examname)1 f xxxx xxxxx1 f xxxx xxxxx1 f xxxx xxxxx2 p3 p4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx(note, id = 4 has 7 failed exams).to a table with up to five columns of failedexamnames:1 f xxxxx xxxxx xxxxx2 p2 p4 f xxxxx xxxxx xxxxx xxxxx xxxxxcan get rid of exameid if helps.--Sent by ricksql from yahoo subpart from comThis is a spam protected message. Please answer with reference header.Posted via http://www.usenet-replayer.com/cgi/content/new
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.
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 ?
I'm trying to run the cross tab query so that it returns only one row. If there is data I want to dump in the field value (fText), otherwise I put in a dash. The problem I'm having is that it's returning multiple rows when I want a single one. I could create a # table and dump the data in, but it seems a bit long winded.
Many thanks.
SELECT distinct (CASE WHEN f.TypeID=1 THEN 1 ELSE 0 END) f1, (CASE WHEN f.TypeID=2 THEN f.fText else '-' END) f2, (CASE WHEN f.TypeID=3 THEN f.fText else '-' END) f3, (CASE WHEN f.TypeID=4 THEN f.fText else '-' END) f4, (CASE WHEN f.TypeID=5 THEN f.fText else '-' END) f5, (CASE WHEN f.TypeID=6 THEN f.fText else '-' END) f6, (CASE WHEN f.TypeID=7 THEN f.fText else '-' END) f7, (CASE WHEN f.TypeID=8 THEN f.fText else '-' END) f8, (CASE WHEN f.TypeID=9 THEN f.fText else '-' END) f9, (CASE WHEN f.TypeID=10 THEN f.fText else '-' END) f10 FROM tblTest f WHERE (f.fID=219)
" I have two sql server2000 database named db1 and db2. i have a user named 'user1' who has permission in both database.I have used a 'Select * from tableOne'in db1 when i have this table 'tableOne'in db1. now this table was droped and created in db2.
what i need is i should log in to db1 and access the same select statement which is there in application used by my clients.
i have created a view in db1 with the same name as
'create view tableOne as select * from db2..tableOne'
now i can access. Is there some othere way with out creating view?
Is is possible to write a query that does the reverse of a cross tab query, I have a table that is mirrored for our Main frame, the table contains a item number a warehouse number and a date field and then 43 fields like field01 thru field43 which store quantities, each field out from 01 is another date our from the date field and I need to look for the item, warehouse and return a date where one of the many field is greater than or equal to the quantity I am looking for, I obviously will have to calculate the date field but thought If I could do the reverse of a cross tab giving a resuld set of 43 records for everyone in the first file I could do a quick update for item number, warehouse match and quantity field greater than what i need returning the date
blah blah blah , sorry I made it sound so confusing
I need to do this so it would be the correct format for a report. Can anyone suggest how to do this? I need to create a view with this format, so speed is probably an issue?
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?
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?
insert into @log(date, category, value1, value2) select '2008-01-01', 'ABC', 11,12 union all select '2008-01-02', 'ABC', 35,53 union all select '2008-01-03', 'ABC', 38,62 union all select '2008-01-05', 'ABC', 59,95 union all select '2008-01-02', 'XYZ', 42,21 union all select '2008-01-04', 'XYZ', 9,7 union all select '2008-01-05', 'XYZ', 89,45 union all select '2008-01-01', 'HHH', 70,52 union all select '2008-01-03', 'HHH', 3,83 union all select '2008-01-05', 'HHH', 26,77
where 1) date is always up to the day (no time variation) 2) date and category can be considered a composite unique key
Given a date range (let's say, from 2008-01-01 to 2008-01-05) I need to get the below:
Ideally, the results include - every day in the date range (even if there is no corresponding data for that date) - the columns values to be dependent on the categories found within the date range
I came up with this
-- to fulfill requirement "every day in the date range" declare @dt table (d smalldatetime) insert into @dt select '2008-01-01' union all select '2008-01-02' union all select '2008-01-03' union all select '2008-01-04' union all select '2008-01-05'
-- to fulfill display all the categories (manually determined) select convert(varchar(10),d,101) as date, abc.value1 as abc_value1, abc.value2 as abc_value2, xyz.value1 as xyz_value1, xyz.value2 as xyz_value2, hhh.value1 as hhh_value1, hhh.value2 as hhh_value2 from @dt dt left join @log abc on dt.d = abc.date and abc.category = 'ABC' left join @log xyz on dt.d = xyz.date and xyz.category = 'XYZ' left join @log hhh on dt.d = hhh.date and hhh.category = 'HHH'
just for the purpose of generating the end result example, but in a real life situation, both the date range and the categories that may fall within that date range... are dynamic. To make my head spin even more, I also suspect the issue of value2 AND value3 being pulled is making this one complicated statement.
in front end application,iam using pivot table,there is no option to create the growth rate calcuation/formula in FE.
my table data consists like below: country_name Revenue PERIOD_TYPE_OUT Amount_out UK Solutions01 Apr 2007 To 31 Mar 200856.83000000 UK Solutions01 Apr 2006 To 31 Mar 2007116.07000000
while iam using the cross tab in front end application data view is coming as country_name
01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008 solution solution uk 116.07 56.83
Actual o/p should be:
01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008 Growth solution solution uk 116.07 56.83 -0.51
can anybody tell how to calcualate growth in stored procedure level formala for calculating growth rate is : (56.83-116.07)/116.07 ([current year]-[previous year])/[previous year]
I am using SQL server 2005 and want to write a cross-tab query. i have managed a basic one but can't seem to write a more complex one. if i have a table with columns called id, person_id, field_description, and content. e.g. image that the two values i could have in field description are age and favourite_colour, and then the corrosponding content for these fields would be 56yrs and blue or something.
now i want column names as person_id, age and favourite_colour. and the actual data should be content.
Have i made sense? I am sure this must be simple, but can't figure the syntax
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
I have a CROSS table in ACCESS that I want to export to SQL 2005. The query is: TRANSFORM Count(GD_PROFILE.cnRow) AS CountOfcnRow SELECT GD_PROFILE.ID FROM GD_PROFILE GROUP BY GD_PROFILE.ID PIVOT GD_PROFILE.ID_APLIC;
The RESULT is:
ID
20
22
77
122
141
179
211
327
334
370
388
405
524
584
645
687
826
865
914
CN1
1
1
1
1
1
CN2
1
1
1
1
CN3
1
1
1
1
CN4
1
1
1
1
1
CN5
1
1
1
1
CN8
1
1
1
1
1
1
1
1
1
1
1
CN14
1
1
1
1
1
1
1
1
CN15
1
1
1
1
1
1
1
1
1
1
CN22
1
1
1
1
1
1
1
1
CN29
1
1
1
1
1
1
1
CN36
1
1
1
I want to create a query in SQL Server that give me rows from the cross query that are equal. For example, the row 1 and 4 are equal. Has the same values in each row. How can I make it programatically?
The Cross is a simple cross query, and the goal is return this final result:
Profile 1 - ID CN1, ID CN4 Profile 2 - ID CN2, ID CN3, ID CN5 Profile 3 - ID CN8 Profile 4 - ID CN14 Profile 5 - ID CN15 Profile 6 - ID CN22 Profile 7 - ID CN29 Profile 8 - ID CN36
select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/11/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/12/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/13/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/14/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/15/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/16/2006' group by task_date
My out put is Date Program Design Upload Testing Meeting Others 2006-09-11 00:00:00.000 42 0 0 8 2 1 2006-09-12 00:00:00.000 77 0 0 4 0 0 2006-09-13 00:00:00.000 56 0 0 8 0 1 2006-09-14 00:00:00.000 63 0 0 6 0 1 2006-09-15 00:00:00.000 63 0 0 6 0 1
Now i want in below format 2006-09-11 2006-09-11 etc
I would like to be able to enter a date range and allow sql to format the result set as follows:
The two dates I would pass in are 7/1/2007 (start) to 12/31/2007 (end date)
I have three fields called: Amount ($10.50) Date (7/11/2007) Key ( A, B, C, etc )
Table looks like this: Key Amount Date A 10.50 7/11/2007 C 1.00 8/1/2007 D 2.00 12/20/2007 B 3.45 8/5/2007
The result should look like this: Key Jul07 Aug Sep Oct Nov Dec07 Total A 10 40 0 0 5 10 65 B 30 10 10 5 10 10 75 Etc.
If I changed the date range to 1/1/2007 (start) to 12/31/2007 the result should look like this: Key Jan07 Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec07 Total A 4 10 42 0 0 3 10 40 0 0 5 10 124 B 1 6 10 0 2 0 30 10 10 5 10 10 94 Etc.
Hi, I'm doing a web application that will get some information from an ERP. At this moment I have 2 databases: 1) The aspnetdb, where I have the tables for Merbership and Role 2) The ERP database I need to put my web application tables on one of these two DB's. This tables will reference the users from the membership and some products from the ERP DB. I will store products requests that will store both UserID (from aspnetdb) and ProductID (from ERP DB). I'm thinking to put these tables on the aspnetdb, so that all web application tables stick together. But, I will loose tha ability to make joins with the ERP database, right? Do you think this will work? Can someone make some comments about this situation, and give me some tips? Thank you!
I am interested in adding a new row to a table 'Table05' that exists in a SQL Server 2005 database whenever a table 'Table00' in another SQL Server 2000 database has a row added to it. Can someone tell me a way to implement the above solution?
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?
HiI have a table with rows of timestamped values for given variables. Each of the variables are periodically inserted at the same time. ieDateTime Variable Value.T1 V1 xxxxT1 V2 xxxxT1 V3 xxxxT2 V1 yyyyT2 V2 yyyyT2 V3 yyyyIn a SQLserver stored procedure I would like to insert this data into a temp table like the following. I don't need summaries just need a table or query where I can select on a variables value. ie "select * from temptable where V3 = yyyy"Datetime V1 V2 V3T1 xxxx xxxx xxxxT2 yyyy yyyy yyyy any suggestions for an SQL statement.thanks in advance.
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?
When I check out > 2000 customer databases cross 8 SQL 2000 servers, I wish I could use "Select * from " + @server + "." + @databaseName + ".information_Schema.coulmns where column_name = " + @column_name.
It did not works in this way. However, why it works on "Select * from serverA.master.information_schema.columns"? It is also a cross server query but is on Master DB.
