STRING_AGG is not introduced in SQL SERVER 2016. It is introduced in SQL SERVER 2017. In the MSDN link you have provided it is mentioned THIS TOPIC APPLIES TO : SQL Server 2017 not SQL SERVER 2016. At the time of the question, this version was known by the code name “vNext”, described as: SQL … Read more
Using a table instead of a variable: SELECT count(priceLineLists.RoomTypeId) FROM Mytable CROSS APPLY OPENJSON (JsonDataCol, N’$.BasePriceLineList’) WITH ( RoomTypeId int) AS priceLineLists
You can use: select right(col, charindex(‘-‘, reverse(col)) – 1)
It happened to me when my LocalDB got updated (probably by Visual Studio installer) I’ve recreated the instance using the following steps in PowerShell console: λ SqlLocalDB delete MSSQLLocalDB LocalDB instance “MSSQLLocalDB” deleted. λ SqlLocalDB create MSSQLLocalDB LocalDB instance “MSSQLLocalDB” created with version 13.1.4001.0. λ SqlLocalDB start MSSQLLocalDB LocalDB instance “MSSQLLocalDB” started.
You just need to daisy-chain them: REPLACE(REPLACE(T2.[ShipToCode], ‘&’, ‘and’), ‘,’, ”)
You can use the following query: SELECT pr.person_id AS [person.id], pr.person_name AS [person.name], ( SELECT pt.pet_id AS id, pt.pet_name AS name FROM @Pets pt WHERE pt.pet_owner=pr.person_id FOR JSON PATH ) AS [person.pet] FROM @Persons pr FOR JSON PATH, ROOT(‘pet owners’) For more information, see https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/10/09/returning-child-rows-formatted-as-json-in-sql-server-queries/
While you can’t get away with using only a single OPENJSON, you can simplify your query a bit to make it easier to create dynamically by removing the nested subqueries: SELECT SectionName, SectionOrder, RenderName, RenderOrder, FieldName, FieldData FROM OPENJSON(@layout, ‘$.Sections’) WITH ( SectionName NVARCHAR(MAX) ‘$.SectionName’, SectionOrder NVARCHAR(MAX) ‘$.SectionOrder’, Renders NVARCHAR(MAX) ‘$.Renders’ AS JSON ) CROSS … Read more
Could you check compatibility level on database? OPENJSON is available under compatibility level 130. Could you try to execute: ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 130 Also, if you are using JSON on Azure SQL Database, note that even new databases are created under 120 compatibility level so you should change it if you want … Read more
Thanks! The soultion we found is converting into XML first – SELECT JSON_QUERY(‘[‘ + STUFF(( SELECT ‘,’ + ‘”‘ + item_id + ‘”‘ FROM #temp FOR XML PATH(”)),1,1,”) + ‘]’ ) ids FOR JSON PATH , WITHOUT_ARRAY_WRAPPER
Using CROSS APPLY: declare @json nvarchar(max) set @json = ‘ [ { “IdProject”:”97A76363-095D-4FAB-940E-9ED2722DBC47″, “Name”:”Test Project”, “structures”:[ { “IdStructure”:”CB0466F9-662F-412B-956A-7D164B5D358F”, “IdProject”:”97A76363-095D-4FAB-940E-9ED2722DBC47″, “Name”:”Test Structure”, “BaseStructure”:”Base Structure”, “DatabaseSchema”:”dbo”, “properties”:[ { “IdProperty”:”618DC40B-4D04-4BF8-B1E6-12E13DDE86F4″, “IdStructure”:”CB0466F9-662F-412B-956A-7D164B5D358F”, “Name”:”Test Property 2″, “DataType”:1, “Precision”:0, “Scale”:0, “IsNullable”:false, “ObjectName”:”Test Object”, “DefaultType”:1, “DefaultValue”:”” }, { “IdProperty”:”FFF433EC-0BB5-41CD-8A71-B5F09B97C5FC”, “IdStructure”:”CB0466F9-662F-412B-956A-7D164B5D358F”, “Name”:”Test Property 1″, “DataType”:1, “Precision”:0, “Scale”:0, “IsNullable”:false, “ObjectName”:”Test Object”, “DefaultType”:1, “DefaultValue”:”” } … Read more