This is a simplified version, stripped down to my core problem. I have a ContactData table with millions of rows of data, with each contact record broken up into categories with a ReferenceID. I now have to assign a new UpdatedValue to each contact record, based on counts from the separate NewValues table als…
Tag: sql-server
Dynamically fill an SQL table by detecting a name?
I want to dynamically fill a SQL table by detecting duplicate names. So, when I use an insert statement on this table – if the name already exists it adds an entry with the same ID. What I mean by this, for example I’ve got this empty table: Table Domain: DomainID Name URL StatusID ErrorID Date So…
Transpose row to column in SQL Server
I have a table like below: The first row is the header. Now, I would like to transpose table into this I have tried unpivot but the result is not desired table. Thanks in advance, Answer I recommend using cross apply to unpivot and then aggregation: I much, much prefer this over pivot/unpivot. Why? APPLY impl…
Check if rows exists in SQL Server using R and then Insert or Update rows
I have a table with the below structure. ID Entity UserName UserRole UserStatus UpdatedDate Clustered key is defined on columns Entity, UserName, UserRole. UserStatus for an Entity-UserName-UserRole …
How to select distinct value from multiple tables given a column filter?
I have this table Products Id ProductName ProductCode 1 Alexa P0001 2 Alexa P0002 3 Alexa 2 P0003 4 Aquarium P0004 5 Aquarium X P0004 6 Bathtub P0005 Scenario 1: Expected Result: Returns distinct ProductName along with Id Id ProductName 1 Alexa 3 Alexa 2 4 Aquarium 5 Aquarium X Actual result: Returns all prod…
Using the Union function in SQL to reverse columns
I have the following SQL code to produce the following table of data: I am hoping to transform data to create a table that looks like: What would be the best way to achieve this? Something like creating two tables and then a Union join? Answer You can use self join to achieve what are you looking for. Schema:…
How to loop array of objects from table column?
Table columns : My table column details has json object like I want to write a query which will give me records like below : I tried with RESULT WITH COUNT : EXPECTED Answer To get all the objects from json array along with other columns you can use OpenJson() and Cross Apply as below: Query: Output: id cnt n…
SQL – Cast Column as XML then Query Value from said column
I have a column with a bunch of unformatted XML code. I am trying to really just query 1 value out of the column. The value inside of the column is listed below: The value that I am looking for is displayValue=”NSharePoint Read Item” which is located in the line: I have the following query: which …
What does the “i” stand for in this SQL query?
I’m looking at a bunch of SQL query’s made by another individual and am trying to interpret how they were made. I’m fairly new to SQL so its involved a lot of Googling, its also my first time posting …
Conditional Aggregation with multiple case and group by
The query below gives me average of case when QuoteStatusID = 6 but it I am having issues with associating the average by Street column. QuoteTable QuoteID QuoteStateID ProjectManager_userID Shipping_AddressID 1 6 12 56 2 6 12 56 3 26 12 56 4 6 12 18 5 26 12 18 Shipping_AddressID 56: 338 Elizabeth St 18: 83 E…