I’m using SQL Server 2017 Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) – 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19042: ). I’m trying to sort the results of the STRING_AGG function. This works …
Tag: sql-server-2017
Pivoting but handling Column Names
I’m currently getting a result-set back as follows: What I’m trying to do is get the results to appear as follows: I’ve put together the following query, and I was curious as to whether someone had a better way of doing it: Here’s the sample data: Answer You can use conditional aggrega…
Turn these temp tables into one longer subquery (can’t use Temp tables in Power BI)
Currently I have created these temp tables to get the desired output I need. However, Power BI doesn’t allow the use of temp tables so I need to get this all into 1 query using inner selects. This code is simple and straight forward. However I can’t seem to figure out using a subquery. Here is wha…
Split records into buckets based on counts in reference table
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…
Merge not inserting new values
I’m trying to use MERGE to insert new values to a table only if they don’t already exists in the same table. This is the query I am using: MERGE [dbo].[TARGET_TABLE] AS Target USING (SELECT [NAME] …
SQL Remove all leading zeros
My join returns null because I am not able to remove the leading zeros. Basically, I am trying to replace the pol.Policy_Number with src.Policy_Number for a large number of policies. Is there a way I can remove the leading 0s in the ON statement for src.Policy_Number? I think Padding is needed, but I am new t…
Break periods at the end of the month
SQL Server 2017 CREATE TABLE [TABLE_1] ( PLAN_NR decimal(28,6) NULL, START_DATE datetime NULL, ); INSERT INTO TABLE_1 (PLAN_NR, START_DATE) VALUES (1,’2020-05-01′), (2,’2020-08-01′); …
How to comapre two rows in SQL Server
Consider the below rows in a table I want to compare two rows and get difference between them. My required result would be: Answer …adjust..
Query not returning expected data
I’m looking for an explanation for why 1 of the following 3 queries aren’t returning what I am expecting. Assume the following: Anmodning with ANNo=1, ANCpr=1111112222 And the Person table doesn’t have a row with PSCpr=111111-2222 Queries are executed in Management Studio against a SQL Serve…
Use a common field in two tables with XML in SQL Server
How delete the redundance in the table #XMLItm? My code: In this code I want load information in two tables for a common field ID_LIST. How load the ID_LIST from #XMLLst in the temp table #XMLItm? I want delete the field ID_LIST from XML in the table #XMLItm and take a ID_LIST from temp table #XMLLst; Functio…