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: tsql
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…
Insufficient user permission performing an operation on Sql Server Azure
I am trying to re-run migration on a newly created DB in azure. DB, User, and all privileges were created from scratch. I basically granted all operation permission to the user including DB admin …
Looping in SQL with TempTable
I am new to SQL Server, can you please help me to write query for my scenario? I have this Table1 : Input to my stored procedure is a PolicyNumber e.g 1234 and the code should return 5 rows. I need to cover these cases: If the first payment is not cancelled, I need return that row. If the first
Dynamic Pivot Table by Month
I’m trying to create a dynamic pivot table in SQL that will report based on month and year. I did a bunch of research and was able to come up with the below query: I am able to print the @column variable successfully, but the problems happen when I try to set it in the @dynamic variable. The error messa…
If Row from left joined table is null use another row
I have two table that I am trying to join(see below). Each have the same four columns on which I need to join. The second is the complete list, while the first may be missing some records. Where the records are missing I want to use the lowest ROWNUM that matches the other three. I am failing to see a
SQL- Find the price of the smallest product in a store
I would like to find the price of the smallest product in a store and in addition, in another column, populate this price in all the products of the same store. Here is my table and the desired result in the “results” column: Table1 Here is my request but it does not populate the price: SELECT loc…
Transform Columns to Rows dynamically using T-SQL
I have the following table where I need to transform Columns to Rows tbl_Survey: I need to have the following result: To have this result I used the following code: But, my Quest_1, Quest_2, Quest_3 values could potentially be changed / or even added a new once… Are there ways to code them, so it can be…
How do I return only the most recent record on a date field split into two
Scenario: Person A takes test B three times in the span of two year. There will be three entries for that person. However, I need to write a query that tells me the number of Persons that have taken a test(just one, the latest test). The problem with this is I have a column labeled, Test_Month (xx) and Test_y…
How to transform my existing table into this specific format using T-SQL?
I am using SQL Server 2014. I have a table T1 (extract shown below): I would like to have the following output using a T-SQL query: The logic here is that data related to a specific ProfileID is to be transformed into the wide format. The StayID column is always unique. I have been looking into the pivot func…