Assign Custom Sequence with T-SQL

Add number 20 between different group and continue the sequence.

Create TABLE test(SNo int identity(1,1),StudentID INT,RollNumber int
, Course CHAR(1))

INSERT INTO test (StudentID,Course)
VALUES (123,’A’),(124,’A’),(125,’A’)
,(126,’B’)
,(127,’B’),(128,’B’)
,(129,’C’)
,(130,’C’),(131,’C’)
,(132,’D’)
,(133,’D’),(134,’D’)

;with mycte as (
Select SNo,Course,
dense_rank() OVER(ORDER BY Course) dnk,
ROW_NUMBER() OVER(Partition by Course ORDER BY SNO) rn
,10*Ceiling(count(*)Over(Partition by Course)/10.) cnt
from test
)
,mycte2 as (
select * ,lag(cnt) Over(order by sno) cntlag
from mycte)

select SNo, Course
, Case when dnk=1 then rn else
sum(case when rn =1 then cntlag else 0 end)Over(Order by SNo)+rn +20*(dnk-1) -1 end RollNumber

from mycte2

–Or this

;with mycte as (
Select SNo,Course,
dense_rank() OVER(ORDER BY Course) dnk,
ROW_NUMBER() OVER(Partition by Course ORDER BY SNO) rn
,10*Ceiling(count(*)Over(Partition by Course)/10.) cnt
from test)

select SNo,Course,
case when dnk=1 then rn else
rn+20*(dnk-1)+Sum(Case when rn=1 then ISNULL(cnt2,0) else 0 end) Over(Order by sno) -1 end cnt22
from mycte m
outer apply( select top 1 cnt cnt2 from mycte where dnk<m.dnk order by dnk desc) d

order by sno

drop table test

https://forums.asp.net/t/2146815.aspx?SQL+Server+Query+for+Partitioning+Data

Advertisements

STRING_AGG,STRING_SPLIT to Order List Within a Column (SQL Server 2017)

  
 
  ---SQL Server 2017
Create TABLE test
	( id int,unOrderCol VARCHAR(200) 
	);

INSERT	Test(id,unOrderCol) 
VALUES (1,'4,8,5,7'),(2,'9,3,7'),(3,null)

 SELECT	id
 , STRING_AGG(value, ',') WITHIN GROUP ( ORDER BY CAST(value AS INT)) orderCol
 FROM	test  
Cross apply STRING_SPLIT(unOrderCol, ',')
Group by id  
 
drop table test


 

Get Six Digits Coupon Code (unique)

Create table test (id int, pinCode varchar(6))

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n between 1 and 100),
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 *, row_number() Over(partition by Pincode Order by n) rn
 from (Select Left(Convert(varchar(36),NEWID()),6) as pincode ) t
cross apply (select n from nums WHERE n<10000) d(n)

)


Select * 
into myRandomCodeTable
from mycte 
where rn=1
 
 Select * from   myRandomCodeTable


--drop table   myRandomCodeTable
drop table test 
 
 
 
 

Get Six Digits Coupon Code (4 letters and 2 numbers)

 
 --create  table myRandomTable (id int identity(1,1),
--c1 char(1),
--c2 char(1),
--c3 char(1),
--c4 char(1),
--c5 char(1),
--c6 char(1)
--,RandomCode char(6))
--go

--===== 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 between 1 and 100),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,singleMix as (
select  cast(n-1 as char(1) ) c from nums
where (n between 1 and 10 ) 
union all
select   Char(ascii('A')+n-1) c from nums
where (n between 1 and 26 )  
)

,singleMix5 as (Select top 1 c from singleMix ORDER BY NEWID())
,singleMix6 as (Select top 1 c from singleMix ORDER BY NEWID())

,singleMix1 as (Select top 1 c from singleMix ORDER BY NEWID())
 ,singleMix2 as (Select top 1 c from singleMix ORDER BY NEWID())
 ,singleMix3 as (Select top 1 c from singleMix ORDER BY NEWID())
 ,singleMix4 as (Select top 1 c from singleMix ORDER BY NEWID())
 
 ,mix2 as (
 Select 
 m1.c c1
 ,m2.c c2
 ,m3.c c3
 ,m4.c c4
 ,m5.c c5
 ,m6.c c6
  from singleMix1 m1,singleMix2 m2,singleMix3 m3,singleMix4 m4
 ,singleMix5 m5,singleMix6 m6)
 
  Insert into myRandomTable (  RandomCode )
 Select   concat(c1,c2,c3,c4,c5,c6) RandomCode from mix2
  WHERE   isnumeric(c1)
 +isnumeric(c2)
 +isnumeric(c3)
 +isnumeric(c4)
 +isnumeric(c5)
 +isnumeric(c6)=2

 go 50000



 select count(*) from myRandomTable  


 --drop table myRandomTable


  ;with mycte as (
 select *, row_number() Over(partition by RandomCode Order by RandomCode) rn  
 from myRandomTable
 )

 delete from mycte where rn>1

 select top 15000 randomCode    from myRandomTable
 
 
 
 

Custom Format Date–a Sample

  
 
  Select Cast(dateadd(day,n,DtMonday) as date) dt, DATENAME(WEEKDAY,dateadd(day,n,DtMonday)) WEEK_DAY 
,Format(dateadd(day,n,DtMonday),'MM/dd/yyyy (dddd)','en-US') theformatyouwant
from (
Select dateadd(week,datediff(week,0,getdate()),0) DtMonday ) t
cross apply(VALUES	(0),(1),(2),(3),(4),(5),(6)) d(n)
/*
dt	WEEK_DAY	theformatyouwant
2018-08-06	Monday	08/06/2018 (Monday)
2018-08-07	Tuesday	08/07/2018 (Tuesday)
2018-08-08	Wednesday	08/08/2018 (Wednesday)
2018-08-09	Thursday	08/09/2018 (Thursday)
2018-08-10	Friday	08/10/2018 (Friday)
2018-08-11	Saturday	08/11/2018 (Saturday)
2018-08-12	Sunday	08/12/2018 (Sunday)

*/


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/33297de1-64bb-4f23-ad28-4a35de1eac63/getting-the-date-of-7-days-based-on-getdate?forum=transactsql


Unpivot sys.assembly_modules System View with Collation Issue

The system view sys.assembly_modules has this structure:

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-assembly-modules-transact-sql?view=sql-server-2017

Column_name
object_id
assembly_id
assembly_class
assembly_method
null_on_null_input
execute_as_principal_id

We can check this view with sp_help:
USE master;
GO
EXEC sp_help ‘sys.assembly_modules’;
GO

/*
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
object_id int no 4 10 0 no (n/a) (n/a) NULL
assembly_id int no 4 10 0 no (n/a) (n/a) NULL
assembly_class nvarchar no 256 yes (n/a) (n/a) Latin1_General_BIN
assembly_method nvarchar no 256 yes (n/a) (n/a) Latin1_General_BIN
null_on_null_input bit no 1 yes (n/a) (n/a) NULL
execute_as_principal_id int no 4 10 0 yes (n/a) (n/a) NULL
*/
Both assembly_class and assembly_method have collation of Latin1_General_BIN, which is not a default collation for most servers I have seen.

The following unpivot code will generate error:

SELECT [Label],[Value]
FROM
(
SELECT
CAST([assembly_id] as sysname) AS [assembly_id],
[assembly_method]
FROM
[sys].[assembly_modules]
) AS [t]
UNPIVOT
(
[Value] FOR [Label] IN
(
[assembly_id],
[assembly_method]
)
) AS [v];

Msg 8167, Level 16, State 1, Line 19
The type of column “assembly_method” conflicts with the type of other columns specified in the UNPIVOT list.

But you can add the Latin1_General_BIN collation to the cast assembly_id column.

SELECT [Label],[Value]
FROM
(
SELECT
CAST([assembly_id] as sysname) Collate Latin1_General_BIN AS [assembly_id],
[assembly_method]
FROM
[sys].[assembly_modules]
) AS [t]
UNPIVOT
(
[Value] FOR [Label] IN
(
[assembly_id],
[assembly_method]
)
) AS [v];

But another unpivot syntax (CROSS APPLY )can avoid this error without explicit adding collation:

select Label,Value from (SELECT
Cast([assembly_id] as nvarchar(128)) [assembly_id],
[assembly_method]
FROM [master].[sys].[assembly_modules]) t
Cross apply (Values(‘assembly_id’ ,[assembly_id])
,(‘assembly_method’, [assembly_method]) ) d(Label,Value)

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e3745569-45f3-4f0d-8f23-cbbbc2030fec/unpivot-data-type-error?forum=transactsql

–******************* Convert_Implict in play for the result Collation

Declare @Sample Table(
ColA int
–varchar(10) Collate SQL_Latin1_General_CP1_CI_AS
, ColB varchar(10) Collate Latin1_General_Bin);
Insert @Sample(ColA, ColB) Values (‘1’, ‘x’);
Insert @Sample(ColA, ColB) Values (‘2’, ‘y’);

select Label,Value from (

SELECT
Cast (ColA as varchar(10) ) as ColA, ColB
FROM @Sample) t
Cross apply (Values(‘ColA’ ,[ColA])
,(‘ColB’, [ColB]) ) d(Label,Value);
I found some related information to this issue.

I checked the execution plan of this query, there is an implicit_convert for colA to varchar(10).

Based on Collation Precedence document

https://docs.microsoft.com/en-us/sql/t-sql/statements/collation-precedence-transact-sql?view=sql-server-2017

, the result column (value column after unpivot ) collation should be Latin1_General_Bin in this sample.

I put the result to another table and check the column collation of the column value and it is confirmed that this is the case.
The following table summarizes the rules.

Operand coercion label Explicit X Implicit X Coercible-default No-collation
Explicit Y Generates Error Result is Explicit Y Result is Explicit Y Result is Explicit Y
Implicit Y Result is Explicit X Result is No-collation Result is Implicit Y Result is No-collation
Coercible-default Result is Explicit X Result is Implicit X Result is Coercible-default Result is No-collation
No-collation Result is Explicit X Result is No-collation Result is No-collation Result is No-collation


Dynamic Pivot with CASE for Dates

  
 
 create table sick_codes_Test$ (
 EMP_ID int, EMP_SHORT_NAME varchar(100), SEG_CODE varchar(10), 
 NOM_DATE date)
 Insert into sick_codes_Test$ values 
 (1,'aaa','s','2018-07-17'),
  (1,'aaa','s','2018-07-18'),
   (1,'aaa','s','2018-07-19'),
    (1,'aaa','s','2018-07-20'),
	 (1,'aaa','s','2018-07-21'),
	  (1,'aaa','s','2018-07-22'),
	   (1,'aaa','s','2018-07-23')
	   ,(2,'bbb','s2','2018-07-22'),
	   (2,'bbb','s2','2018-07-23')
declare @ColumnHeaders NVARCHAR(4000) ;
declare  @sql NVARCHAR(4000);
declare @Param int=7

-- --===== 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), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
WHere n<= @Param ) ---Control the total columns list
,dates as (
Select n, DATEADD(day, -n+1, getdate() ) dt  
from(Select n from nums) D(n)
)
  
Select @ColumnHeaders = STUFF( (SELECT ',' + 'max (Case when NOM_DATE='+ quotename( Convert(char(8),dt,112),'''') +' then  SEG_CODE  else null end) as ' + Quotename(Convert(char(10),dt,120),'[')  + char(10)+char(13)
FROM  dates
Order by n
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  
 --print @ColumnHeaders

	  SET @sql = N'Select EMP_ID, EMP_SHORT_NAME, '
+ @ColumnHeaders 
+' from sick_codes_Test$
Group by EMP_ID, EMP_SHORT_NAME ' 

--print @sql
 EXEC sp_executesql @sql;
  

  drop table sick_codes_Test$


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1a77fecf-75a7-43ec-90fa-2b33888633c2/how-to-use-dynamic-dates-in-sql-pivot?forum=transactsql