Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:
Text QueryPlan Plan_handle Name Value
select id,name,Address from person <showPlznXML... 010101 Table Person select id,name,Address from person <showPlznXML... 010101 column id select id,name,Address from person <showPlznXML... 010101 Table Person
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.
I got a table which gets populated by stor proc where we pivot the Sum(Balance of mortgage) by YYYYMM for the whole duration of the loan term.
I have a requirement to rename the column header where the previous month end balance period be renamed to P0.
if we run the report today, then the balance as at 31/09 should show under column P0 which now shows under 201509 and then P0 keeps shifting with each month run.
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL DROP TABLE #Pyr GO SELECT vst_int_id, [4981] AS Primary_Ins, [4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". I will post the definitions from another effort below.
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:
I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
With Sql Server Management Studio, while creating/modifying a table I want to specify one of its columns to store values in lower case only. Can it be done through the designer or by some other means?
I have written a Query to Pivot this data like below:
SELECT WAREHOUSE,ITEM, QTY
FROM
(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10, for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21, for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p
UNPIVOT
(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7, for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19, for20,for21,for22,for23,for24))AS unpvt
I would like to add some more code to the query, so for each FOR% column, i can put a numeric value in it. The value will be the numbers ,1 - 24 . One for each line as this represents Months Forward.
I'm trying to translate this portion of VFP code into LINQ query:
select COUNT(ID) as conflicts from dbo.max4sale where <<thisform.cWhere>> AND Start_Time >= <<VFP2SQL(m.ltBegin + m.lnStartTime)>> and Start_time <= <<VFP2SQL(m.ltEnd)>> AND CONVERT(varchar(5),Start_Time,108) <= <<VFP2SQL(m.lcEndTime)>> AND CONVERT(varchar(5),End_Time,108) >= <<VFP2SQL(m.lcStartTime)>> <<m.lcDays>>
Here is my non-working attempt:
var startTime = new DateTime(1900, 1, 1, beginDateTime.Hour, beginDateTime.Minute, 0); var endTime = new DateTime(1900, 1, 1, endDateTime.Hour, endDateTime.Minute, 0); var daysOfWeek = dailyLimits.Where(dl => dl.Selected == true).Select(ds => ds.WeekDay).ToList(); if (daysOfWeek.Count() < 7) // not all days of the week selected
[Code] .......
First of all, I see a bug in my logic now as the first part of the query I need to do all the time and only the second part if the count < 7. But that's not my problem - I can not figure out how to make times comparison only using LINQ. Ideally I think I'd like to have cast(start_time as time) >= @p1 as a result to be executed by LINQ.
BTW, I am only getting the error in run-time that Parse can not be interpreted. So, I need to figure out another way of making LINQ recognize my intent of checking time portion of the date only.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB 123 colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!
The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.
Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index.
And also I am not sure, whether creating index in more columns will create issue or not.
Attached the update query and index query.
CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2 ( [PART] ASC, [ORG] ASC, [SPLRNAME] ASC, [REPITEM] ASC, [RFQ] ASC,
I have a data with mutliple esn but different auditdate and opid. I will pull this data filtering by date and opid. My requirements is not to include the opid = 51 but need to get the desired opdesc for this esn that contains opid=51.
See below sample ddl and desired result. I dont want do include the opid = 51 because it will create a duplicate in transaction instead retain the opid 5
example: esn T9000000000019829505 has multiple rows with different auditdate and opid. retain the records for opid is equal to 5 but get the opdesc for opid is equal 51.
reate table #test (esn nvarchar(35), dateaudit datetime, opid int) insert into #test(esn,dateaudit, opid)values('352452060499834','2015-05-12 20:32:39.490',5) insert into #test(esn,dateaudit, opid)values('352452060499834','2015-07-06 17:35:14.210',5) insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-01-14 15:18:45.620',5)
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1) In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment. If I do change the query to something below then definately it will break - Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment ( DeptId int identity(1,1) primary key, name varchar(50) ) create table ChildDepartment ( Id int identity(1,1) primary key,
I hope to update a DateTime column value with a Time input parameter. Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE] @QuePoolID int=null ,@ApptTime time(7) ,@SendOnDate datetime
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
I am confused on key column of case table and key time column of nested table by using Time Series algorithm.
In my case, the case table structure is as below:
Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),
While the nested table structure is as below:
Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)
So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?
Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?
Hope it is clear for your advices and help.
And I am looking forward to hearing from you shortly.
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
Hi there, I am a new member of this site and I am not very much aware of T-sql's working. My question is what if I need to get one column's data to be the heading of another column. To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this StuID - - - SubID - - - -Tot - - -Obt 1 - - - - - - -1 - - - - - - -50 - - - 38 1 - - - - - - -2 - - - - - - -50 - - - 41 1 - - - - - - -3 - - - - - - -50 - - - 42 1 - - - - - - -4 - - - - - - -50 - - - 40 2 - - - - - - -1 - - - - - - -50 - - - 35 2 - - - - - - -2 - - - - - - -50 - - - 40 2 - - - - - - -3 - - - - - - -50 - - - 42 2 - - - - - - -4 - - - - - - -50 - - - 41
StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4 1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40 2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41
The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted. Thanks.
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,
the number of results per CaseID are varable some have 1 record others up to 36 records..I need to pivot the results based off of the competed date Name has 20 distinct values but we can have diplicates...so something like
Case Name Date 1 This 2015-01-01 1 That 2015-01-03 2 That 2013-01-01 2 Then 2014-12-03 2 This 2015-01-01 2 There 2015-04-03 3 This 1999-01-01 3 That 2001-01-02 3 This 2006-01-01
looks like this
1 This That 2 That Then This There 3 This That This
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 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?