SQL Server 2016 JSON PIVOT Sample

create table SourceTable (Header varchar(50)  , Value  varchar(50))
insert SourceTable (Header,Value) Values
('Top','A'),
('Top','B'),
('Middle','RT'),
('Middle','MS'),
('Middle','TQ'),
('Top','A'),
('Middle','MS'),
('Low','NX'),
('Low','C'),
('Ignore','X')

;with mycte as (
select Distinct Header, Value
from  SourceTable
Where Header<>'Ignore'
)

,mycte2 as (
SELECT Header,  (SELECT  Vals =  JSON_QUERY(
(
SELECT Value
FROM mycte t2 WHERE t2.Header = t1.Header
FOR JSON PATH )
)    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)   jsCol
FROM mycte t1
Group by Header)

,mycte3 as (
select Header,

Concat(JSON_VALUE (jsCol,'$.Vals[0].Value'),
','+JSON_VALUE (jsCol,'$.Vals[1].Value'),
','+JSON_VALUE (jsCol,'$.Vals[2].Value'),
','+JSON_VALUE (jsCol,'$.Vals[3].Value') )  CombinedValues

from mycte2
)

select Max(Case when Header ='Low' then CombinedValues else null end ) 'Low'
,Max(Case when Header ='Middle' then CombinedValues else null end ) 'Middle'
,Max(Case when Header ='Top' then CombinedValues else null end ) 'Top'
from mycte3

drop table SourceTable

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1020897b-a73c-4ece-ba80-f87d78320c8e/sql-2016-pivoting-or-similar-on-text-data?forum=transactsql

JSON Split and Remove Duplicate Values in T-SQL

declare @s as varchar(max) = ‘SAV-JFK/JFK-DXB/DXB-HYD’

;with mycte as (
SELECT [key], Value val
FROM OpenJson(‘[“‘+replace(replace(@s ,’-‘,'”,”‘),’/’,'”,”‘)+'”]’)
)

,mycte2 as (
Select [key],
row_number() Over(partition by [val] order by [key]) rn,
val
from mycte )

select STRING_AGG (Val,’,’) WITHIN GROUP (ORDER BY [key])
from mycte2
where rn=1

–SAV,JFK,DXB,HYD

Setting to Allow Long Result from SSMS

When you have a long text column data returned in your SSMS, you may see a cut off from SSMS. Your result is still there but not showing for IDE performance reason.

You can modify two settings to allow large amount data from SSMS result.

From SSMS

1. Tools>> Options>>Query Results>>SQL Server >>Result to Text>> change the maximum characters in each column from 256 to something big, for example 8192.
2. Query>>Query Options>>Result>>Text change maximum characters in each column from 245 (default) to 8192.

Split Delimited Column into Separate Columns with JSON

create table #test
(id VARCHAR(25))
insert into #test
values('CO.UKE')
,('CO.UKE.IF')
,('CO.UKE.IF.IFIC00')
,('CO.UKE.IF.IFIC00.IFI')
;with mycte as (
SELECT id, '["'+ REPLACE(id, '.', '","') + '"] ' jsCol
--["CO","UKE"]
FROM #test
)
Select id
,max(Case when [key]=0 then value else null end) LVL_1
,max(Case when [key]=1 then value else null end) LVL_2
,max(Case when [key]=2 then value else null end) LVL_3
,max(Case when [key]=3 then value else null end) LVL_4
,max(Case when [key]=4 then value else null end) LVL_5
,max(Case when [key]=5 then value else null end) LVL_6
FROM mycte
cross apply openjson(jsCol ) d
group by id


CREATE TABLE numberList
(id int, nums VARCHAR(250))
insert into numberList values(1,'1.2.3.4.5'),(2,'21.22.23.24'),(3,'10.12.3.7.5.2')
;with mycte as (
SELECT id, '['+ REPLACE(nums, '.', ',') + '] ' jsCol
--[1,2,3,4,5]
FROM numberList
)
Select id
,max(Case when [key]=0 then value else null end) LVL_1
,max(Case when [key]=1 then value else null end) LVL_2
,max(Case when [key]=2 then value else null end) LVL_3
,max(Case when [key]=3 then value else null end) LVL_4
,max(Case when [key]=4 then value else null end) LVL_5
FROM mycte
cross apply openjson(jsCol ) d
group by id
drop table numberList
drop table #test


create table #test
(id VARCHAR(25))



insert into #test
values('CO.UKE')
,('CO.UKE.IF')
,('CO.UKE.IF.IFIC00')
,('CO.UKE.IF.IFIC00.IFI')
;with mycte as (
SELECT id, '{"data":["'+ REPLACE(id, '.', '","') + '"]}' jsCol
--{"data":["CO","UKE"]}
FROM #test
)
Select
id
,LVL_1
,LVL_2
,LVL_3
,LVL_4
,LVL_5
FROM mycte
cross apply openjson(jsCol,'$')
WITH (
LVL_1 NVARCHAR(MAX) '$.data[0]',
LVL_2 NVARCHAR(MAX) '$.data[1]',
LVL_3 NVARCHAR(MAX) '$.data[2]',
LVL_4 NVARCHAR(MAX) '$.data[3]',
LVL_5 NVARCHAR(MAX) '$.data[4]'
) t
 

;with mycte as (
 
SELECT id, '{"data":["'+ REPLACE(id, '.', '","') + '"]}' jsCol
--{"data":["CO","UKE"]}
FROM #test 
 
)
 
Select
id    
,jSON_VALUE(jsCol,'$.data[0]') as LVL_1
,jSON_VALUE(jsCol,'$.data[1]') as LVL_2
,jSON_VALUE(jsCol,'$.data[2]') as LVL_3
,jSON_VALUE(jsCol,'$.data[3]') as LVL_4
,jSON_VALUE(jsCol,'$.data[4]') as LVL_5
 
  
 FROM mycte

;with mycte as (

SELECT id, ‘{“data”:[{“col”:”‘+ REPLACE(id, ‘.’, ‘”},{“col”:”‘) + ‘”}]}’ jsCol
–{“data”:[{“col”:”CO”},{“col”:”UKE”}]}
FROM #test

)

Select
id
,jSON_VALUE(jsCol,’$.data[0].col’) as A1
,jSON_VALUE(jsCol,’$.data[1].col’) as A2
,jSON_VALUE(jsCol,’$.data[2].col’) as A3
,jSON_VALUE(jsCol,’$.data[3].col’) as A4
,jSON_VALUE(jsCol,’$.data[4].col’) as A5
,jSON_VALUE(jsCol,’$.data[5].col’) as A6

FROM mycte

drop table #test

https://social.msdn.microsoft.com/Forums/en-US/04073c0a-db03-4e64-b76f-880e44f6eb55/extracting-text-after-multiple-character-in-string?forum=transactsql