PIVOT a table with JSON in SQL Server(2017 or 2019)
Posted: February 28, 2020 Filed under: Uncategorized Leave a comment
---SQL Server 2017 or 2019
CREATE TABLE mytable(A varchar(10) NOT NULL, B varchar(10) NOT NULL)
INSERT mytable VALUES
(10, -1),(50, 10),(51, 10),(52, 10),
(11, -1),(60, 11),(61, 11),(62, 11),(63, 11),
(12, -1),(70, 12),(71, 12),(72, 12),(73, 12),(74, 12)
;with mycte as (
SELECT (
SELECT B, Vals = JSON_QUERY('["' + STRING_AGG(STRING_ESCAPE(A, 'json'), '","') + '"]')
FOR JSON PATH
) jsCol
FROM mytable
where B-1
GROUP BY B
)
Select
/*jsCol, */
JSON_VALUE(jsCol,'$[0].B') MN
,JSON_VALUE(jsCol,'$[0].Vals[0]') as S1
,JSON_VALUE(jsCol,'$[0].Vals[1]') as S2
,JSON_VALUE(jsCol,'$[0].Vals[2]') as S3
,JSON_VALUE(jsCol,'$[0].Vals[3]') as S4
,JSON_VALUE(jsCol,'$[0].Vals[4]') as S5
,JSON_VALUE(jsCol,'$[0].Vals[5]') as S6
from mycte
drop TABLE mytable
https://stackoverflow.com/questions/49574006/sql-server-query-columns-to-json-object-with-group-by
Stop SQL Service Hang from SQL Server Configuration Manager
Posted: February 24, 2020 Filed under: Uncategorized Leave a commentI try to stop a SQL Server service from SQL Server Configuration Manager but it hangs there.
I found a post from the internet to use SC QueryEx and taskkill to stop the service from a commandline windon through powershell.
Here are the steps:
Find the service name from From SQL Server Configuration Manager and from a Powershell window to execute SC QueryEx command to find the PID for the service and execute another command (with admin right) taskkill /PID thePIDNumber /f
An example:
SC QueryEx MSSQL$SQL2019MAIN
—get the PID number, in my case 6372 this time
Open another powershell window as admin to execute:
taskkill /PID 6372 /f
The service will be stopped immediately.
Parse json data to comma separated string in sql server
Posted: February 7, 2020 Filed under: Uncategorized Leave a commentA question from ASP.NET forum:
https://forums.asp.net/p/2163844/6293782.aspx?p=True&t=637166585154171541.
SQL Server 2016 or above:
DECLARE @json NVARCHAR(MAX)
SET @json =
N'[
{"countryCode":"IN","countryName":"India","postalCode":"400102","administrativeArea":"Maharashtra","subAdministrativeArea":"Mumbai Suburban","locality":"Mumbai","subLocality":"Andheri West","thoroughfare":"New Link Road","subThoroughfare":"2"} ]'
SELECT Concat_WS(', ',countryCode,countryName,postalCode,locality,
administrativeArea) Locations
FROM OPENJSON ( @json )
with (
countryCode varchar(10) '$.countryCode',
countryName varchar(10) '$.countryName',
postalCode varchar(10) '$.postalCode' ,
administrativeArea varchar(50) '$.administrativeArea' ,
locality varchar(50) '$.locality'
)
For prior SQL Server 2014 or earlier:
You may try to use a function to do the convert from json to xml. Here is a sample UDF written by DANIEL HUTMACHER.
Here is the link:
The query to use the UDF and extract data:
DECLARE @json NVARCHAR(MAX)
SET @json =
N'
{"countryCode":"IN","countryName":"India","postalCode":"400102","administrativeArea":"Maharashtra","subAdministrativeArea":"Mumbai Suburban","locality":"Mumbai","subLocality":"Andheri West","thoroughfare":"New Link Road","subThoroughfare":"2"}'
;with mycte as
(
SELECT dbo.fn_parse_json2xml(@json) myXML
)
select myxml,
S.a.value('(countryCode/text())[1]', 'varchar(15)')
as countryCode,
S.a.value('(countryName/text())[1]', 'varchar(15)')
as countryName,
S.a.value('(postalCode/text())[1]', 'varchar(15)')
as postalCode,
S.a.value('(administrativeArea/text())[1]', 'varchar(15)')
as administrativeArea
,S.a.value('(locality/text())[1]', 'varchar(15)')
as locality
FROM mycte
CROSS APPLY myXML.nodes('/.') S(a)
THE UDF copied from DANIEL HUTMACHER’s blog to support above code.
CREATE FUNCTION dbo.fn_parse_json2xml(
@json varchar(max)
)
RETURNS xml
AS
BEGIN;
DECLARE @output varchar(max), @key varchar(max), @value varchar(max),
@recursion_counter int, @offset int, @nested bit, @array bit,
@tab char(1)=CHAR(9), @cr char(1)=CHAR(13), @lf char(1)=CHAR(10);
--- Clean up the JSON syntax by removing line breaks and tabs and
--- trimming the results of leading and trailing spaces:
SET @json=LTRIM(RTRIM(
REPLACE(REPLACE(REPLACE(@json, @cr, ''), @lf, ''), @tab, '')));
--- Sanity check: If this is not valid JSON syntax, exit here.
IF (LEFT(@json, 1)!='{' OR RIGHT(@json, 1)!='}')
RETURN '';
--- Because the first and last characters will, by definition, be
--- curly brackets, we can remove them here, and trim the result.
SET @json=LTRIM(RTRIM(SUBSTRING(@json, 2, LEN(@json)-2)));
SELECT @output='';
WHILE (@json!='') BEGIN;
--- Look for the first key which should start with a quote.
IF (LEFT(@json, 1)!='"')
RETURN 'Expected quote (start of key name). Found "'+
LEFT(@json, 1)+'"';
--- .. and end with the next quote (that isn't escaped with
--- and backslash).
SET @key=SUBSTRING(@json, 2,
PATINDEX('%[^\\]"%', SUBSTRING(@json, 2, LEN(@json))+' "'));
--- Truncate @json with the length of the key.
SET @json=LTRIM(SUBSTRING(@json, LEN(@key)+3, LEN(@json)));
--- The next character should be a colon.
IF (LEFT(@json, 1)!=':')
RETURN 'Expected ":" after key name, found "'+
LEFT(@json, 1)+'"!';
--- Truncate @json to skip past the colon:
SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));
--- If the next character is an angle bracket, this is an array.
IF (LEFT(@json, 1)='[')
SELECT @array=1, @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));
IF (@array IS NULL) SET @array=0;
WHILE (@array IS NOT NULL) BEGIN;
SELECT @value=NULL, @nested=0;
--- The first character of the remainder of @json indicates
--- what type of value this is.
--- Set @value, depending on what type of value we're looking at:
---
--- 1. A new JSON object:
--- To be sent recursively back into the parser:
IF (@value IS NULL AND LEFT(@json, 1)='{') BEGIN;
SELECT @recursion_counter=1, @offset=1;
WHILE (@recursion_counter!=0 AND @offset<LEN(@json)) BEGIN;
SET @offset=@offset+
PATINDEX('%[{}]%', SUBSTRING(@json, @offset+1,
LEN(@json)));
SET @recursion_counter=@recursion_counter+
(CASE SUBSTRING(@json, @offset, 1)
WHEN '{' THEN 1
WHEN '}' THEN -1 END);
END;
SET @value=CAST(
dbo.fn_parse_json2xml(LEFT(@json, @offset))
AS varchar(max));
SET @json=SUBSTRING(@json, @offset+1, LEN(@json));
SET @nested=1;
END
--- 2a. Blank text (quoted)
IF (@value IS NULL AND LEFT(@json, 2)='""')
SELECT @value='', @json=LTRIM(SUBSTRING(@json, 3,
LEN(@json)));
--- 2b. Other text (quoted, but not blank)
IF (@value IS NULL AND LEFT(@json, 1)='"') BEGIN;
SET @value=SUBSTRING(@json, 2,
PATINDEX('%[^\\]"%',
SUBSTRING(@json, 2, LEN(@json))+' "'));
SET @json=LTRIM(
SUBSTRING(@json, LEN(@value)+3, LEN(@json)));
END;
--- 3. Blank (not quoted)
IF (@value IS NULL AND LEFT(@json, 1)=',')
SET @value='';
--- 4. Or unescaped numbers or text.
IF (@value IS NULL) BEGIN;
SET @value=LEFT(@json,
PATINDEX('%[,}]%', REPLACE(@json, ']', '}')+'}')-1);
SET @json=SUBSTRING(@json, LEN(@value)+1, LEN(@json));
END;
--- Append @key and @value to @output:
SET @output=@output+@lf+@cr+
REPLICATE(@tab, @@NESTLEVEL-1)+
''+
ISNULL(REPLACE(
REPLACE(@value, '\"', '"'), '\\', '\'), '')+
(CASE WHEN @nested=1
THEN @lf+@cr+REPLICATE(@tab, @@NESTLEVEL-1)
ELSE ''
END)+
'';
--- And again, error checks:
---
--- 1. If these are multiple values, the next character
--- should be a comma:
IF (@array=0 AND @json!='' AND LEFT(@json, 1)!=',')
RETURN @output+'Expected "," after value, found "'+
LEFT(@json, 1)+'"!';
--- 2. .. or, if this is an array, the next character
--- should be a comma or a closing angle bracket:
IF (@array=1 AND LEFT(@json, 1) NOT IN (',', ']'))
RETURN @output+'In array, expected "]" or "," after '+
'value, found "'+LEFT(@json, 1)+'"!';
--- If this is where the array is closed (i.e. if it's a
--- closing angle bracket)..
IF (@array=1 AND LEFT(@json, 1)=']') BEGIN;
SET @array=NULL;
SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));
--- After a closed array, there should be a comma:
IF (LEFT(@json, 1) NOT IN ('', ',')) BEGIN
RETURN 'Closed array, expected ","!';
END;
END;
SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)+1));
IF (@array=0) SET @array=NULL;
END;
END;
--- Return the output:
RETURN CAST(@output AS xml);
END;
Double Reverse with PARSENAME
Posted: February 4, 2020 Filed under: Uncategorized Leave a comment;with cte as (select 'ABC-1yr-All' as original_string, 'ABC' as parsed_first_column,'1yr' as parsed_second_column,'All' as parsed_third_column ,'' as parsed_forth_column union all select 'ABC-3yr-Partial-Delete','ABC','3yr','Partial','Delete' union all select 'YZ-3yr-Partial-Upload','YZ','3yr','Partial','Upload' union all select 'YZ-5yr-No-Convert', 'YZ','5yr','No','Convert' union all select 'YZ-3yr-All','YZ','3yr','All' ,'' ) select * ,reverse(parsename(reverse(replace(original_string,'-','.')),1)) c1 ,reverse(parsename(reverse(replace(original_string,'-','.')),2)) c2 ,reverse(parsename(reverse(replace(original_string,'-','.')),3)) c3 ,reverse(parsename(reverse(replace(original_string,'-','.')),4)) c4 from cte
Find Continuous Date Range
Posted: February 4, 2020 Filed under: Uncategorized Leave a comment
CREATE TABLE Events(
[User_Id] INT NOT NULL
,Date DATE NOT NULL
);
INSERT INTO Events([User_Id],Date) VALUES
(1,'1/1/2020')
,(1,'1/2/2020')
,(1,'1/3/2020')
,(2,'1/1/2020')
,(2,'1/2/2020')
,(2,'1/4/2020')
,(2,'1/5/2020')
,(2,'1/6/2020')
,(2,'1/7/2020')
,(2,'1/8/2020')
,(2,'1/9/2020')
,(2,'1/10/2020');
;WITH dates AS (
SELECT DISTINCT [date],User_Id
FROM dbo.[Events]
),
groups AS (
SELECT
ROW_NUMBER() OVER (ORDER BY [date],[user_id]) AS rn,
dateadd(day, -ROW_NUMBER() OVER (Partition by [user_id] ORDER BY [date]), [date]) AS grp,
[date],[USER_ID]
FROM dates
)
SELECT
COUNT(*) AS consecutiveDates,
MIN(date) AS minDate,
MAX(date) AS maxDate, User_Id
--into #Streak
FROM groups
GROUP BY grp,user_id
having COUNT(*)>=3
--select count(*) as Streak from #streak
--drop TABLE #Streak
drop TABLE Events
How to convert coma separated string in to different columns
Posted: February 3, 2020 Filed under: Uncategorized Leave a comment
create table test (Secondary_DX varchar(200))
insert into test values('E66.9, F43.10, F43.12'),
('F31.81, F33.2, F43.10, Z59.9, Z65.8, Z86.59'),
('F10.10, F10.20, F10.21, F32.9, F33.1, F41.1, Z63.0'),
('E66.9, F32.0, F43.20, F43.21, Z63.0, Z63.32')
--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
SELECT IDENTITY(int, 1,1) id, Secondary_DX,
Substring(Secondary_DX , n, charindex(',', Secondary_DX + ',', n) - n) cols
into mytemp
FROM TEST
Cross apply (select n from nums) d(n)
Where n <= len(Secondary_DX) AND substring(',' + Secondary_DX, n, 1) = ','
;with mycte1 as (
Select Secondary_DX,Cols
,row_number() Over(Partition by Secondary_DX Order by id ) rn
from mytemp
)
Select Secondary_DX,
Max(Case when rn=1 then cols End) Secondary_DX_1
, Max(Case when rn=2 then cols End) Secondary_DX_2
, Max(Case when rn=3 then cols End) Secondary_DX_3
, Max(Case when rn=4 then cols End) Secondary_DX_4
, Max(Case when rn=5 then cols End) Secondary_DX_5
, Max(Case when rn=6 then cols End) Secondary_DX_6
, Max(Case when rn=7 then cols End) Secondary_DX_7
, Max(Case when rn=8 then cols End) Secondary_DX_8
, Max(Case when rn=9 then cols End) Secondary_DX_9
, Max(Case when rn=10 then cols End) Secondary_DX_10
FROM mycte1
Group by Secondary_DX
--clean up
drop table mytemp
--use XML
;WITH mycte AS
(
SELECT Secondary_DX,Cast(N'' +
REPLACE(Secondary_DX, ',', '')+ '' as XML) AS vals
FROM test
)
SELECT Secondary_DX
, vals.value('(/H/r)[1]','VARCHAR(30)') AS Secondary_DX1
, vals.value('(/H/r)[2]','VARCHAR(30)') AS Secondary_DX2
, vals.value('(/H/r)[3]','VARCHAR(30)') AS Secondary_DX3
, vals.value('(/H/r)[4]','VARCHAR(30)') AS Secondary_DX4
, vals.value('(/H/r)[5]','VARCHAR(30)') AS Secondary_DX5
, vals.value('(/H/r)[6]','VARCHAR(30)') AS Secondary_DX6
, vals.value('(/H/r)[7]','VARCHAR(30)') AS Secondary_DX7
, vals.value('(/H/r)[8]','VARCHAR(30)') AS Secondary_DX8
, vals.value('(/H/r)[9]','VARCHAR(30)') AS Secondary_DX9
, vals.value('(/H/r)[10]','VARCHAR(30)') AS Secondary_DX10
From mycte
---SQL Server 2016 or up
select Secondary_DXJSONArray
,JSON_VALUE(value, '$.csvdata[0]') Secondary_DX0
,JSON_VALUE(value, '$.csvdata[1]') Secondary_DX1
,JSON_VALUE(value, '$.csvdata[2]') Secondary_DX2
,JSON_VALUE(value, '$.csvdata[3]') Secondary_DX3
,JSON_VALUE(value, '$.csvdata[4]') Secondary_DX4
,JSON_VALUE(value, '$.csvdata[5]') Secondary_DX5
,JSON_VALUE(value, '$.csvdata[6]') Secondary_DX6
,JSON_VALUE(value, '$.csvdata[7]') Secondary_DX7
,JSON_VALUE(value, '$.csvdata[8]') Secondary_DX8
,JSON_VALUE(value, '$.csvdata[9]') Secondary_DX9
from (
Select Secondary_DX
,'[{"csvdata":["'+ REPLACE(Secondary_DX, ', ', '","') + '"]} ] ' as Secondary_DXJSONArray
FROM test
) t
cross apply openjson(Secondary_DXJSONArray) d
drop table test
Jingyang Li 