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