Split delimited data into one row

  

  set statistics io on 
set statistics time on 

CREATE TABLE TEST_DSP_APPENDED_DATA04
    (ConstituentID int, city varchar(255), total int, RAFFLE_RFMLA varchar(255), customer_id int);
--Add values to table
INSERT INTO TEST_DSP_APPENDED_DATA04
    (ConstituentID, city, total, RAFFLE_RFMLA, customer_id)
VALUES
    (1, 'London', 1000, '5/4-5/£10.00-£11.00/78/90+', 101),
    (4, 'NewYork', 765, '4/21/£35/11/12+', 65),
    (7, 'Mexico', 34, '1-3/21-25/£30-£40/12/52', 431),
    (15 ,'Lisbon', 850, '1-8/15/12/14/55+', 102)
--Check (display) data in table 
SELECT * FROM [dbo].[TEST_DSP_APPENDED_DATA04]

---Query

 --===== 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, ConstituentID, city, total, RAFFLE_RFMLA, customer_id,
Substring(RAFFLE_RFMLA , n, charindex('/', RAFFLE_RFMLA  + '/', n) - n)  cols

into mytemp

FROM TEST_DSP_APPENDED_DATA04
Cross apply (Select n from nums) d(n) 
Where n <= len(RAFFLE_RFMLA) AND substring('/' + RAFFLE_RFMLA, n, 1) = '/'

 ;with mycte1 as (
  Select ConstituentID, city, total, RAFFLE_RFMLA, customer_id,Cols
  ,row_number() Over(Partition by ConstituentID Order by id ) rn  
  from mytemp
 )

 Select ConstituentID, city, total, RAFFLE_RFMLA, customer_id,
  Max(Case when rn=1 then cols End) RAFFLE_RFMLA_1 
 , Max(Case when rn=2 then cols End) RAFFLE_RFMLA_2 
 , Max(Case when rn=3 then cols End) RAFFLE_RFMLA_3 
 , Max(Case when rn=4 then cols End) RAFFLE_RFMLA_4 
 , Max(Case when rn=5 then cols End) RAFFLE_RFMLA_5 

FROM mycte1

Group by  ConstituentID, city, total, RAFFLE_RFMLA, customer_id

  --clean up
 drop table mytemp

 -- ;with shredded as
 --(
 --    select ConstituentID, city, total, RAFFLE_RFMLA, customer_id, t.*
 --    from [dbo].[TEST_DSP_APPENDED_DATA04] as data
 --    cross apply [dbo].[DelimitedSplit8K](data.RAFFLE_RFMLA,'/') as t
 --)
 --select 
 --    ConstituentID, city, total, RAFFLE_RFMLA, customer_id
 --   ,isnull(pvt.[1], '') as RAFFLE_RFMLA_1
 --   ,isnull(pvt.[2], '') as RAFFLE_RFMLA_2
 --   ,isnull(pvt.[3], '') as RAFFLE_RFMLA_3
 --   ,isnull(pvt.[4], '') as RAFFLE_RFMLA_4
 --   ,isnull(pvt.[5], '') as RAFFLE_RFMLA_5 
 --from shredded
 --pivot (max(Item) for ItemNumber in ([1],[2],[3],[4],[5])) pvt;

drop   TABLE TEST_DSP_APPENDED_DATA04

set statistics io off 
set statistics time off

 

https://social.msdn.microsoft.com/Forums/en-US/f9ad740a-9824-47e1-94c6-9165da467174/need-help-with-querry?forum=transactsql

Three ways to create number combinations

create table test(id int)
insert into test values (1), (2), (3), (4)

--Option 1 Cross join
select * from test t1, test  t2
where t1.id<>t2.id
Order by t1.id,t2.id

 --or
 select * from test t1 CROSS JOIN test  t2
where t1.id<>t2.id
 Order by t1.id,t2.id

--Option 2  cross apply
select * from test t1
cross apply test  t2
where t1.id<>t2.id
Order by t1.id,t2.id

--Option 3 inner join
SELECT *  FROM  test t1
    INNER JOIN  test t2
	  ON t1.id <>t2.id
 Order by t1.id,t2.id

drop table test

 

Split String to Separate Columns

  

  create table test (strCol varchar(20))
insert into test values('1-1141-0065-0111') 

--===== 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)

,mycte as (
select strCol, n, substring(Replace(strCol,'-',''), n, 1)  splitVal
from test
cross apply Nums
Where n <= len(Replace(strCol,'-',''))  

)
 Select *  into temp 
 from mycte

 Declare @sql nvarchar(max);
 Declare @cols varchar(max);

set @cols = STUFF((SELECT ',' + QUOTENAME(n)  
					FROM temp 
					group by n
					order by n
					FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;

 set @sql = N'SELECT ' + @cols + ' 
			FROM temp
			PIVOT (MAX(splitVal) FOR n IN (' + @cols + ')) p';

exec(@sql);

drop table temp 
drop table test

 

https://social.msdn.microsoft.com/Forums/en-US/9ee582ea-9ce2-4938-b727-5282cbbf5f87/string-manipulation?forum=transactsql&prof=required

Check Agent Job History

  
 
 select distinct
 j.name as 'JobName',
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100  )  ) as 'RunDurationSeconds'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
 ON j.job_id = h.job_id 
where   
msdb.dbo.agent_datetime(run_date, run_time) =Dateadd( hour, 4,Cast(Cast(getdate() as date) as datetime)) --4am
--BETWEEN '2016-05-02 04:00:00.000' and '2016-05-02 04:01:00.000'   
 
order by JobName