I think you need an UNPIVOT – SELECT OrgID, Property, DataValue FROM (SELECT OrgID, Property, DataValue FROM YOUR_TABLE UNPIVOT ( DataValue FOR Property IN (ExtensionDigits ,StartExtension ,LastUsedExtension ,Server1IP ,Server2IP ,ServerPort ,IsFunctionOn ,VolumeDecibel) ) unpvt) temp WHERE DataValue IS NOT NULL; 1 solved Pivot selected columns into rows with SQL Server [closed]
I suspect you are trying to achieve this by using one a single join between the tables – whereas what you actually need is two separate joins: SELECT table1.module as mod_code, table1.season as psl_code, table2.Sdate as ypd_sdate, table3.Edate as ypd_edate FROM t1 as table1 JOIN t2 as table2 ON table2.yr = table1.year AND table2.season = … Read more
On SQL-Server use CHARINDEX CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] ) DECLARE @CAMPO30 varchar(64) = ‘2,663.25’; SELECT CASE WHEN CHARINDEX(‘.’, @CAMPO30) > 1 THEN CAST(REPLACE(REPLACE(@CAMPO30,’.’,”),’,’,’.’) AS FLOAT) ELSE CAST(REPLACE(@CAMPO30,’,’,’.’) AS FLOAT) END AS CAMPO30 GO | CAMPO30 | | ——: | | 2.66325 | dbfiddle here 1 solved I need to convert … Read more
I think you didn’t clear the parameters. You should clear it always after your transaction. cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(“@UserId”, userId); cmd.Parameters.AddWithValue(“@MonthlyIncomeName”, income.MonthIncomeName); cmd.Parameters.AddWithValue(“@MonthlyIncomeAmount”, income.MonthIncomeAmount); cmd.Parameters.AddWithValue(“@TotalMonthlyIncome”, income.MonthIncomeAmount); cmd.Parameters.AddWithValue(“@Month”, insertMonth); cmd.Parameters.AddWithValue(“@Year”, insertYear); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); // insert this line And search about AddWithValue vs Add like @cha said that check your parameter types. See this. 1 solved Stored … Read more
I’m not sure what you’ve tried so far but based on your sample data, try this: SELECT UserID, SessionID, MIN(timestamp) StartTime, MAX(timestamp) EndTime, DATEDIFF(s,MIN(timestamp), MAX(timestamp)) SecondsDifference FROM Table GROUP BY UserID, SessionID This kind of data is usually tricky because you don’t have the luxury of a sessionid, or the sessionid doesn’t truly reflect what … Read more
The syntax for selecting values into variables in MySQL is select … into. For example you could write: SELECT POSITION, SecPosition FROM orderdetails WHERE OrderID = orderDetID INTO strPos, strPosOtherRes; The message “Not allowed to return a result set from a function” means that the select statements as they stand now would be returning a … Read more
Seems like you have missed mapping of column vs values. Please pass your values in correct sequence as per BillRegister table columns. Thanks, Amrut solved Disallowed implicit conversion from data type datetime to data type float
Please use this to achieve your result: select [name] = [name] ,[rowid] = [rowid] ,[type] = [type] ,[company] = [company] ,[date] = [date] ,[kpi] = [kpi] ,[value] = iif([rowid] = 1, isnull([kpi], [lvalue].[value]), isnull([kpi], [avgvalue].[value])) from #urgent as [u] outer apply ( select top 1 [value] = [kpi] from #urgent where [rowid] = 1 and … Read more
If you want to connect to SQL server remotly you need to use a software – like Sql Server Management studio Follow these Instructions:- Start SQL Server Browser service if it’s not started yet. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on … Read more
You could calculate the 1st of the month using EOMONTH something like this SELECT DISTINCT ATB.AcountCountDesc,TB.LastFirstName,N.EMAIL,TB.AccountNumber,TB.OpenShareCount,TB.MemberOpenDate, TB.OpenMemberCount,TB.OpenShareBalance,SH.ShareType,FORMAT(SH.ShareOpenDate,’MM/dd/yyyy’) AS “ShareOpenDate”, SH.ShareCreatedByUser,SH.ShareCreatedByUserName,SH.ShareBranchName,SH.ShareBranch,cast(month(SH.ShareOpenDate) as varchar) + “https://stackoverflow.com/” + cast(year(SH.ShareOpenDate) as varchar)as ‘Open Period’, CONCAT(SH.ShareCreatedByUser,’-‘,SH.ShareCreatedByUserName) ‘Opened By’ FROM arcu.vwARCUOperationMemberTrialBalance as TB JOIN arcu.vwARCUOperationMemberAccountTrialBalance as ATB ON TB.MemberSuppID = ATB.MemberID and TB.ProcessDate = ATB.PDate and TB.MemberStatus = 0 — Account … Read more
I would expect something like this: INSERT INTO PensionFunds (EmployeeId, Amount, PensionProvider) SELECT EmployeeId, Salary * 0.05, PensionId FROM CompanyEmployees; This does not take a zillion things into account, such as: Not all rows in CompanyEmployees might be active employees. Not all rows in CompanyEmployees might be contributing to the pension fund. There could perhaps … Read more
Use string_split(): select t.ticket, s.value as agent from t cross apply string_split(t.agent, ‘ ‘) s order by s.value; Here is a db<>fiddle. solved separate values by new line and split to new row [duplicate]
I have prepared a SQL script for you which splits the input string twice then converts is into row data, and finally inserts into a database table To split string data, I used this SQL split string function First of all, you need to create this function on your database. Unfortunately, if you do not … Read more
There are 3 quite different methods needed to arrive at the counts, so I have used 3 separate sub-queries. see this working at sqlfiddle (but not on MS SQL Server) here: http://sqlfiddle.com/#!5/9df16/1 Result: | Total_Count | Repeat_Return | Same_Symptom_Return | |————-|—————|———————| | 6 | 2 | 1 | Query: select (select count(distinct SN + RMA … Read more
CONVERT(varchar,b.[Action Date],101) as Action_Date With that code i convert my datetime to date format solved Convert datetime column value from SQL Server in vb.net