Split and Dynamic Pivot with Case

  

create table test ( strCol varchar(2000))
insert into test values( '|Xyz,Type1,03/01/2018 10:00 PM,L1,P,-1|Abc,Type2,03/01/2018 10:00 PM,L1,A,1|Mno,NA,03/01/2018 10:00 PM,L2,P,-1') 
,( '|Xyz2,Type1,03/01/2018 10:00 PM,L1,P,-1|Abc2,Type2,03/01/2018 10:00 PM,L1,A,1|Mno2,NA,03/01/2018 10:00 PM,L2,P,-1') 
 
 
--===== 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)

--load number table
Select * into NumsTable
from Nums
 
;with mycte as (
select  strCol, n,  
substring(strCol, n, charindex('|', strCol + '|', n) - n)  splitData 
 
from test   
cross apply NumsTable
Where n <= len(strCol) AND substring('|' + strCol, n, 1) = '|'
)
--Load data into temp table 1
Select  IDENTITY(int, 1,1) id, * 
into mytemp1
from   mycte



;with mycte1 as (

select id, splitData, m.n
, row_number()Over(partition by id  Order by id,m.n) rn2
, substring(splitData, Nums.n, charindex(',', splitData + ',', Nums.n) - Nums.n)  splitData2 

from mytemp1 m  
cross apply NumsTable nums
Where Nums.n <= len(splitData) AND substring(',' + splitData, Nums.n, 1) = ','
)

--Load data into temp table 2
 Select *  into mytemp2
 from mycte1
 

 --dynamic pivot with CASE
 Declare @sql nvarchar(max);
Declare @ColumnHeaders VARCHAR(MAX) 
SET @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN rn2=' + cast(rn2 as varchar(5)) 
+ ' THEN splitData2 else null end ) as ' + quotename(rn2,'[')  + char(10)+char(13)
  FROM mytemp2
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
 --print @ColumnHeaders

 set @sql = N'SELECT ' + @ColumnHeaders + '  FROM   mytemp2  group by id ';
--print @sql
exec(@sql);
 
 --clean up
drop table  mytemp2, mytemp1,NumsTable
drop table test


--dynamic pivot query
 create table test ( strCol varchar(2000))
insert into test values( '|Xyz,Type1,03/01/2018 10:00 PM,L1,P,-1|Abc,Type2,03/01/2018 10:00 PM,L1,A,1|Mno,NA,03/01/2018 10:00 PM,L2,P,-1') 
,( '|Xyz2,Type1,03/01/2018 10:00 PM,L1,P,-1|Abc2,Type2,03/01/2018 10:00 PM,L1,A,1|Mno2,NA,03/01/2018 10:00 PM,L2,P,-1') 
 
 
--===== 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 * into NumsTable
from Nums
 
;with mycte as (
select  strCol, n,row_number()Over(  Order by strCol) rn1,  
substring(strCol, n, charindex('|', strCol + '|', n) - n)  splitRole 
 
from test   
cross apply NumsTable
Where n <= len(strCol) AND substring('|' + strCol, n, 1) = '|'
)

Select  IDENTITY(int, 1,1) id, * 
into mytemp1
from   mycte
;with mycte1 as (

select id, splitRole,rn1, m.n, row_number()Over(partition by id,rn1 Order by id,m.n) rn2, substring(splitRole, Nums.n, charindex(',', splitRole + ',', Nums.n) - Nums.n)  splitRole2 

from mytemp1 m  
cross apply NumsTable nums
Where Nums.n <= len(splitRole) AND substring(',' + splitRole, Nums.n, 1) = ','
)
 Select *  into mytemp2
 from mycte1
 

 --dynamic
 Declare @sql nvarchar(max);
 Declare @cols varchar(max);
  Declare @cols2 varchar(max);
 set @cols = STUFF((SELECT ',max(' + QUOTENAME(rn2)  +') as ' +QUOTENAME(rn2)
                    FROM mytemp2
                   group by  rn2
                    order by  rn2
                    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;
 
set @cols2 = STUFF((SELECT ',' + QUOTENAME(rn2)   
                    FROM mytemp2
                    group by rn2
                    order by rn2
                    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;
 
 set @sql = N'SELECT id,' + @cols + ' 
            FROM   mytemp2
            PIVOT (MAX(splitRole2) FOR rn2 IN (' + @cols2 + ')) p group by id ';
print @sql
exec(@sql);
 
  
 --select * from mytemp1
 --select * from mytemp2

drop table  mytemp2, mytemp1,NumsTable
drop table test

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8189de40-33f5-4814-a836-5911d435a99b/split-strings-into-a-table-without-loop-in-sql-server-2008r2?forum=transactsql

Advertisements

Export Stored Procedure Result with OPENQUERY

  
-- EXEC sp_serveroption 'myserver\Instancename', 'DATA ACCESS', TRUE

SELECT  *
INTO     mytmpTable
FROM    OPENQUERY("myserver\Instancename", 'EXEC mydbname.[dbo].[usp_name_here] 2') -- with parameter value 2
 
select * from mytmpTable

drop table mytmpTable


 

You can use INSERT INTO atable EXECUTE sp syntax to put result into an existing table.

  
 
if object_id('datatemp','U') is not null 
drop table datatemp

--create a temp table base Stored procedure resultset
create table datatemp (id int, col varchar(50))


---insert result from stored procedure
INSERT INTO  datatemp(id, col)
EXECUTE yourspname @parameter1

--clean up
Drop table datatemp

 

Transfer Table Schema From one to dbo

 

  

declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema varchar(1000)

set @oldschema = 'IIS APPPOOL\User1'
set @newschema = 'dbo'

while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)
begin
select @table = name from sys.tables 
where object_id in(select min(object_id) from sys.tables where schema_name(schema_id) = @oldschema)

set @sql = 'alter schema [' + @newschema + '] transfer [' + @oldschema + '].' + @table

exec(@sql)
end
 
 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc3ec5b6-282c-4b6f-8161-ebb754bffc49/change-schema-sql-2016?forum=transactsql

Original Script from:
https://www.sqlservercentral.com/Forums/654301/change-old-schema-name-into-new-schema-name


Split records, separate the overlapped dates

  
 DECLARE @TBL_TEMP TABLE(MBR_NBR VARCHAR(100),PLAN_ID VARCHAR(100),MBR_ELIG_EFFDTE DATE,MBR_ELIG_TRMDTE DATE)

INSERT @TBL_TEMP

SELECT '101162464','MCR000','2016-01-01','2017-12-31' UNION
SELECT '101162464','MCRMCD08','2016-08-01','2017-04-30' UNION

SELECT '14601938','MCRADV02','2016-01-01','2016-04-30' UNION
SELECT '14601938','MCRMCD02','2016-05-01','2017-04-30' UNION
SELECT '14601938','MCR000','2017-05-01','2017-12-31' 

 

;with mycte as (
SELECT MBR_NBR,
Case when n=1 then lead(PLAN_ID) Over(Partition by MBR_NBR  Order by dt ) else PLAN_ID end PLAN_ID,
dateadd(day,n,dt) MBR_ELIG_EFFDTE, n,n2,
dateadd(day,-lead(n2) Over(Partition  by MBR_NBR Order by dt ),lead(dt) Over(Partition by MBR_NBR Order by dt ))  MBR_ELIG_TRMDTE

 FROM @TBL_TEMP
cross apply (values(0,1,MBR_ELIG_EFFDTE),(1,0,MBR_ELIG_TRMDTE)  ) d(n,n2,dt)
)

Select  MBR_NBR,PLAN_ID, MBR_ELIG_EFFDTE,MBR_ELIG_TRMDTE 
--,Row_number() Over(Partition by MBR_NBR,MBR_ELIG_EFFDTE Order by MBR_ELIG_TRMDTE desc)  rn
--,n,n2
 from mycte 
 WHERE MBR_ELIG_TRMDTE is not null  and MBR_ELIG_EFFDTE < MBR_ELIG_TRMDTE
order by   MBR_NBR, MBR_ELIG_EFFDTE



-- expected resultset should like below
DECLARE @TBL_TEMP2 TABLE(MBR_NBR VARCHAR(100),PLAN_ID VARCHAR(100),MBR_ELIG_EFFDTE DATE,MBR_ELIG_TRMDTE DATE)

INSERT @TBL_TEMP2

SELECT '101162464','MCR000','2016-01-01','2016-07-31' UNION
SELECT '101162464','MCRMCD08','2016-08-01','2017-04-30' UNION
SELECT '101162464','MCR000','2017-05-01','2017-12-31' UNION

SELECT '14601938','MCRADV02','2016-01-01','2016-04-30' UNION
SELECT '14601938','MCRMCD02','2016-05-01','2017-04-30' UNION
SELECT '14601938','MCR000','2017-05-01','2017-12-31' 


SELECT * FROM @TBL_TEMP2 ORDER BY 1,3


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4897356c-a293-414e-9af6-672d7ab7e078/split-records-separate-the-overlapped-dates?forum=transactsql


Pivot With Case For Two Groups

  

CREATE TABLE mytable(
   YEAR INTEGER  NOT NULL  
  ,ID   INTEGER  NOT NULL
  ,NBR  INTEGER  NOT NULL
  ,PT   INTEGER  NOT NULL
  ,AMT  INTEGER  NOT NULL
);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2016,63,1,26,2000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2016,63,2,27,1000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2016,63,3,25,3000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2015,66,11,55,5000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2015,66,13,56,1000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2015,66,11,58,5000);
INSERT INTO mytable(YEAR,ID,NBR,PT,AMT) VALUES (2015,66,12,57,8000);
 

 Declare @sql as NVarchar(max)=null  
declare @ColumnHeaders NVARCHAR(MAX) 

;with mycte
as(
select *, row_number() Over(Partition by ID Order by PT) rn from mytable)

Select @ColumnHeaders = STUFF( (SELECT  ',' + 'MAX(CASE WHEN rn=' + Cast(rn as varchar(5))+ ' THEN PT else 0 end ) as ' + quotename('PT'+ Cast(rn as varchar(2)),'[')  + char(10)+char(13)
  FROM mycte 
  Group by rn
  Order by rn FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '') +
 ','+STUFF( (SELECT    ',' +'MAX(CASE WHEN rn=' + Cast(rn as varchar(5))+ ' THEN AMT else 0 end ) as ' + quotename('AMT'+ Cast(rn as varchar(2)),'[')  + char(10)+char(13)
 FROM mycte    Group by rn Order by rn  FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '') 
  +' ,SUM(AMT) as ''Total''';
 --print @ColumnHeaders
 
Set @sql  =' ;with mycte
as(
select *, row_number() Over(Partition by ID Order by PT) rn from mytable)
Select [Year],ID,'+   @ColumnHeaders + ' from mycte Group by [Year],ID ';
     
--print @sql
EXEC(@SQL)

 
 

drop table mytable

/*
Year	ID	PT1	PT2	PT3	PT4	AMT1	AMT2	AMT3	AMT4	Total
2016	63	25	26	27	0	3000	2000	1000	0	6000
2015	66	55	56	57	58	5000	1000	8000	5000	19000
*/

 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e41a7ac5-36e3-4772-b042-e45264c8e44d/sql-help?forum=transactsql


Date Range with Category

  

DECLARE @Activities TABLE
(
  Department varchar(100),
  Activity varchar(100), 
  StartDateCampus1 Date,
  StartDateCampus2 Date

)
declare @dt datetime='2017-07-25'

INSERT INTO @Activities values
('Physics','Enrolment',@dt - 180,@dt - 120),
('Physics','StartofClasses',@dt - 90,@dt - 60),
('Physics','LastClassDate',@dt - 15,@dt- 1)

 
declare @startdt1 datetime
declare @startdt2 datetime  

--get start dates for  both campus
;with mycteDt as (
Select  StartDateCampus1  dt from @Activities
UNION ALL
Select  StartDateCampus2 from @Activities)
 
Select @startdt1 = DATEADD(month, DATEDIFF(month,0,min(dt)),0) 
,@startdt2 = DATEADD(month, DATEDIFF(month,0,max(dt)),0) 
from mycteDt 

--****  create a Number table
;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)
  
 --Departments and Dates  
,myDateDepartments as
(
select Department, n as rn, dateadd(month,n-1,@startdt1) dt 
from Nums ,(Select Distinct Department From  @Activities) a
   
)

,mycte2 as (
Select m.Department, rn, a.Activity Activity1, b.Activity Activity2
,dt, a.StartDateCampus1 , b.StartDateCampus2 
from myDateDepartments m
LEFT JOIN    @Activities a 
ON DATEADD(month, DATEDIFF(month,0,a.StartDateCampus1),0)=m.dt  and m.Department=a.Department
LEFT JOIN    @Activities b 
ON DATEADD(month, DATEDIFF(month,0,b.StartDateCampus2),0)=m.dt and m.Department=b.Department
 
 WHERE m.dt<=@startdt2 --last date
 )

 Select Department, Stuff(Convert(varchar(11), m.dt,100),4,4,'-') as [Month],d1.Activity1 as [Activity(Campus 1)], d2.Activity2 as [Activity(Campus 2)]
 from mycte2 m
Outer apply (select top 1 Activity1 from mycte2 m2 
WHERE m2.rn<=m.rn and Activity1 is not null  
Order by rn DESC) d1(Activity1) 
Outer apply (select top 1 Activity2 from mycte2 m2 
WHERE m2.rn<=m.rn and Activity2 is not null  
Order by rn DESC) d2(Activity2) 
/*
Department	Month	Activity(Campus 1)	Activity(Campus 2)
Physics	Jan-2017	Enrolment	NULL
Physics	Feb-2017	Enrolment	NULL
Physics	Mar-2017	Enrolment	Enrolment
Physics	Apr-2017	StartofClasses	Enrolment
Physics	May-2017	StartofClasses	StartofClasses
Physics	Jun-2017	StartofClasses	StartofClasses
Physics	Jul-2017	LastClassDate	LastClassDate

*/
 


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/422f62fd-f775-4838-9cda-f5cb2ff792b5/need-to-split-data-as-per-dates?forum=transactsql


Island Group, Regroup Sample Query

  

CREATE TABLE #temp
(
Identifier varchar(20)NOT NULL
,CreatedDate DATETIME NOT NULL
,CompletedDate DATETIME NOT NULL
,SN_Type varchar(20) NOT NULL
,SN_Status varchar(20) NOT NULL
)
;
INSERT INTO #temp
VALUES('64074558792','20160729','20160805','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160729','20160805','Re-Activattion','PARTIALLY');
INSERT INTO #temp
VALUES('64074558792','20160809','20160809','Re-Activattion','PARTIALLY');
INSERT INTO #temp
VALUES('64074558792','20160810','20160810','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160812','20160812','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160811','20160811','Re-Activattion','COMP');
INSERT INTO #temp
VALUES('64074558792','20160811','20160813','Re-Activattion','N-CO');
 
--INSERT INTO #temp
--VALUES('64074558792','20160812','20160812','Re-Activattion','N-CO');
--INSERT INTO #temp
--VALUES('64074558792','20160911','20160911','Re-Activattion','COMP');
--INSERT INTO #temp
--VALUES('64074558792','20160912','20160913','Re-Activattion','N-CO');
--;

;WITH Src AS (
SELECT Identifier, CreatedDate, CompletedDate, SN_Type, SN_Status,
 ROW_NUMBER() OVER(PARTITION BY Identifier,SN_Status ORDER BY CreatedDate, CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END, CompletedDate) AS rn,
 ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY CreatedDate, CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END, CompletedDate) -
 ROW_NUMBER() OVER(PARTITION BY Identifier,CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END ORDER BY CreatedDate, CompletedDate) AS grp
 FROM #temp
 
)
,Grouped as (
select * , dense_rank()Over(Partition by Identifier,SN_Status  Order by grp) grp2
from Src)

Select Identifier,    
Case when SUM(CASE WHEN SN_Status = 'COMP' THEN 1 ELSE 0 END) >0 then 
CAST( DATEDIFF(day,min(CreatedDate) ,max(CreatedDate)  )  AS VARCHAR(25))
ELSE 'NOT COMPLETED' END AS RE_ACT_COMPLETION_TIME 

,Sum(CASE WHEN SN_Status = 'N-CO' THEN 1 ELSE 0 END) as [RE-AN NCO #]

,Sum(CASE WHEN SN_Status = 'PARTIALLY' THEN 1 ELSE 0 END) as [RE-AN PART#]
 
from Grouped
Group by Identifier, grp2 
order by min(CreatedDate)


drop table #temp


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/50edadfb-60d8-407c-b72e-d024e83fbf8e/rownumber-over-partition-assistance?forum=transactsql