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 have the following structure with remote select permissions; I cannot create temp tables or use stored procs:
tblEvent with event_pk, eventName tblReg with reg_pk, event_fk, person_fk, organization_fk
I'm currently using a case statement to get counts for these categories: case when c.person_fk is Null and c.organization_fk is not null then 'Employer' when c.person_fk is Not Null and c.organization_fk is null then 'Individual' when c.person_fk is not Null and c.organization_fk is not null then 'Both' else 'Unknown' end
But I need some kind of count (0) for every category. I've used a cross-join, group by in the past - but what do you do if you don't have a table? For example, the end result when selecting event_pk=(112,113) would be:
From the above I wanted to create a pivot table, from there I want to pass the column values through to a UDF
XSection (Width, Height, Flange, Leg, LegCount)
I tried the following to get a pivot table but it does not give a single row but 5.
SELECT CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ELSE 0 END AS Width, CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ELSE 0 END AS Height, CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ELSE 0 END AS Flange, CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ELSE 0 END AS Leg, CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ELSE 0 END AS LegCount FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp ON tsp.fkProperty = sp.Property WHERE tsp.fkTemplate = 1
So, this leaves me with one question, even if I was to get this to work, is is possible to then extract the values and pass them through to the UDF within the same stored proc?
Hi all,I have a table in this formatcolname1 colname2 colname3col1data1 col2data1 col3data1col1data2 col2data2 col3data2col1data3 col2data3 col3data3col1data4 col2data4 col3data4I want to display it in this formatcolname1 col1data1 col1data2 col1data3 col1data4colname2 col2data1 col2data2 col2data3 col2data4colname3 col3data1 col3data2 col3data3 col3data4Basically rotate it through 90 degrees clockwise and flip it over :)I'm pretty sure this is done by using a crosstab query and or aderived table or temp table. The problem is I use a crosstab query toget the original data into the first format. I've been strugglingtrying to get the ouptput into the second format for over a day nowand just can't seem to get it to work. Can anyone give me any pointerson the general solution to this?I hope this makes sense. Thanks for the help.
Hi All, The problem is about cross reference. 1. I have a third party cross reference store procedure SimpleXTab CREATE PROCEDURE [dbo].[SimpleXTab2] @XField varChar(50), @XTable varChar(100),@XWhereString varChar(250), @XFunction varChar(10), @XFunctionField varChar(50), @XRow varchar(300),@ResultTable varchar(100) ASDeclare @SqlStr nvarchar(4000)Declare @tempsql nvarchar(4000)Declare @SqlStrCur nvarchar(4000)Declare @col nvarchar(100) set @SqlStrCur = N'Select [' + @XField + '] into ##temptbl_Cursor from [' + @XTable + '] ' + @XWhereString + ' Group By [' + @XField + ']' /* select @sqlstrcur */exec sp_executesql @sqlstrcur
declare xcursor Cursor for Select * from ##temptbl_Cursor open xcursor Fetch next from xcursor into @Col While @@Fetch_Status = 0Begin set @Sqlstr = @Sqlstr + ", " set @tempsql = isnull(@sqlstr,'') + isnull(@XFunction + '( Case When ' + @XField + " = '" +@Col + "' then [" + @XFunctionField + "] Else 0 End) As [" + @Col + "]" ,'') set @Sqlstr = @tempsql Fetch next from xcursor into @Col End /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */ set @tempsql = 'Select ' + @XRow + ', ' + @Sqlstr + 'into ' +@ResultTable+' From ' + @XTable + @XWhereString + ' Group by ' + @XRowprint @tempsql set @Sqlstr = @tempsql Close xcursor Deallocate xcursor set @tempsql = N'Drop Table ##temptbl_Cursor' exec sp_executesql @tempsqlprint @tempsql /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */print @sqlstr exec sp_executesql @Sqlstr if @@rowcount = 0 select 'No Records found'GO 2. I've use this store procedure for many cross reference successfully. But this time my cross reference value (resultcode) is a varchar which cannot be convert to int or decimal in sql, Probably, you've noticed that the fourth parameter is a function. how can i modify SimpleXtab to avoid using math function but still can generate cross reference. exec simplextab2 'Sequence','##tbltempreport',' ','sum','resultcode','Parameter' ,'dbo.resultcodetable'
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";
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?
Hello, I have an SQL problem that is hard to describe so here's an example scenario:
a) company has a personnel database, identifying each person in the company and their assigned role(s).
b) tblPersonnel has a row per person, key is 'PersonnelID' c) tblRoles is a list of all the roles in the company (administrator, clerk, shipper, truck-driver, etc.), key is 'RoleID'
d) tblPersonnelRoles is a cross-reference of all the roles assigned to each person - in simple format of:
PersonnelID | RoleID
Given all the above, I need to have a select statement that produces one row per person, and list all the fields from tblPersonnel AS WELL AS identifies each role assigned to the person.
I'm no SQL guru .. is this even possible?
I know there are alternative solutions (e.g., to write code that for each Personnel does a lookup in Roles) but for one particular situation I need a single SQL statement, or even Stored Procedure.
Thanks in advance, Rick Piovesan Detaya Corp Aurora, Ontario, Canada
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status] ,[final uplift status], [final rank], [final uplift percentage] ,practiceID=row_number() over (partition by [practice unit name] order by Metricname) ,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
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
