Is there a way to write Crosstab query in SQL 7.0.
I have a Table which has partner, usernames and the city they are from, apart from other fields. I have another table which tracks the user activity on a day by number of visits to the web site. What i want is like this :-
Date Total Visits Atlanta Connecticut .... .... ....
I.E. I wan't the city names to appear as columns. The main catch here is that this data is for a given partner. So the city names could be different for different users from different partners. i.e. city names have to be generated on the fly by looking at the partner table and then by getting all its users and seeing what all are the cities involved.
Currently working on a Attendance System project . Iam storing the data in table against the employee code, date and status (basically a rowwise data).
Would require a query which can generate a cross-tab display with Employee Codes (on X axis), Dates (on Y-Axis) and with the Attendance Status.
The sample data for the same is as follows:
EmpCode Att_Date Att_Status ------- -------- ---------- 001 01/01/2007 P 001 01/02/2007 A .. ... .. 001 01/31/2007 P
002 01/01/2007 P . . . . . . . . .
Would require the output as..
EmpCode 01/01/2007 01/02/2007 ...... 01/31/2007 ------- ---------- ---------- ---------- 001 P A P 002 A P P . . . . . . . . . . . .
Can someone show me how to write a query to convert this:Year, Account, Qtr1, Qtr2, Qtr3, Qtr42004, 12345, 100, 200, 300, 4002005, 23456, 200, 300, 400, 500to this:Year, Account, Quarters, Amount2004, 12345, Qtr1, 1002004, 12345, Qtr2, 2002004, 12345, Qtr3, 3002004, 12345, Qtr4, 4002005, 23456, Qtr1, 2002005, 23456, Qtr2, 3002005, 23456, Qtr3, 4002005, 23456, Qtr4, 500So far I've only found examples which convert values to columns but notfor columns to values. TIA... AL
I have two tables Bill and Location.Bill(location_id int,prod_period datetime,consumption float,demand float)Location(location_id int,location_name varchar(45))I want to create a stored procedure that takes a parameter of @year. Ibasically want the procedure to return results that show locationswhere consumption and/or demand IS NULL or 0 for each month throughouta given year. I would like my results to look something like this:location_id year Jan Feb Mar Apr May Jun Jul Aug Sept OctNov Dec1 2005 0 0 02 2005 0 0 0 03 2005 0 0If data does exist for consumption or demand, I would like to show it,however I am really interested in the locations that have no dataassociated with them.Any ideas of how I can accomplish this?
I cant figure out what is going wrong here. I created this query to be able to show values across periods , and put it inside of a table instead of a matrix, for easier totals and subtotals. but for some reason my subquery is not pullin up the right amounts for "Goal Amount". The fields i have labeled as Period_1 , period_2 and so on work just fine. But the goals are not coming up with the same number as in the Sub query, if i ran it by its self. I dont know if maybe its the join, or what. but for some reason the numbers are way high then they should be. am i doing something wrong. Please let me know if i need to give more information.
This is what the output looks like now: 0.0000 0.0000 0.0000 4516182.0000 5569957.8000 2559169.8000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 3915126.9500 3275922.5500 1598011.0000 4526861.2500 6880829.1000 3078265.6500 629010.4000 589697.2500 353818.3500 2218175.9000 1984683.7000 1517699.3000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000
this is what it looks like in the subquery: 150539.4000 2008 1 80 12 150539.4000 2008 2 80 12 150539.4000 2008 3 80 12 39313.1500 2008 1 81 12 39313.1500 2008 2 81 12 39313.1500 2008 3 81 12 181074.4500 2008 1 82 12 181074.4500 2008 2 82 12 181074.4500 2008 3 82 12 116746.1000 2008 1 86 12 116746.1000 2008 2 86 12 116746.1000 2008 3 86 12 79900.5500 2008 1 87 12 79900.5500 2008 2 87 12 79900.5500 2008 3 87 12 the two numbers highlighted should have the same amounts.
Code Snippet
SELECT cusSales.Report_Level_Id AS Report_Level, cusSales.Customer_Sales_Summary_Year AS Year, Goals.Region_Key, Qry_Report_Level_Brand.Report_Level_Description, RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Name, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 1 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_1, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 2 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_2, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 3 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_3, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 4 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_4, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 5 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_5, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 6 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_6, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 7 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_7, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 8 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_8, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 9 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_9, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 10 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_10, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 11 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_11, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 12 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_12, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 13 THEN cusSales.Customer_Sales_Summary_Amount ELSE 0 END) AS Period_13, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 1 THEN Goal_Amount ELSE 0 END) AS GOAL_1, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 2 THEN Goal_Amount ELSE 0 END) AS GOAL_2, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 3 THEN Goal_Amount ELSE 0 END) AS GOAL_3, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 4 THEN Goal_Amount ELSE 0 END) AS GOAL_4, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 5 THEN Goal_Amount ELSE 0 END) AS GOAL_5, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 6 THEN Goal_Amount ELSE 0 END) AS GOAL_6, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 7 THEN Goal_Amount ELSE 0 END) AS GOAL_7, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 8 THEN Goal_Amount ELSE 0 END) AS GOAL_8, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 9 THEN Goal_Amount ELSE 0 END) AS GOAL_9, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 10 THEN Goal_Amount ELSE 0 END) AS GOAL_10, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 11 THEN Goal_Amount ELSE 0 END) AS GOAL_11, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 12 THEN Goal_Amount ELSE 0 END) AS GOAL_12, SUM(CASE cusSales.Customer_Sales_Summary_Period WHEN 13 THEN Goal_Amount ELSE 0 END) AS GOAL_13 FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Sales_Summary_Fiscal AS cusSales INNER JOIN Qry_Report_Level_Brand ON cusSales.Sub_Brand_Id = Qry_Report_Level_Brand.Sub_Brand_Id ON RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer.Customer_Code = cusSales.Customer_Code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension ON RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer.Territory_Code = RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Territory_Code FULL OUTER JOIN (SELECT SUM(cusSales.Customer_Sales_Summary_Amount) AS Goal_Amount, cusSales.Customer_Sales_Summary_Year AS Year, cusSales.Customer_Sales_Summary_Period AS Period, cusSales.Report_Level_Id, RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region.Region_Key FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Sales_Summary_Fiscal AS cusSales INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Customer AS Tbl_Territory_In_Customer_1 ON cusSales.Customer_Code = Tbl_Territory_In_Customer_1.Customer_Code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region ON Tbl_Territory_In_Customer_1.Territory_Code = RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region.Territory_Code WHERE (cusSales.Customer_Sales_Summary_Year = 2008) AND (cusSales.Consolidated_Sales_Tables_Id = 8) and region_key=12 GROUP BY cusSales.Customer_Sales_Summary_Year, cusSales.Customer_Sales_Summary_Period, cusSales.Report_Level_Id, RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region.Region_Key) AS Goals ON RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Key = Goals.Region_Key AND cusSales.Report_Level_Id = Goals.Report_Level_Id AND cusSales.Customer_Sales_Summary_Year = Goals.Year AND cusSales.Customer_Sales_Summary_Period = Goals.Period WHERE (cusSales.Customer_Sales_Summary_Year IN (2007, 2008)) AND (cusSales.Consolidated_Sales_Tables_Id = 6) AND (NOT (cusSales.Bill_Customer_Code IN ('11825', '11990', '11971'))) AND (cusSales.Report_Level_Id IN (78, 80, 81, 82, 86, 87)) AND (RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'BREEDER') AND (RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'OTHER') AND (RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'Vet - Corporate') AND (RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'export') AND (RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Group_Name <> 'Vet - Other') AND (RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Key = 12) GROUP BY cusSales.Report_Level_Id, cusSales.Customer_Sales_Summary_Year, Goals.Region_Key, Qry_Report_Level_Brand.Report_Level_Description, RC_DWDB_INSTANCE_1.dbo.Qry_Sales_Group_Dimension.Region_Name
I am trıed to do crosstab in sql server 2005 but I failed. I look all document on the net but they are all static means the column that are crosed are avaliable. but ın my query I do not know the count of the columns... Please help me...
This the my query... Yağmur Duman Türk Masajı 11 Mehtap . Türk Masajı 10 Mehtap . Japon Masajı 3 Yağmur Duman TENİS KORTU1 SAAT 1 Mehtap . TENİS KORTU 1 SAAT 4 Yağmur Duman Kortu Kirası 1 saat 1 Mehtap . Kortu Kirası 1 saat 2 Administrator . BALI MASAJI 1 Yağmur Duman BALI MASAJI 10 Mehtap . BALI MASAJI 11 Mehtap . 6 EL MASAJI 1 Mehtap . COUPLE MASAJ 1 Yağmur Duman PILATES SINGLE 1 Yağmur Duman CİLT BAKIMI 3 Mehtap . CİLT BAKIMI 4 Mehtap . Çikolata Masajı 2 Yağmur Duman BAL BAKIMI 1 Mehtap . BAL BAKIMI 4 Yağmur Duman 4 EL MASAJI 1 Mehtap . 4 EL MASAJI 2 Yağmur Duman YOSUN BAKIMI 1 Mehtap . MASA TENİSİ 1 Mehtap . HAMAM 1 Mehtap . KESE & KÖPÜK 1 Yağmur Duman TRIO 1 Mehtap . TRIO 1 Özge Öztürk TRIO 1 Özge Öztürk 4 EL MASAJI 16 Ozan Çicek ISVEC MASAJI 1 Özge Öztürk ISVEC MASAJI 3 Ozan Çicek 4 EL MASAJI 5 Ozan Çicek BAL BAKIMI 3 Özge Öztürk BAL BAKIMI 10 Özge Öztürk THAI MASAJI 1 Ozan Çicek Çikolata Masajı 24 Özge Öztürk Çikolata Masajı 12 Ozan Çicek SICAK TAŞ 5 Özge Öztürk SICAK TAŞ 14 Özge Öztürk CİLT BAKIMI 17 Ozan Çicek SHATSU MASAJ 1 Özge Öztürk PILATES SINGLE 16 Özge Öztürk SHATSU MASAJ 4 Ozan Çicek CİLT BAKIMI 3 Ozan Çicek PILATES SINGLE 2 Özge Öztürk COUPLE MASAJ 10 Ozan Çicek COUPLE MASAJ 6 Özge Öztürk 6 EL MASAJI 16 Ozan Çicek 6 EL MASAJI 18 Ozan Çicek BALI MASAJI 44 Özge Öztürk BALI MASAJI 59 Ozan Çicek Kortu Kirası 1 saat 5 Özge Öztürk Kortu Kirası 1 saat 5 Ozan Çicek TENİS KORTU 1 SAAT 12 Özge Öztürk TENİS KORTU 1 SAAT 5 Ozan Çicek Japon Masajı 41 Özge Öztürk Japon Masajı 26 Ozan Çicek Türk Masajı 38 Özge Öztürk Türk Masajı 17 Yeşim Yıldırım Türk Masajı 32 Yeşim Yıldırım Japon Masajı 35 Yeşim Yıldırım TENİS KORTU 1 SAAT 3 Yeşim Yıldırım Kortu Kirası 1 saat 4 Yeşim Yıldırım BALI MASAJI 101 Yeşim Yıldırım 6 EL MASAJI 20 Yeşim Yıldırım COUPLE MASAJ 11 Yeşim Yıldırım SHATSU MASAJ 4 Yeşim Yıldırım PILATES SINGLE 3 Yeşim Yıldırım CİLT BAKIMI 10 Yeşim Yıldırım THAI MASAJI 1 Yeşim Yıldırım Çikolata Masajı 18 Yeşim Yıldırım SICAK TAŞ 14 Yeşim Yıldırım BAL BAKIMI 4 Yeşim Yıldırım 4 EL MASAJI 9 Yeşim Yıldırım HAMAM 1 Yeşim Yıldırım KESE & KÖPÜK 1 Yeşim Yıldırım SAUNA TEK GİRİŞ 3 Yeşim Yıldırım YOSUN BAKIMI 1 Yeşim Yıldırım TRIO 2 Funda Güngörür TRIO 3 Funda Güngörür 4 EL MASAJI 5 Funda Güngörür BAL BAKIMI 8 Funda Güngörür THAI MASAJI 4 Funda Güngörür Çikolata Masajı 10 Funda Güngörür SICAK TAŞ 14 Funda Güngörür SHATSU MASAJ 5 Funda Güngörür CİLT BAKIMI 18 Funda Güngörür PILATES SINGLE 9 Funda Güngörür COUPLE MASAJ 11 Funda Güngörür 6 EL MASAJI 15 Funda Güngörür BALI MASAJI 60 Funda Güngörür Kortu Kirası 1 saat 4 Funda Güngörür TENİS KORTU 1 SAAT 6 Funda Güngörür Japon Masajı 41 Funda Güngörür Türk Masajı 58 Ayşegül Duman Türk Masajı 22 Ayşegül Duman TENİS KORTU 1 SAAT 1 Ayşegül Duman Japon Masajı 17 Ayşegül Duman Kortu Kirası 1 saat 1 Ayşegül Duman BALI MASAJI 53 Ayşegül Duman 6 EL MASAJI 2 Ayşegül Duman COUPLE MASAJ 7 Ayşegül Duman SHATSU MASAJ 7 Ayşegül Duman PILATES SINGLE 9 Ayşegül Duman SICAK TAŞ 7 Ayşegül Duman CİLT BAKIMI 10 Ayşegül Duman Çikolata Masajı 5 Ayşegül Duman THAI MASAJI 1 Ayşegül Duman BAL BAKIMI 6 Ayşegül Duman ISVEC MASAJI 2 Ayşegül Duman TRIO 3 Ayşegül Duman KESE & KÖPÜK 1 Ayşegül Duman 4 EL MASAJI 15 Mert Ilgın HAMAM 2 Mert Ilgın YOSUN BAKIMI 2 Mert Ilgın SAUNA TEK GİRİŞ 1 Mert Ilgın MASA TENİSİ 1 Mert Ilgın 4 EL MASAJI 11 Mert Ilgın KESE & KÖPÜK 1 Mert Ilgın TRIO 2 Mert Ilgın ISVEC MASAJI 1 Mert Ilgın BAL BAKIMI 1 Mert Ilgın Çikolata Masajı 6 Mert Ilgın THAI MASAJI 1 Mert Ilgın SICAK TAŞ 11 Mert Ilgın CİLT BAKIMI 7 Mert Ilgın SHATSU MASAJ 3 Mert Ilgın PILATES SINGLE 6 Mert Ilgın 6 EL MASAJI 9 Mert Ilgın COUPLE MASAJ 11 Mert Ilgın BALI MASAJI 28 Mert Ilgın Kortu Kirası 1 saat 2 Mert Ilgın TENİS KORTU 1 SAAT 5 Mert Ilgın Japon Masajı 6 Mert Ilgın Türk Masajı 11 Aslı Öztürk Türk Masajı 14 Aslı Öztürk Japon Masajı 2 Aslı Öztürk TENİS KORTU 1 SAAT 1 Aslı Öztürk BALI MASAJI 31 Aslı Öztürk 6 EL MASAJI 2 Aslı Öztürk COUPLE MASAJ 1 Aslı Öztürk PILATES SINGLE 10 Aslı Öztürk CİLT BAKIMI 5 Aslı Öztürk SICAK TAŞ 4 Aslı Öztürk Çikolata Masajı 1 Aslı Öztürk BAL BAKIMI 2 Aslı Öztürk ISVEC MASAJI 1 Aslı Öztürk TRIO 3 Aslı Öztürk 4 EL MASAJI 2 Aslı Öztürk KESE & KÖPÜK 2 Ahmet Yılmaz TRIO 2 Ahmet Yılmaz Çikolata Masajı 2 Ahmet Yılmaz CİLT BAKIMI 5 Ahmet Yılmaz SICAK TAŞ 4 Ahmet Yılmaz PILATES SINGLE 20 Ahmet Yılmaz SHATSU MASAJ 1 Ahmet Yılmaz COUPLE MASAJ 2 Ahmet Yılmaz BALI MASAJI 22 Ahmet Yılmaz Japon Masajı 2 Ahmet Yılmaz Kortu Kirası 1 saat 1 Ahmet Yılmaz Türk Masajı 10 Ceren Yıldızdoğan Türk Masajı 4 Ceren Yıldızdoğan Japon Masajı 3 Ceren Yıldızdoğan BALI MASAJI 26 Ceren Yıldızdoğan 6 EL MASAJI 4 Ceren Yıldızdoğan SHATSU MASAJ 5 Ceren Yıldızdoğan PILATES SINGLE 12 Ceren Yıldızdoğan SICAK TAŞ 11 Ceren Yıldızdoğan CİLT BAKIMI 3 Ceren Yıldızdoğan Çikolata Masajı 3 Ceren Yıldızdoğan THAI MASAJI 1
this the query result but I want to do this Türk Masajı TENİS KORTU1 SAAT Kortu Kirası 1 saat ............
consider the following table: name , TaskDate john , 01/01/2006 john , 01/03/2005 steve , 01/05/2006 i want to build a select statement that gives result like the following: name , JanuaryTotal , FebruaryTotal john , 150 , 110 steve , 170 , 50 so the result will be total tasks in specific month, is it doable in one select or should i turn around using hash tables ???
create procedure up_CrossTab (@SelectStatement varchar(1000), @PivotColumn varchar(100), @Summary varchar(100), @GroupbyField varchar(100), @OtherColumns varchar(100) = Null) AS /* Inputs are any 1000 character or less valid SELECT sql statement, the name of the column to pivot (transform to rows), the instructions to summarize the data, the field you want to group on, and other fields returned as output. 1 */ set nocount on set ansi_warnings off
declare @Values varchar(8000); set @Values = '';
set @OtherColumns= isNull(', ' + @OtherColumns,'') /* An 8000 varchar variable called @values is created to hold the [potentially filtered] values in the pivot column. @Values is initiated to an empty string. Then, a temporary table is created to hold each unique value. After the table is created, its rows are loaded into the variable @values. It's usefullness completed, the temporary table is destroyed. 2 */ create table #temp (Tempfield varchar(100))
insert into #temp exec ('select distinct convert(varchar(100),' + @PivotColumn + ') as Tempfield FROM (' + @SelectStatement + ') A')
select @Values = @Values + ', ' + replace(replace(@Summary,'(','(CASE WHEN ' + @PivotColumn + '=''' + Tempfield + ''' THEN '),')[', ' END) as [' + Tempfield ) from #Temp order by Tempfield
drop table #Temp /* Finally, a dynamic sql select statement is executed which takes the GroupByField, and OtherColumns, passed into the procedure, and each of the Values from the Pivot Column from the passed in SELECT statement . 3 */ exec ( 'select ' + @GroupbyField + @OtherColumns + @Values + ' from (' + @SelectStatement + ') A GROUP BY ' + @GroupbyField)
set nocount off set ansi_warnings on GO
And then my sql query is as like
EXEC up_CrossTab 'SELECT ProdId, GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ', 'Year(GrnDate)', 'sum(Quantity)[]', 'ProdId'
error occurring
ambiguous column name ‘ProdId’
But when I compile this query
EXEC up_CrossTab 'SELECT grnNo,GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ', 'Month(GrnDate)', 'sum(Quantity)[]','GrnNo'
Hello --I think this is the term for what I want (something that could be generatedin ACCESS using a pivot table, or, maybe Yukon).We have data for sales by sales people in sales regions. More than oneperson sells in a region.We want to display data as follows:salesperson's names----------- ----------- ----------- ----------- ----------- -----------region 1region 2 row/column values are sales amounts for person inthat regionregion 3We will add a WHERE clause for the period of time covered.I don't want to have to change the query if a new salesperson or new regionis added.Can this be done in SQL Server 2000, Analysis Services, OLAP, anywhere?Can someone direct me to examples of how to do this?Thanks for any direction.Larry Mehl
Instead of doing a Count for the Pivot (the count will always be either 0 or 1 due to the design of the table being used), I would like to return an "X" for those records with a count of 1, and return a blank (otherwise null) for those records with a count of 0. So, the result set would look like:
ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast 1000 Item1000 X X X X X 1024 Item1024 X X X X X 135 Item135 X X X X X 107 Item107 X X X 106 Item106 X X X X X
I tried using a Case statement within the PIVOT portion, but I either did it incorrectly or it's not possible to do use a Case within the Pivot. Can I easily accomplish this?
In such a case, devising a dynamic cross-tab query, to cover all the items, and result like a pivot like the following to represent the data in a multi-header pivot like following:
Hi all I'm transferring some Access queries to SQL server and the crosstabs don't want to work, can anyone shed any light on the query below.
Thanks.
TRANSFORM Min(tCompany.cCompanyName) AS CompanyName SELECT tProjContacts.ProjectID FROM tCompany INNER JOIN (tProjContacts INNER JOIN tCompanyType ON tProjContacts.CoTypeId = tCompanyType.CoTypeId) ON tCompany.CompanyID = tProjContacts.CompanyID WHERE (((tCompanyType.CoType) Like "*topo*" Or (tCompanyType.CoType) Like "*ground*")) GROUP BY tProjContacts.ProjectID PIVOT tCompanyType.CoType;
i have marked in bold the query in question ..whch gives me the the runtime error mentioned at the below , i double checked everything all the table names and the field names are correct so whats the problem , please anyone??im really stumped! if (Radio_Btn_Book.Checked == true) { string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state = 1 where I.Book_Id = Books.Book_Id and Books.Book_Name = '" + Session["Book_name"].ToString()+"'"; SqlCommand Cmd_book = new SqlCommand(book_query, con); con.Open(); Cmd_book.ExecuteNonQuery(); con.Close(); } ERROR: The column prefix 'Books' does not match with a table name or alias name used in the query.The column prefix 'Books' does not match with a table name or alias name used in the query.
Hi, TABLES (Association CONTAINS COLUMNS AssociationID), (Group CONTAINS COLUMNS GroupID, GroupName, AssociationID), (GroupMembers CONTAINS COLUMNS GroupID, GroupMemberID, UserprofileID, DateCreated, DateRemoved), (UserProfiles CONTAINS COLUMNS UserProfileID, UserID), (AllUsers CONTAINS COLUMNS UserID, FirstName, LastName) I am trying to write a query to collect information about from the tables. I need to collect all the Users who are not members of Group A in Association I. Note that Users can belong to more than one group and have more than one profile. I would appreciate it if you could help me figure out how to deal with this logic. Thanks in Advance
Hi I have a function which returns the periodId from the period table and the function is as follows: ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime) RETURNS int AS
BEGIN
DECLARE @month int SET @month = Month(@Date) RETURN CASE WHEN @month BETWEEN 1 AND 3 THEN 5 WHEN @month BETWEEN 4 AND 6 THEN 6 WHEN @month BETWEEN 7 AND 9 THEN 7 WHEN @month BETWEEN 10 AND 12 THEN 8 END END Which works fine but i want to do the following with it. We produce quarterly statements.. So the user comes in jan or the begining of Feb to upload and process and order Statements from 10/31 to 12/31 the the 4 quarter data.. So Is there a way i can check if the user orders the statement in between jan and feb it has to reture PeriodId 5.
I have three parameters called @rsLengthofservice, @rsLengthofServicematch and @rsLenghtofservicePS. How do i check if all the three parameters have the same value.. Any help will be appreciated.. Regards, Karen
Hi, I have this query SELECT o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId --cp.ClientId FROM [Order] o Inner Join Plan cp on o.PlanId = cp.PlanId and o.CreatedByUserId = cp.UserId Inner Join User c on o.CreatedByUserId = c.UserId WHERE --cp.ClientId = @ClientId --AND o.OrderDate BETWEEN @StartDate AND @EndDate ORDER BY o.OrderId DESC Which gives the list of order from the start date to the end date..but it doesnt List the orders which are some mins apart. For eg. 16524/24/2008 10:48:05 AM4252840 16534/25/2008 10:15:42 AM378550 16544/25/2008 10:51:24 AM3147110 16614/25/2008 1:35:54 PM425045 16624/25/2008 2:30:43 PM4174387130 16634/25/2008 2:47:14 PM154825 16644/25/2008 2:49:10 PM265085 it listed order no. 1652 and 1654 but not 1653 and in the next 4 it didnt list 1663.. why is this happening.. I am using a reports in the report server. any help will be appreciated. Regards,Karen
my app contains one form (aspx) and it has different controls to be filled by user (textbox,radiobutton ..etc)it has one button which i want to use to pass values entered in these controls to other page and do some queries to sql server there (2nd page)Now the thing is ...my controls can have NULL values ...like user could enter just one parameter and hit button or user can fill 2 parameter and hit enter so on the other hand (2nd page) how should i query the database accordingly ....
I have some data that looks like this: field1(pk) field2 field3 563136 199535840A D2119562408 199615461C D2119562806 199748610A D2119547463 199748610A D2368562278 200255598A D2368562286 200255598A D2468 Field2 can have the same value. What I need is to return all records where field3 is not 'D2468' ever for field2. So with the above data, I'd want the first 4 records returned, but not 5 and 6 because for those field2 is the same value and in 6, field3 is 'D2468'. So, I can't simply say: SELECT a.field2FROM table1 a inner join table2 b on a.field2 = b.field2 and a.field5 = b.field5where a.field3 not like 'D2468' because it will still return record 5. Can anyone help me with this? Thank you!
I need help here. I have two queries one on each server and one does not work the way it should be. Can some help me here in figuring out if both these queries are same or no. If there is any difference, what is causing that difference. Thanks
Query1: SELECT Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.CountyName,Shipment.Zip, Shipment.WarehouseCd, Phone FROM Orders INNER JOIN ((NewWarehouse INNER JOIN Shipment ON NewWarehouse.WarehouseCd = Shipment.WarehouseCd) INNER JOIN WarehouseCurrency ON NewWarehouse.WarehouseCd = WarehouseCurrency.WarehouseCd) INNER JOIN Warehouse ON (Shipment.CountryCd = Warehouse.CountryCd) AND (NewWarehouse.WarehouseCd = Warehouse.WarehouseCd)) ON Orders.OrderNum = Shipment.OrderNum WHERE BE = 5 AND AddressTy = 1 AND WarehouseCurrency.CurrencyCd ='USD' AND Active = -1 GROUP BY Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.CountyName, Shipment.Zip, Shipment.WarehouseCd, Phone ORDER BY Min(Shipment.OrderNum) DESC)
Query 2: (This is working) SELECT Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.Zip, Shipment.WarehouseCd, Shipment.Phone, Shipment.CountyName FROM ((Orders INNER JOIN (NewWarehouse
INNER JOIN Shipment ON NewWarehouse.WarehouseCd = Shipment.WarehouseCd) ON Orders.OrderNum = Shipment.OrderNum) INNER JOIN WarehouseCurrency ON NewWarehouse.WarehouseCd = WarehouseCurrency.WarehouseCd) INNER JOIN Warehouse ON (NewWarehouse.WarehouseCd = Warehouse.WarehouseCd) AND (Shipment.CountryCd = Warehouse.CountryCd) WHERE (((Orders.BE)=5) AND ((Shipment.AddressTy)=1) AND ((WarehouseCurrency.CurrencyCd)='USD') AND ((NewWarehouse.Active)=-1)) GROUP BY Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.Zip, Shipment.WarehouseCd, Shipment.Phone, Shipment.CountyName ORDER BY Min(Shipment.OrderNum) DESC
I am sorry I am not that good in sql as some of you may be. Thanks.
I have 2 tables: Table1 has a field [Description](varchar) 100 Table2 has a field [IDDescription] int and field [Description] varchar(100)
In table 2 I have IDDescription store but some time also description wich is not store in table2 I need to pull all description (if it is from table2 or table1). How do I do this?
