Dynamic Column List In Sorting Order

Posted: January 29, 2015 | Author: | Filed under: Uncategorized | Leave a comment 

create table KeywordFieldMap(fieldname varchar(50))
Insert into KeywordFieldMap values('aaa'),('bbbb'),('aaa'),('ab0s'),('1aaa'),('zaaa')
DECLARE @cols AS NVARCHAR(MAX);

select @cols = STUFF( (SELECT  ',' + QUOTENAME(fieldName)  FROM KeywordFieldMap  
Group by fieldName
Order by fieldName 
FOR XML PATH(''),type).value('.','varchar(max)'),1,1,'') 

select @cols



drop table KeywordFieldMap


--Another
create table test (Header  varchar(50), Score  varchar(50),  UserID int)
insert into test values('Grade','C',1)
,('Grade','B',2)
, ('Percent','79.80',1)
,('Percent','89.0',2)

DECLARE @col AS NVARCHAR(max)=''
DECLARE @sql AS NVARCHAR(max)=''
  
SELECT @col=stuff( (SELECT  distinct ',['+ Header  +']'
FROM test   
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;

SET @sql='SELECT [UserID], ' + @col + ' FROM (
Select userid,Header,Score from test) src
Pivot (max(Score) For Header in ('+ @col + ')) AS pvt'
 
 
EXEC sp_executesql @sql

 drop table test

For XML PATH to Concatenate Rows

Posted: January 29, 2015 | Author: | Filed under: Uncategorized | Leave a comment 

CREATE  TABLE  test (id INT, Code varchar(50), Category  varchar(50), value decimal(6,0))

INSERT INTO test VALUES (1,'AB','XYZ',1000),(2,'BC','PQR',2000),(3,'AB','XYZ',1000),(4,'BC','PQR',2000)

SELECT t1.Code,t1.Category,
       Stuff(( SELECT ',' + Cast(t2.id as varchar(5))
           FROM test t2
          WHERE t2.Code = t1.Code and  t2.Category = t1.Category
          ORDER BY id
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS ids
  FROM test t1
 GROUP BY t1.Code,t1.Category;


 drop table test

--another sample with cross apply
CREATE TABLE mytable(
   Id     INTEGER  NOT NULL 
  ,Date   DATE  NOT NULL
  ,Diet   VARCHAR(100)
 
);
INSERT INTO mytable(Id,Date,Diet ) 
VALUES (13,'2018-07-02','1" Pieces cut to size'),
  (13,'2018-07-02','1/2" Pieces cut to size'),
 (13,'2018-07-12',null ),
 (13,'2018-07-11',null );
  
 Select distinct  Id,Date, STUFF(t.Diets,1,1,'')  Diets
  from mytable  m
 cross apply
 (Select Stuff((select ','+ RTRIM(t1.Diet)  Diets 
 from mytable t1
 where m.id= t1.id and m.Date =t1.Date
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,''))    t ( Diets)     

 drop table mytable

Another Text Editor– Directory Opus 11

Posted: January 28, 2015 | Author: | Filed under: Uncategorized | Leave a comment

Directory Opus comes with lite (free) and pro versions form one download.
When you have a large text file to preview (>100M), you may hang in there forever.
Here is a quick way to use the Directory Opus’s Slidesshow to open the file. I used the Light version.

Click on Slideshow Tab from the program (Directory Opus) to peek a large text file and then copy the opened slideshow file to a new text file and next to use Notepad++ to view Symbols for what I want to view.

Done.

Download the light version or try/buy the pro version from here:
http://www.gpsoft.com.au/

bcp to Export Empty String to NUL in Text File (Casuses issues to work with other systems)

Posted: January 28, 2015 | Author: | Filed under: Uncategorized | Leave a comment

When you export data with bcp command, you may see some NUL value in the place of empty string. The workaround is to use NULL in TSQL to replace empty string with NULL by using NULLIF function.

Found the discussion here:

How to make Microsoft BCP export empty string instead of a NUL char?
http://stackoverflow.com/questions/12588149/how-to-make-microsoft-bcp-export-empty-string-instead-of-a-nul-char

UNPIVOT,PIVOT and PIVOT Dynamically

Posted: January 28, 2015 | Author: | Filed under: Uncategorized | Leave a comment 

--Prepare sample tables
create table srcTable (Date datetime, [AC-1] decimal(8,4),  [AM-1] decimal(8,4), [AM-1E] decimal(8,4),[AM-1W] decimal(8,4), [AM-2] decimal(8,4))
Insert into srcTable (Date,[AC-1],[AM-1],[AM-1E]) values('1996-09-18', null, null, null),('1996-11-26', null, null, null),('1991-01-22', 26.45, null, null),('1991-01-28', 25.73, null, null)

create table targettable (Piezmeter varchar(10), TOC decimal(8,4), RedFlag decimal(8,4))
Insert into targettable values('AC-1',738.65, 720.00),('AM-1',723.31, 720.00),('AM-1E',722.7, 720.00), ('AM-1W',722.5, 720.00),('AM-2',723.3, 720.00)



;with mycte as (
Select col,val from (
select SUM([AC-1]) as [AC-1], SUM([AM-1]) as [AM-1], SUM([AM-1E]) as [AM-1E],SUM([AM-1W]) as [AM-1W], SUM([AM-2]) as [AM-2]  from srcTable) t
--UNPIVOT with cross apply
Cross apply (values('AC-1',[AC-1]),('AM-1', [AM-1]),('AM-1E', [AM-1E]),('AM-1W', [AM-1W]),('AM-2',[AM-2]) ) d(col, val)

)

--select Piezmeter, TOC-ISNULL(val,0) as val, RedFlag 
--from target  t Left join mycte m on t.Piezmeter=m.col


--PIVOT


Select [AC-1],[AM-1], [AM-1E], [AM-1W],[AM-2], RedFlag from ( 
select  Piezmeter, TOC-ISNULL(val,0) as val, RedFlag 
from targettable  t Left join mycte m on t.Piezmeter=m.col) s 
PIVOT (Max(val) For Piezmeter IN ([AC-1],[AM-1], [AM-1E], [AM-1W],[AM-2]) ) pvt


 

 ----Dynamic way
 
 --You need to modify the query to get the final PIVOT

DECLARE @col AS VARCHAR(max)=''
DECLARE @sql AS VARCHAR(max)
 
SELECT @col=stuff( (SELECT ',['+ Piezmeter  +']'
FROM targettable  
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')
  ;
 
--print @col
 
SET @sql='SELECT [RedFlag], ' + @col
+ ' FROM (select  Piezmeter, TOC  as val, RedFlag from targettable  t  ) s PIVOT (MAX(val) FOR [Piezmeter] IN ('+ @col + ')) AS pvt'
--print @sql
EXEC (@sql)



--clean up
drop table srctable,targettable

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql

Custom Sorting on Alpha Numeric Column

Posted: January 28, 2015 | Author: | Filed under: Uncategorized | Leave a comment 

create table #t (col varchar(100))

insert into #t values ('10 x 5.5 x 2')
insert into #t values ('125 x 95 x 13.5/21')
insert into #t values ('1264.01 x 1200 x 22.23')
insert into #t values ('130 x 105 x 12')
insert into #t values ('130 x 95 x 12') --added
insert into #t values ('210 x 180 x 15')
insert into #t values ('28 x 16 x 15.2')
insert into #t values ('28 x 16.8 x 6/7.5')
insert into #t values ('52 x 25 x 10')
insert into #t values ('52 x 25 x 7/7.5')
insert into #t values ('52/58 x 25 x 7')
insert into #t values ('52/59 x 25 x 10/15.5')
insert into #t values ('55/61 x 35 x 11.5/16')
insert into #t values ('74.2/78.2 x 56 x 8/10.5')
insert into #t values ('74.5/79.5 x 45/42.5 x 12/13.')

 

;with mycte as (
select col,
parsename(Replace(replace(col,'.','*'),'X','.'),3) col1
,parsename(Replace(replace(col,'.','*'),'X','.'),2) col2
,parsename(Replace(replace(col,'.','*'),'X','.'),1) col3  from #t)

,mycte1 as (
Select col,
 Cast(ISNULL(Replace(parsename(Replace(col1,'/','.'),2),'*','.'), Replace(parsename(Replace(col1,'/','.'),1),'*','.')) as Decimal(6,2)) col11
 , Cast(Replace(parsename(Replace(col1,'/','.'),1),'*','.') as Decimal(6,2)) as col12
 , Cast(ISNULL(Replace(parsename(Replace(col2,'/','.'),2),'*','.'), Replace(parsename(Replace(col2,'/','.'),1),'*','.')) as Decimal(6,2)) col21
 , Cast(Replace(parsename(Replace(col2,'/','.'),1),'*','.') as Decimal(6,2)) as col22
 , Cast(ISNULL(Replace(parsename(Replace(col3,'/','.'),2),'*','.'), Replace(parsename(Replace(col3,'/','.'),1),'*','.')) as Decimal(6,2)) col31
 , Cast(Replace(parsename(Replace(col3,'/','.'),1),'*','.') as Decimal(6,2)) as col32 

from  mycte )

Select col from mycte1
Order by col11,col12,col21,col22,col31,col32

 

drop table #t

https://social.msdn.microsoft.com/Forums/en-US/242efaf7-7aaa-46e6-a537-1fbd9666abe9/how-to-sort-the-alpha-numeric-character-into-number?forum=transactsql

Island Puzzle — a solution and another better one

Posted: January 26, 2015 | Author: | Filed under: Uncategorized | Leave a comment 

 CREATE TABLE SalesByMonth
(
	SalesMonth date PRIMARY KEY,
	Sales int
)

INSERT INTO SalesByMonth VALUES
('20140101', 4000),
('20140201', 4000),
('20140301', 4000),

 --('20140401', 4000),

('20140501', 4000),
('20140601', 100),
('20140701', 100),
('20140801', 4000),
('20140901', 4000),
('20141001', 4000),
('20141101', 4000),
('20141201', 4000),
('20150101', 4000),
('20150201', 4000),
('20150301', 4000)

 
 

--Mine
 ;with mycte as (


 Select dateadd(month,n, dt) dt from ( select Cast('20140101' as date) dt ) src
 cross apply (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) d(n)
 )

,mycte1 as (
select  *, SUM( case when Sales>=4000 Then 1 else 0 End ) Over(Order by dt ROWS BETWEEN 3 preceding AND CURRENT ROW) slidingTotal4
from mycte m left join SalesByMonth s on m.dt=s.SalesMonth 
)
 
 , mycte2 AS
(
	SELECT 
		SalesMonth,	ROW_NUMBER() OVER (PARTITION BY slidingTotal4 ORDER BY SalesMonth) AS rn
	FROM mycte1
	WHERE  slidingTotal4=4
)	
 

SELECT SalesMonth AS GiftSalesMonth, rn
FROM mycte2
WHERE    rn % 4 = 1


---Another better one from Jesus Lopez

;WITH B AS
(
	SELECT 
		SalesMonth, Sales,
		DATEADD(MONTH, -DENSE_RANK() OVER (ORDER BY SalesMonth), SalesMonth) AS IslandId
	FROM 
		SalesByMonth
	WHERE 
		Sales >= 4000
)
, C AS
(
	SELECT 
		B.SalesMonth, 
		ROW_NUMBER() OVER (PARTITION BY IslandId ORDER BY SalesMonth) AS RowNumberInIsland
	FROM B
)	
SELECT
	C.SalesMonth AS GiftSalesMonth
FROM 
	C
WHERE
	RowNumberInIsland % 4 = 0

drop table SalesByMonth


--https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d4c8aba6-7e23-41fd-96c1-c61a4d60fcfa/interesting-puzzle?forum=transactsql#61bb76e5-8895-4f55-909e-4920a8e9c02c

How to Fill Column Null Values from Previous Non-null Value

Posted: January 19, 2015 | Author: | Filed under: Uncategorized | Leave a comment 

 
 
IF OBJECT_ID(N'dbo.Test', N'U') IS NOT NULL DROP TABLE dbo.test;
GO
CREATE TABLE dbo.test (  id INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,  col1 INT NULL);
 

INSERT INTO dbo.Test(id, col1) VALUES
  ( 2, NULL),
  ( 3,   10),
  ( 5,   -1),
  ( 7, NULL),
  (11, NULL),
  (13,  -12),
  (17, NULL),
  (19, NULL),
  (23, 1759);


 
SELECT id, col1,
  CAST(SUBSTRING(MAX( CAST(id AS BINARY(4)) + CAST(col1 AS BINARY(4)) ) OVER( ORDER BY id ROWS UNBOUNDED PRECEDING ), 5, 4) AS INT) AS lastval
FROM dbo.test;


drop table test


--By Itzik Ben-Gan
--http://sqlmag.com/t-sql/last-non-null-puzzle

End of month : Dateadd with Datepart MONTH

Posted: January 15, 2015 | Author: | Filed under: Uncategorized | Leave a comment

”
If datepart is month and the date month has more days than the return
month and the date day does not exist in the return month, the last day of the return month is returned.
”

Here is a sample to get month end date within a year:


 
 


;with mycte as (
select 1 as n, dateadd(year, datediff(year,0,getdate()), 0)  as Firstdt,EOMONTH(dateadd(year, datediff(year,0,getdate()), 0) )  as Enddt1
union all
Select n+1 as n, Firstdt,  EOMONTH(dateadd(month,n,Firstdt))    Enddt1 from mycte where n<12

)


select n as monthNum,Enddt1 from mycte
order by Enddt1

http://msdn.microsoft.com/en-us/library/ms186819.aspx

Mapping Drive with Powershell

Posted: January 13, 2015 | Author: | Filed under: Uncategorized | Leave a comment 


New-PSDrive -Name w -PSProvider FileSystem -Root \\myShare1\SQLBackups -Persist -Scope Global



http://powershell.com/cs/blogs/tips/archive/2015/01/13/mapping-drives.aspx
← Older Entries