jingyang-li

CREATE table #TBL
(DATE1 DATETIME
,ID INT
,Col1 Varchar(5)
,Col2 Varchar(5)
)
Insert into #TBL values
('12/1/22', 1001, 'abc', 'df')
,('12/5/22', 1001, 'abc', 'def')
,('12/5/22', 1002, 'dcb', 'ef')

jingyang-li

 create table #temp (ID varchar(50)
 ,DateTime_ dateTime)
    
 insert into #temp (ID,DateTime_) values ('1001','2022-01-01 06:01:24.000')
  ,('1002','2022-01-01 06:07:24.000')
 ,('1003','2022-01-01 06:15:24.000')
 , ('1004','2022-01-01 06:20:24.000')
 ,('1005','2022-01-01 06:39:24.000')
 , ('1006','2022-01-01 06:46:24.000')
 , ('1007','2022-01-01 16:59:24.000')

jingyang-li

Create table test(id INT, dates VARCHAR(200))
        
  INSERT INTO test (id, dates)
  VALUES (1001, '5,10,15,20,25')
  , (1002,'9,12,12,25,30')
        
 Declare @GivenMonth varchar(8)='JUN-2022'
  ;with mycte as (
  select id ,'['+ dates  +']' jsondates  
  from test)

jingyang-li

create table FruitsTest (theDate date,Apple int, Kiwi int, Grape int, Orange int, Banana int)
Insert into FruitsTest values 
('2022-06-10',2,2,4,3,1),
('2022-06-11',3,2,5,2,1)

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

jingyang-li

create table dbo.myTable
(
ItemName varchar(30),
Qty int, Rate int, [Time] int
)

  insert into myTable 
  values
  ( 'A1',5,0 ,null),
  ( 'A2',23,4 ,null),

jingyang-li

CREATE TABLE [dbo].[RentalInvoice](
 [InvoiceId] [int] IDENTITY(1,1) NOT NULL,
 [InvoiceNo] [varchar](15) NOT NULL,
 [InvoiceDate] [date] NOT NULL,
 [RentalId] [int] NOT NULL,
 [StartDate] [date] NOT NULL,
 [EndDate] [date] NOT NULL,
 CONSTRAINT [PK_RentalInvoice] PRIMARY KEY CLUSTERED
 (
 [InvoiceId] ASC

jingyang-li

create table callTable (DateCol date, Calls int)
 insert into callTable values
     
 ('1/1/2022',5),
 ('1/2/2022',7),
 ('1/3/2022',9),
 ('1/4/2022',4),
 ('1/5/2022',2),
 ('1/6/2022',5),
 ('1/7/2022',6),

jingyang-li

 --Yitzhak Khabinsky's solution
 --https://docs.microsoft.com/en-us/answers/questions/845428/need-sql-help-to-split-comma-separated-value-into.html
 create table #test
 (col VARCHAR(2000))
 insert into #test
 values('2292,0192,20211018,0778988158494,0000600,150,000000000000001')
 ,('2292,14642,20210923,630996500903,0000500,061,1')
 ,('2292,4164,20211021,4006592590505,0002180,150,000000000000001,EUR,21,0000000,501,1,21-01917')
      
    

jingyang-li

create table datatable (TrendType varchar(12), date date, sales int)
insert into datatable values ('Monthly','5/1/2021',1234),
 ('Monthly','3/1/2021',1234),
  ('Monthly','2/1/2022',1234),
    ('Monthly','5/1/2022',1234),
	('Monthly','8/1/2021',1234),
   ('Monthly','8/1/2022',1234),
    ('Monthly','12/1/2021',1234),
    ('Monthly','11/1/2022',1234)

jingyang-li

 /*

 Declare @JSON varchar(max)
 SELECT @JSON=BulkColumn
 FROM OPENROWSET (BULK 'C:\temp\datafile22Copy.JSON', SINGLE_CLOB) import
 --datafile22Copy.JSON content is the same in the following string

 */
declare @json varchar(max)='{ "metadata" : {"page" : ["LineOfBusiness","GeoSubMarket","TransportType","ContractType","Entity_MR","SourceType","Products","MRCTiers","DesignatedMarketArea"],"column" : ["ScenarioSource"],"row" : ["Period","LiabilityType","SalesChannel","Measures","ReptCustSegCd","SubscriptionDevices"]}
,"data" : [["","","","","","","TOTAL_ACT"],["01-25-22","TOTSIGCON","BRANDED","GROSSUPG","S604","VOICEDEV","1.0"],["01-25-22","TOTSIGCON","BRANDED","GROSSUPG","X013","VOICEDEV","22400.0"],["01-25-22","TOTSIGCON","BRANDED","GROSSUPG","X013","NONVOICEDEV","708.0"],["01-25-22","TOTSIGCON","BRANDED","GROSSUPG","BUABSBMM","VOICEDEV","1.0"]] }'