Split, Concat with JSON or Other Traditional Way

  

   CREATE TABLE ##Temp
(
   ClassID nvarchar(50), 
   Names Nvarchar(250), 
   Course Nvarchar(250),

);

INSERT into ##Temp (ClassID, Names, Course ) values
 ('IT-01-2001', 'Cliff Musi,Cliff Musi,Cliff Musi,Mary Raj,Mary Raj', 'Budget Execution'),
('IT-01-2002', 'Val Richard,Val Richard,Val Richard,Raj Rose,Vick Shell,Raj Rose', 'Budget Execution Guide')




 --pre sql sql 2016
 

 ;with mycte as (
select * ,  
CAST (N'' +  REPLACE(names,  ',', '')+ '' AS XML) vals
from ##Temp
  )
, mycte1 as
 ( 
 SELECT  distinct ClassID, Course,  
 S.a.value('.', 'VARCHAR(100)') AS splitVal
 FROM mycte d
 CROSS APPLY d.[vals].nodes('/H/r') S(a)
 )
 

 SELECT t1.ClassID,t1.Course,
       Stuff(( SELECT ',' +  t2.splitVal 
           FROM mycte1 t2
          WHERE t2.ClassID = t1.ClassID and  t2.Course = t1.Course
         
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Names
  FROM mycte1 t1
 GROUP BY  t1.ClassID,t1.Course;

---SQL server 2016 or up with JSON

 ;with mycte as (select distinct ClassID, Course  ,   s.value AS myValue
from ##Temp
cross apply openjson(('["'+ REPLACE(Names, ',', '","') + '"]')) s
)

select 
distinct m2.ClassID,  m2.Course ,
  Replace(Replace(Replace(Replace(Replace(((Select myValue from mycte m1 where m1.ClassID=m2.ClassID
for json path)),'myValue',''),'[{"',''),'":"',''),'"},{"',','),'"}]','') names
 
from mycte m2

 



drop TABLE ##Temp

  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dd4f0c26-00a8-43e2-bbca-f537953921f5/query-help-to-show-unique-values-in-the-column?forum=transactsql

Split String list with JSON to Return Item In Order

The String_split function is a handy new feature since SQL Server 2016. But if you want to track the order of the split list, you may not have a definite order according to MSDN document for the function.
In that case, you can use the new JSON support for SQL Server 2016 to split the list with index to track the order.
See some sample code for both number list and literal list with comma separator.

DECLARE @stringNum NVARCHAR(MAX) = '19, 65, 6, 16, 1, 11, 7, 11,66';
--prepare json string
Set @stringNum='['+@stringNum+']'
SELECT s.[key] AS mykey, s.value AS myValue
FROM OPENJSON(@stringNum) s
ORDER BY s.[key]

DECLARE @string NVARCHAR(MAX)  = 'Liam,Noah,William (Bill),James,Logan,Benjamin,Mason,Elijah,Oliver,Jacob';
--prepare json string
Set @string='["'+ REPLACE(@string, ',', '","') + '"]'
SELECT s.[key] AS rn, s.value AS myValue
FROM  OPENJSON(@string)  s
Order by s.[key]

Modify JSON Array Value with Double Quotes

  



  DECLARE @vJson AS NVARCHAR(4000) 
 = N'{"series":[{"name":"A","data":"[0,12,21]"},{"name":"B","data":"[0,1,2]"},{"name":"C","data":"[0,2,0]"}]}'
 
SELECT  JSON_Value (c.value,'$.name')  name
,JSON_QUERY(replace(replace(replace(JSON_Value (c.value, '$.data'),'[','["'),',','","'),']','"]')  ) as data 
FROM OPENJSON(@vJson,'$.series') c
For JSON PATH, root('series')
/*
{"series":[{"name":"A","data":["0","12","21"]},{"name":"B","data":["0","1","2"]},{"name":"C","data":["0","2","0"]}]}
*/


  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3bf6c2a8-f80e-483a-8f85-4f0a7ec90ce6/modify-json-array?forum=transactsql

A helpful blog from Thomas Harlan

A helpful blog from Thomas Harlan.

https://blog.iatric.com/report-writing-blog/temp-tables-with-ssis

Sum Datetime Value

  

 create table test(total_hours_worked datetime)
insert into test values 
 ('02:09:33')
,('01:30:00')
,('03:35:00')
select  
 CAST(cast(sum((cast(total_hours_worked as float(53)))) as datetime ) as time(0)) [hh:mm:ss] --precision 15 digits 25-53
--,cast(sum(cast(total_hours_worked as float(24))) as datetime)   --precision 7 digits 1-24 -- or real
from test

--total hours is under 24 hours

drop table test

  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e40f34b2-6a7d-494a-913a-2a593f114003/sum-a-datetime-field?forum=transactsql

https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-ver15

A sample: Unpivot with JSON in SQL Server

  


create table ##tempMSRCache (CompanyName varchar(50)
, Standard_A_Status varchar(50)
,Standard_A_Solution varchar(50)
, Standard_B_Status varchar(50)
, Standard_B_Solution varchar(50)
)
 
Insert into ##tempMSRCache values
('CompanyA','in spec','has backups','out of spec','pop mail'),
('CompanyB ','in spec','has backups','inspec','pop mail') 
 
 ;with mycte   (TheKey, TheValue) as (
SELECT [KEY], Value from OpenJson((
select * from ##tempMSRCache  FOR JSON AUTO ))
WHERE type = 5)
 
  
 SELECT
 --columns not in unpivot list
 JSON_VALUE(src.TheValue,'$.CompanyName') CompanyName
--JSON_VALUE(src.TheValue,'$.emp_name') emp_name
-- ,src.TheKey +1 as colOrder, 
 ,unpvt.[Key], unpvt.Value 
 FROM mycte AS src
 CROSS APPLY OpenJson(src.TheValue) AS unpvt  
 WHERE unpvt.Type!=5  and
 --columns not in unpivot list
 unpvt.[Key] not in ('CompanyName')
  
 
 Select * from ##tempMSRCache 
 
drop table ##tempMSRCache

--https://jingyangli.wordpress.com/2016/03/15/dynamic-unpivot-gloabal-temp-table-t-sql/

  

Unpivot with JSON in SQL Server

  

 --SQL Server 2016, 2017,2019 
create table test (emp_id int
,emp_name varchar(30) 
,[17-Sep] decimal(6,2) 
,[17-Oct] decimal(6,2) 
,[17-Nov] decimal(6,2) 
,[17-Dec] decimal(6,2)
,[18-Jan] decimal(6,2) 
,[18-Feb] decimal(6,2) 
,[18-Mar] decimal(6,2) 
,[18-Apr] decimal(6,2) 
,[18-May] decimal(6,2) 
,[18-Jun] decimal(6,2)
,IsActive bit,note varchar(max))
 
 Insert into test 
 values(597,'Bill', 1,2,3,84.01, 0,0,0,95.13,0,0,0,'Regular')
 ,(999,'Joe', 9,8,6,54.01,0,0,0, 95.13,null,0,1,'VIP')

;with mycte   (TheKey, TheValue) as (
SELECT [KEY], Value from OpenJson((
select * from test  FOR JSON AUTO ))
WHERE type = 5)

 
 SELECT
 --columns not in unpivot list
 JSON_VALUE(src.TheValue,'$.emp_id') empid
,JSON_VALUE(src.TheValue,'$.emp_name') emp_name
-- ,src.TheKey +1 as colOrder, 
 ,unpvt.[Key], unpvt.Value 
 FROM mycte AS src
 CROSS APPLY OpenJson(src.TheValue) AS unpvt  
 WHERE unpvt.Type !=5  and 
 --columns not in unpivot list
 unpvt.[Key] not in ('emp_id','emp_name')
 
drop table test 

  

Transpose Matrix with JSON in SQL Server

There is an article to introduce an easy way to use JSON to transpose a matrix with TSQL.(SQL Server 2016 or above)
The article posted a Generic TransposedJSONMatrix Function to demonstrate how to transpose matrix with JSON.

An Easier Way of Pivoting Data in SQL Server