I have a query that uses the PIVOT function and works fine in SQL 2012. I've been asked to move the query to a database that has the compatibility level set to 80(SQL 2000). I receive an "Incorrect syntax near" error when I try to excute the query on the SQL 2000 database. I would like to duplicate the exiting PIVOT functionality in SQL 2000.The existing query retrieves employee names and the order that the employee should be displayed from a table. The names will appear on the report according to the order that is retrieved from the database. Also, the users have requested that only 5 names appear on each row of the report. This is why the PIVOT function was needed. Below is an example of how the existing query works.
Table
CREATE TABLE [dbo].[EmpGuest](
 [Guest_ID] [int] NOT NULL,
 [Guest_Name] [varchar](80) NULL,
 [Display_Order] [int] NULL
) ON [PRIMARY]
I am using the pivot task to to a pivot of YTD-Values and after that I use derived columns to calculate month values and do a unpivot then.
All worked fine, but now I get this error message:
[ytd_pivot [123]] Error: Duplicate pivot key value "6".
The settings in the advanced editor seem to be correct (no duplicate pivot key value) and I am extracting the data from the source sorted by month.
Could it be a problem that I use all pivot columns (month 1 to 12) in the derived colum transformation and they aren´t available at this moment while data extracting is still going on?
I have a simple pivot table (screenshot below) that has two variables on it: one for entry year and another for 6 month time intervals. I have very simple DAX functions that count rows to determine the population N (denominator), the number of records in the time intervals (numerator) and the simple percent of those two numbers.
The problem that I am having is that the function for the population N is not overriding the time interval on the pivot table when I use an ALL function to do so. I use ALL in other very simple pivot tables to do the same thing and it works fine.
The formula for all three are below, but the one that is the issue is the population N formula. Why ALL would not work, any other way to override the time period variable on the pivot table.
Population N (denominator): =CALCULATE(COUNTROWS(analyticJudConsist),ALL(analyticJudConsist[CurrentTimeInCare1])) Records in time interval (numerator): =COUNTROWS(analyticJudConsist) Percent: =[countrows]/[denominatorCare]
I am trying to find duplicate records based on a set of criteria, and flag one of those records as unique and the other duplicate ones as dup.
I found this suggestion:
IF( CALCULATE(Â Â COUNTROWS( Table1 );Â Â FILTER( Table1; Â [Column1] = EARLIER( [Column1] ) && [Column2] = EARLIER( [Column2] ) )Â Â ) > 1; Â "dup"; Â "unique" )
However, this method flags all duplicates as dup. how I can get to the enclosed results for one particular student?The dup check column shows the desired results. The criterion is any record with the same STUDENT_ID, PROG_DESC, FISCAL_YEAR will be considered as duplicates, but should at least have one record out of that group of duplicates flagged as 1.
I have 2 excel tables files.One table has info about sales by country, by model, by date...and the other table has units by date.Obviously, the common key is "date"....and by creating a relationship, I can add "units" to my combined resulting table.However, I can't create the relationship. Excel keeps telling me: "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values".
Below is my query and returned rows. I am pulling unique dx codes for each account. In the case below I have 14 returned rows, each having a unique dx_key (second column), which I need to display across the report horizonaly versus vertical. (see below example)
I came across the post below by Manivannan.D.Sekaran on using the PIVOT function but I can't quite get the hang of it. Can someone give me some help please?
12451538 117782 20190 Hodgkin's disease, unspecified site, extranodal and solid organ sites 12451538 117783 5789 Gastrointestinal hemorrhage, unspecified 12451538 117784 5990 Urinary tract infection, site not specified 12451538 117785 2768 Hypopotassemia 12451538 117786 2449 Unspecified acquired hypothyroidism 12451538 117787 28529 Anemia of other chronic disease 12451538 117788 3051 Tobacco use disorder 12451538 117789 53540 Gastritis without mention of hemorrhage 12451538 117790 53190 Gastric ulcer without hemorrhage or perforation, without mention of obstruction 12451538 117791 5589 Noninfectious gastroenteritis and colitis 12451538 117792 56210 Diverticulosis of colon without mention of hemorrhage 12451538 117793 2113 Benign neoplasm of colon 12451538 117794 V1259 Personal history of diseases of circulatory system 12451538 117795 V011 Contact with or exposure to tuberculosis
Below is an example of a pivot table from the help in SQL2005. My question: Do you have to manually define the columns ([164], [198], etc.) for the pivot?
I would like to use this for a daily report where the columns would be the dates?
Thanks.
GO SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p PIVOT ( COUNT (PurchaseOrderID) FOR EmployeeID IN ( [164], [198], [223], [231], [233] ) ) AS pvt ORDER BY VendorID
SELECT Production_Date, Production_Order, LogicalVat KPI_Category, 'Probiotic Amt Consumed' KPI_Data1_Name,'RC Amt Consumed' KPI_Data2_Name FROM ( SELECT Production_Date , NULL Production_Order, LogicalVat, ReportValue ReportValue FROM BIReports.dbo.r_VatMake WHERE Production_Date between '10/27/2015' and '10/27/2015'
[code].....
Now the attributes changed into like below where the number after RC is dynamic and I can't use the query above anymore
Example: RC Amt Consumed - RC 6 RC Amt Consumed - RC 7 RC Amt Consumed - RC 8
Probiotic attribute changed into like below where number after PROB is dynamic
Probiotic Amt Consumed - PROB 15 Probiotic Amt Consumed - PROB 16 Probiotic Amt Consumed - PROB 17
I have the following data:Product Type Hours Controllers Development 105.0Controllers Research 1.0Controllers Sustaining 24.0How do I use SQL statement to change it to the following?Product Development Research SustainingControllers 105.0 1.0 24.0Thanks.DanYeung
playing with the Power pivot , DAX. While analyzing the DAX ,I came across a function EVALUATE , but when I tried this function in excel Power Pivot workbook - =EVALUATE 'Date' where 'Date' is my one of the Power pivot table , I was writing this function within the Calculation area of the Power Pivot model. I get the below error when I hit enter after writing the function ."The expression is not valid or appears to be incomplete..An MDX expression was expected while a full statement was specified."But in many forums I find the syntax is correct.
I have a DATESMTD function which is not working. Â This is what is happening, if there is no row data for the month it creates a month to date total similar to the year to date total instead of zero. Â See below my formula:
MTDSUM:=CALCULATE(SUM('Combined Years Dataset'[Net]),DATESMTD('Combined Years Dataset'[Period2]),'Date'[date])
Period 2 is a column with dates (end of monthdates) in a table called Combined Years Dataset.
So, if I have $200.00 data for Aug and no data for Sept, the system puts in 200.00 as the month to date  for Sept instead of zero.  What can I do to make the system insert zero in the month to date column instead of the $200.00.  What am I doing wrong in the formula.
I have a table which I would like to group on several columns, and for the Contract number, I'd like a maximum of four different columns which would contain pivoted information.
Here is my DDL:
CREATE TABLE [dbo].[SV00403]( [CUSTNMBR] [char](15) NOT NULL, [ADRSCODE] [char](15) NOT NULL, [Contract_Number] [char](11) NOT NULL, [WSCONTSQ] [int] NOT NULL,
-- drop table #temp create table #temp(ID bigint, Description varchar(50), ET varchar(200), ET_Status varchar(50), ET_Date datetime, ET_IsValid varchar(3))
insert into #temp select * from (values (1,'Test','A', 'Ack','08/15/2015', 'Yes'),(1,'Test','B', 'Nack','08/17/2015', 'Yes'),(1,'Test','C', 'Ack','08/21/2015', 'Yes')) a(ID, Description, ET, ET_Status, ET_Date, ET_IsValid)
I want to pivot this. My expected result look like this.
ID - Description - ET_A_Status - ET_A_Date - ET_A_IsValid -  ET_B_Status - ET_B_Date  - ET_B_IsValid - ET_C_Status  - ET_C_Date - ET_C_IsValidÂ
1  - Test   - 'Ack'    - '2015-08-15 00:00:00.000'  - 'Yes'   -  'Nack'  - '2015-08-17 00:00:00.000'  - 'Yes'  - 'Ack'   - '2015-08-21 00:00:00.000' -  'Yes'
I'm trying to Pivot and I keep getting an "Invalid Column Name" error, which I can't figure out since, if I run the query and exclude the Pivot statement, the query runs fine.
Columns ItemNmbr Char(31) not null SetupTime_I Numeric(19,5) not null WCID_IÂ Char(11) not null select ItemNmbr,SetupTime_I, WCID_I from RT010130 Results
Now run select ItemNmbr,SetupTime_I, WCID_I from RT010130 pivot (sum(SetupTime_I) for WCID_I in ([BLA01],[URE02])) PVT
And I get an Invalid Column Name error for both SetupTime_I and WCID_I - which, as far as I can tell, is demonstrably incorrect.
itemID | part 1Â Â Â Â Â Â Â Â | A 1Â Â Â Â Â Â Â Â | B 2Â Â Â Â Â Â Â Â | A 2Â Â Â Â Â Â Â Â | A 2Â Â Â Â Â Â Â Â | A 3Â Â Â Â Â Â Â Â | C
I need the table to look like the following:
itemID | part1 | part2 | part 3 1        | A       | B     | null 2        | A       | A     | A 3        | C       | null   | null
There will _never_ be more than three parts to an item, and it does not matter what order they are in.
In an Excel workbook I'm building a report using a PowerPivot data model.
I've a Calendar filter. If I select an year from this filter I need to show in a cell the total number of working days, present in the Calendar table as a column.
I created a Switch statement below that surprisingly doesn't throw any errors and some of it actually works. The problem is that the function calls in the 3rd and 4th sections of it below (in bold) are not working.
=switch(HASONEVALUE('s1JudgeIds'[JudgeName]), values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(Time[ExitMonthCategory]),Time[ExitMonthCategory] <= MAX(Time[ExitMonthCategory]))), values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",calculate([Numerator],all('s1JudgeIds'[JudgeName])), values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare],all('s1JudgeIds'[JudgeName])), values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare])
I am trying to find a solution in order to make a pivot dynamically. One of my department charge every month all the sales figure in one table and I need to pick up the last two months archived in order to make a pivot and to see if something is changed or not. What I am trying to do is to have these last two months dynamically. create table forum (customer varchar (50), nmonth varchar(6), tot int, archived datetime)
insert into forum values ('Pepsi','201503',100,'2015-04-28'), ('Pepsi','201504',200,'2015-04-28'), ('Texaco','201503',600,'2015-04-28'), ('Texaco','201504',300,'2015-04-28'),
[code]...
As you can see I have to change manually the values underlined every months but it's a temporary solution. How can I set up the last two months in a dynamic way?
I found this Microsoft article for creating crosstab-like queries in SSMS.Is it possible, however, to create this same query if I do not know what the values for the columns will be? Using their example for my problem, I will not know what the values in the "IN" criteria will be because my query would be for a "rolling" 12 months (thus causing that IN criteria to change every month).I've tried declaring variables to pull in the values, but since this will eventually go into a view, I don't think that I can use declared variables.
I need to pivot my records in columns into rows. Here is sample data:
create table #columnstorows ( Payer varchar (5), Â Â Â Â Â Â Â Â Â ID varchar (5), Â Â Â ClM varchar (2), Â Â Â Â Â Â Â Â Â Paid1 float, Paid2 float, Paid3 float ) Â Â Â Â Â Â Â Â Â Â Â
[code]....
Desired result:
Payer1 Payer2 ID1 ID2 Paid1A Paid1B Paid2A Paid2B Paid3A Paid3B U001 U002 Â 001 002 76.58 19.53 Â 153.48 96.43 Â 53.48 Â 200
I have been given a task to locate duplicate and report duplicate records and am trying to determine the best way to do this with databases that have 1 million records plus.
Say I have a table with 20 columns, I need to check to see if 3 of 10 specific columns match.
So if 2 columns are the same its no problem however if 3 or more match, they are considered duplicate.
Hi,Say I have a table Job with columns name, date, salary . I want to getthe name ,date and salary for the date when that person earned maximumsalary. I am using something likeSELECT X.name,X.date,X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);The problem is ; if a person earns maximum salary on two dates, both ofthe dates are printed. I just want to get any one of those two rows.I triedSELECT X.name,Min(X.date),X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);but it gives error.Can anybody please suggest a solution?Regards,Aamir
I have a query which I want to convert It PIVOT query
SELECTÂ Â Â Â Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName, SUM(CashSalesDetail.Qty) AS QtyFROMÂ Â Â Â Â Â Â Â CashSalesDetail INNER JOINÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â CashSales ON CashSalesDetail.CSNo = CashSales.CSNo INNER JOINÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Parties ON CashSales.PartyID = Parties.PartyIDWHEREÂ Â Â Â (CashSales.TransDate >= CONVERT(DATETIME, '2014-07-01 00:00:00', 102)) AND (CashSales.TransDate <= CONVERT(DATETIME, '2015-06-30 00:00:00', 102))GROUP BY Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName
following is my requirement after summing up qty of each area
ProductName     area a        area b      area c abc                         10               0               20 def                          1                 4              2 ghi                          5                 3              10 jkl                            7                15             3
Note: numeric values are Quantity of each product in each area
Someone ran an update statement multiple times so their are multiple entries in the table. Â What is the quickest way to track down the multiple entries? Â I would only want to see where timein and timeoff exist in the table multiple times for the same id. Â So this would be a duplicate
EntryID -- ID Â -- timein -- timeoff 1487 Â 11 Â Â 2015-05-05 16:33:23 Â 2015-05-05 18:45:26 1623 Â 11 Â Â 2015-05-05 16:33:23 Â 2015-05-05 18:45:26
I have a table that "Geography" that has the following columns: city, state, zip
There are tons of duplicate cities in this table. I ran this query and it shows me the number of occurrences of each city. I want to delete all the duplicates except for 1. I don't want to do this manually as there are a lot of records.
What would the SQL look like to delete the duplicate records but keep at least one?
I have student table where duplicate student exist by name with there fathers name and mothers name. I need to search those duplicate records. I do not need ti count them but If there is 5 same student with name then the query will show 5 name then I will delete individually. Below I am trying to show the scenario.
Student_name   _____________ Rocky Albert Rocky Williams Albert Robert
The query will show
Student_name   ______________ Rocky Rocky Albert Albert
