Dynamic Column List In Sorting Order


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

 
Advertisements

For XML PATH to Concatenate Rows


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 Text Editor– Directory Opus 11

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)

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


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


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


 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