Create Format File For bcp (T-SQL)

We can use bcp command to generate a format file from an existing table.

  exec master..xp_cmdshell  'bcp myDB..myTable format nul -T -n -f c:\temp\myformatfile.fmt -S myMachine\myDBInstance';

  --http://technet.microsoft.com/en-us/library/ms191516.aspx
  --http://technet.microsoft.com/en-us/library/ms191479(v=sql.110).aspx


--Sample usage of the format file to retrieve a text file using this format file

SELECT 
* 
--or column list from format file
FROM  OPENROWSET(BULK N'C:\temp\mytextfile.txt',  FORMATFILE='C:\temp\myformatfile.fmt') AS t 


 

Running Total (deduct value) (T-SQL)

DECLARE @Products TABLE (ProductID INT, ProductName VARCHAR(200), Price int)
 
INSERT INTO @Products(ProductID, ProductName,Price)
VALUES ( 1, 'A', 1000),(2, 'B',500),(3, 'C', 200),(4, 'E', 300)
 

 ;with mycte1 as (
select ProductID, ProductName, row_number() Over(Order by ProductID)  rn,Price
 
from @Products)
 
, mycte2 AS
(
 SELECT ProductID, ProductName, rn, Price
 FROM mycte1
 WHERE rn = 1
 UNION ALL
 SELECT n.ProductID, n.ProductName, n.rn,  (m.Price - n.Price) as price 
 FROM mycte2 m INNER JOIN mycte1 n
 ON n.rn = m.rn + 1
)
SELECT ProductID, ProductName,Price FROM mycte2
 ORDER BY ProductID
 
--OPTION (MAXRECURSION 0)
;



 

Find Capital Letter Position with PatIndex (T-SQL)

--1.Find first Capital letter position:
SELECT patindex('%[A-Z]%'  ,'where is the Capital Letter'  COLLATE Latin1_General_BIN)

--Or use different collation:
SELECT patindex('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%','where is the Capital Letter'  COLLATE SQL_Latin1_General_CP1_CS_AS)


--2.Find all capital letter position
-- We can use  either  collation: Latin1_General_BIN or SQL_Latin1_General_CP1_CS_AS to change case sensitive level

;with mycte as (
Select 65 as i, char(65) as Capitalletter
Union all
Select i+1 i, char(i+1) from mycte
WHERE i<90)

SELECT Capitalletter, patindex('%'+Capitalletter +'%','where is the Capital Letter' COLLATE SQL_Latin1_General_CP1_CS_AS)
FROM mycte 
/*
Capitalletter	pos
A	0
B	0
C	14
D	0
E	0
F	0
G	0
H	0
I	0
J	0
K	0
L	22
M	0
N	0
O	0
P	0
Q	0
R	0
S	0
T	0
U	0
V	0
W	0
X	0
Y	0
Z	0

*/


--http://www.techonthenet.com/ascii/chart.php
--http://technet.microsoft.com/en-us/library/ms187323.aspx



 

Combine Columns to Row (Two columns) –XML Path

--DDLs for sample table and data
Create table Employee(ID int,Department int,Name Varchar(50) )
Insert into Employee values (1,1,'j.Doe')

Create table Executive(ID int,Department int,Name Varchar(50) )
Insert into Executive values (1,1,'Doe')
Insert into Executive values (2,1,'David')
 
Create table Supervisor(ID int,Department int,Name Varchar(50) )
Insert into Supervisor values (1,1,'Doe')
Insert into Supervisor values (2,1,'Jane') 

Create table Departments(ID int,DepartName Varchar(50) )
insert into Departments values(1,'Marketing')


;with mycte as (
Select em.ID,em.Name as EmName,d.DepartName, ex.Name as ExName,s.Name as SuperName 
FROM Employee em 
INNER JOIN Executive  ex on em.Department=ex.Department 
INNER JOIN Supervisor s On s.Department=em.Department 
INNER JOIN Departments d on em.Department=d.ID
)


SELECT Distinct ID, EmName,DepartName
,SupervisorNames = STUFF((
SELECT distinct ',' + SuperName
 FROM mycte m
 WHERE m.ID = m1.ID AND m.EmName = m1.EmName AND m.DepartName = m1.DepartName

 FOR XML PATH('')), 1, 1, '')


, ExecutiveNames = STUFF((
SELECT distinct ',' + ExName
 FROM mycte m
 WHERE m.ID = m1.ID AND m.EmName = m1.EmName AND m.DepartName = m1.DepartName

 FOR XML PATH('')), 1, 1, '') 
 FROM mycte m1


--Clean up
drop table Employee,Executive,Supervisor,Departments
 
 
 


 

Gap Island Problems Solutions

CREATE TABLE [dbo].[test](
	[id] [int] NULL,
	[state] [varchar](50) NULL,
	[from_date] [datetime] NULL
) ON [PRIMARY]

GO
 
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (1, N'Initiated', CAST(0x0000A2D8002C7EA0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (2, N'Initiated', CAST(0x0000A2D8004D7420 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (3, N'Processing', CAST(0x0000A2D8006E69A0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (4, N'Processing', CAST(0x0000A2D8007E57C0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (5, N'Processing', CAST(0x0000A2D800827670 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (6, N'completed', CAST(0x0000A2D8008F5F20 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (7, N'completed', CAST(0x0000A2D8009191A0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (8, N'Initiated', CAST(0x0000A2D8009FD9E0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (9, N'Initiated', CAST(0x0000A2D800B054A0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (10, N'Processing', CAST(0x0000A2D800C0CF60 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (11, N'Processing', CAST(0x0000A2D800C4A7C0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (12, N'completed', CAST(0x0000A2D800C88020 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (13, N'completed', CAST(0x0000A2D800CDFE60 AS DateTime))
GO


--Option 1

;with mycte as(
select  id, state, from_date
, row_number() Over(  Order by id) - row_number() Over(Partition By state Order by id) rndelta  
from  test)

,mycte1 as (Select  id, state, from_date
, row_number() Over(Partition By state,rndelta Order by from_date DESC) rn 
from mycte
)
Select id, state, from_date from mycte1
WHERE rn=1
order by id


--Option 2

SELECT id,state,from_date, seq, Next
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY COALESCE(Next,-1) ORDER BY from_date DESC) AS Seq
FROM test t
OUTER APPLY (SELECT MIN(id) AS next
             FROM test
             WHERE id > t.id
             AND state <> t.state
             )t1
)t
WHERE Seq = 1
order by id



--Option 3

;with cte
as
(
	select *,1 as flag from test
	where id=1
	UNION ALL
	select t.*,CASE WHEN t.state=c.state then c.flag else c.flag+1 end as flag
	from test t
	inner join cte c on t.id=c.id+1
)
select id,state,from_Date
from
(
	select id,state,from_Date,row_number() over(partition by flag order by from_Date desc) rnum
	from cte
) tt
where rnum=1



--http://social.msdn.microsoft.com/Forums/en-US/a3426959-5c00-4449-8f5c-ef7963a326ca/how-to-retrieve-the-max-date-part-value-of-the-continuous-duplicate-record-in-table?forum=transactsql


 

It seems the recursive solution is the winner from checking the execution plan.

Dynamic Pivot With Parameter–(T-SQL)

If object_id('table1','U') is not null
drop table table1
 
create table table1 (PID int, Pname varchar(50), PartId int)
insert into table1 Values(0,'Length',1), (1,'Breadth',1),(2,'Height',1),(0,'Area',2),(1,'Volume',2)     

If object_id('table2','U') is not null
drop table table2

create table table2 (SampleID  int , PID int, Pvalue varchar(10), PartId int)
insert into table2  values (0,0,'10',1), (0,1,'10',1), (0,2,'fail',1), (1,0,'20',1),(1,1,'fail',1),(1,2,'fail',1), (0,0,'10',2), (0,1,'10',2)


--Assign your partid value here
 Declare @partId int=1

--SET @partId =2

 
DECLARE @col AS NVARCHAR(max)=''
DECLARE @sql AS NVARCHAR(max)
 
SELECT @col=stuff( (SELECT ',['+ PName  +']'
FROM (Select distinct top 100 Percent  PName, t2.Pid from table2 t2 
inner join table1 t1 on t1.PID =t2.PID and t1.PartId=t2.PartId  Where t2.PartId=@PartId order by t2.Pid) p2
 
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;
 
--print @col
 
SET @sql='SELECT [SampleID], ' + @col
+ ' FROM (
Select  SampleId, PName, PValue from table2 t2 
inner join table1 t1 on t1.PID =t2.PID and t1.PartId=t2.PartId  
Where t2.PartId='+ Cast(@PartId as Nvarchar(50)) +' ) src
PIVOT (MAX(PValue) FOR [PName] IN ('+ @col + ')) AS pvt'

--print @sql
--EXEC(@sql)
EXEC sp_executesql @sql

 

Dynamic Column List –T-SQL

Create table test (Column1 INT,Column2 NVARCHAR(10),Column3 NVARCHAR(10),Column4 NVARCHAR(10))
INSERT INTO test values( 1,'A',NULL,'B'),(2,'X','Y','Z'),(3,'P','Q',null),(4,null,'W','U') 

 

DECLARE @sql AS NVARCHAR(4000)=''
Declare @tablename nvarchar(50) ='test'
  
 
SELECT  @sql= 'SELECT ' +(SELECT stuff( (SELECT '+'+ 'ISNULL('''+ COLUMN_NAME +'|''+ CAST(' + QuoteName(COLUMN_NAME )  +' as Varchar(50)),'''')'  
FROM  INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME =@tablename 
AND TABLE_SCHEMA ='dbo'  
Order By COLUMN_NAME FOR XML PATH('')),1,1,'')) 
+ ' FROM '+ @tablename
 
--print @sql
EXEC sp_executesql @sql
 

DROP TABLE test

 

Unpivot and Pivot Sample Code

Create table #test (id INT,Column1 NVARCHAR(10),Column2 NVARCHAR(10),Column3 NVARCHAR(10))
INSERT INTO #test values( 1,'A',NULL,'B'),(2,'X','Y','Z'),(3,'P','Q',null),(4,null,'W','U') 


Select id, ISNULL('Column1'+'|'+[Column1],'') + ISNULL('Column2'+'|'+[Column2],'') + ISNULL('Column3'+'|'+[Column3],'')  as ValueData 
FROM (
Select id,col,val from #test
Cross Apply (Values(Column1,'Column1'),(Column2,'Column2'),(Column3,'Column3')) d(val,col)
WHere Val Is not null
) src
Pivot (max(val) For col In ([Column1],[Column2],[Column3])) pvt

--The cleanup can be removed when implemented inside a SP
IF OBJECT_ID('tempdb..#test') IS NOT NULL 
DROP TABLE #test



 

Return Affected Rows with Multiple Statements (Deletes)

ALTER procedure [dbo].[mysp_Option1_Deletes]
@id1 int,@id2 int,
@totalRow int Output
as
Begin
SET NOCOUNT ON;

 
Declare @cnt1 int=0,@cnt2 int=0

Delete from mytable1
Where id=@id1

Set @cnt1=@@Rowcount

Delete from mytable1
Where id=@id2

Set @cnt2=@@Rowcount

Set @totalRow=@cnt1+@cnt2

End



ALTER procedure [dbo].[mysp_Option2_Deletes]
@id1 int,@id2 int,
@totalRow int Output
as
Begin
SET NOCOUNT ON;

DECLARE @MyTableVar table (myid int identity(1,1), id int); 
Declare @cnt1 int=0,@cnt2 int=0

Delete from mytable1

OUTPUT DELETED.id 
INTO @MyTableVar

Where id=@id1

 

Delete from mytable1
OUTPUT DELETED.id 
INTO @MyTableVar
 
Where id=@id2

 

SELECT @totalRow=ISNULL((SELECT count(*) FROM   @MyTableVar),0)

End

 

Recursive Sample for Employee Structure (T-SQL)

Create Table Test(empid  int primary key,  empName varchar(50),  hr_id int);
Insert into Test  Values
(16,'John',12),(15,'Joe',13),(13,'Chad',12),(12,'David',11)
,(11,'Adam',null),(6,'Jean',5),(5,'Paige',4)
,(4,'Steven',3),(3,'Dan',2),(2,'Ryan',null),(10,'Ruth', 9)
,(9,'Greg',8),(8, 'Sam',7),(7,'Pane',1),(1,'Brandon', null);

;With mycte AS
(Select empid, empName, Cast('' AS varchar(max)) AS path,  empid As ManagerEmpId,  empid AS Seq
From Test Where hr_id Is Null 
Union All
Select t.empid, t.empName, Cast(t.hr_id AS varchar(max)) + Case When m.path <> '' Then ','+ m.path Else '' End AS path, m.ManagerEmpId, m.Seq + t.empid AS Seq 
From mycte m
Inner Join Test t On t.hr_id = m.empid)

Select  * From mycte
Order By ManagerEmpId, Seq;

go

Drop Table Test;