TLDR: I have a scalar function, that expects a table-valued parameter and returns the XML representation of that table. Now I need to use this function in a larger Update statement, where the table-parameter of the function is generated by inner Select statements. Hi, We have an XML type definition, say [dbo]…
Tag: sql-server
check for EMAIL format in ROW and return value inside SELECT query
I have a table called user_data and few columns like LOGIN_NAME The column LOGIN_NAME may contain “emailformat@email.com” or just a “username” I need to know if it is possible for me to run a select query and return emailAddress if row has email type format else return userName if it i…
How to do I query all distinct rows with only their highest values?
I have been trying to query each city’s popular genre. I am only trying to get the rows that I have highlighted. I tried using MAX() on a group by but gave me a syntax error. My CTE query is as follows, its based on the dbeaver sample dataset: I tried the following query. Answer I don’t have a dat…
SQL server – Update one column based on the values of other columns
Say there are five columns in the table and I’d like to update the column E based on the value of column A,B,C,D. If the corresponding value is Y, then write the column name as part of the value in column E. The expected result is shown in the second image. Originally, the data in the table looks like t…
Without loop how to insert values into table from XML node
I have the following requirement. I have an XML variable of values. I now should insert the values for this XML variable to a table (Table A), but need to check if the items (one by one) in the XML variable are already present in another table (Table B) and the count of that individual item present in table B
Joining Temp Tables Using Indexes
I’m just beginning to learn about INDEX and I’ve heard it could speed up results but my testing is having the opposite effect. Some points about the data: My company uses a SQL Server but I don’t know much else beyond that I’m just an employee and don’t have any sort of admin acc…
SQL Server – Ordering Combined Number Strings Prior To Column Insert
I have 2 string columns (thousands of rows) with ordered numbers in each string (there can be zero to ten numbers in each string). Example: The end result is to combine these 2 columns, sort the numbers in ascending order and then put each number into individual columns (smallest, 2nd smallest etc). e.g. Cols…
Converting Data Type for SQL Server View
I am trying to query a SQL view to show the transactions for the last business day (i.e. the day before, or the Friday if the day the query is ran is Monday). My query looks like this: I get the following error when I try to run this query: Msg 257, Level 16, State 3, Server PMICDB01A, Procedure ,
Query that returns maximum and corresponding FK(ID) group by year which comes from another select that has averages group by FK(ID) and year
I have a raw table which contains the id (pk AI), farm_fk (or HERD), birthdate, nm (some indicator). I want to get the maximum of nm averages for each existing year and corresponding farm_fk. What is DID? 1st from table I got the averages of nm for each farm_fk group by year which returns some thing like this…
How to set flag to true and also get the total while grouping
Table Output as Thank you in Advance Answer Use conditional aggregation: You can also express the condition as: