PIVOT a table with JSON in SQL Server(2017 or 2019)

  

 
---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://social.msdn.microsoft.com/Forums/sqlserver/en-US/d42591a9-2b0b-4943-8fe2-a6bb9fc08c5e/query-to-produce-a-table-with-7-columns-based-on-the-values-in-a-two-column-table?forum=transactsql

An Easier Way of Pivoting Data in SQL Server

https://stackoverflow.com/questions/49574006/sql-server-query-columns-to-json-object-with-group-by


Stop SQL Service Hang from SQL Server Configuration Manager

I 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

A 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:

Converting JSON data to XML using a T-SQL function

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

  



 ;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

  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/12cba14c-4c03-4066-8e55-59220415323e/parse-string-btw-dashes?forum=transactsql


Find Continuous Date Range

  
 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 

  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e29a38d2-3431-4fa7-96d5-31bfd68cc17e/find-a-streak-based-on-a-date?forum=transactsql#60864500-9e85-46ee-a550-f9dd8d0e8a51

https://jingyangli.wordpress.com/2016/04/29/find-date-range-gap-solutions/


How to convert coma separated string in to different columns

  


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


  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/09d4a064-4a74-4663-8028-36e11d331da3/how-to-convert-coma-separated-string-in-to-different-columns?forum=transactsql