Export Flat File from SQL Server for AS400 System to Consume (with Code page 65001(UTF-8) )

I have a client request for a csv file from our system to consume in their AS400 system.

The request was side-tracked by to remove the last non-visible line at the end of the file.

The real problem is the encoding issue. The default encoding from our Windows (2012) system is 1252(ANSI -Latin I).
First I tried Code Page 437 but it didn’t work.

Finally I switched the encoding to Code page 650001(UTF-8) from the Flat File Connection Manager,
and it seems the file works in the third party environments.


Dynamic Matrix (UNPIVOT and PIVOT)

  
 
create TABLE pivot_information  (
month_col VARCHAR(50),
[scrapRate] int,
[surfaceRepairRate] int,
[totalRepairRate] int ,
[totalProduction] int,
[totalSurfaceParts] int,
[totalScrapNumber] INT ,
[surfaceRepairingNumber] int ,
[allRepairingNumber] int

)

INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'jan', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'feb', 6.58, 20.85,85.12,35652,4521,502,400,1002)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'march', 6.58, 20.85,85.12,35653,4521,503,400,1003)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'april', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'may', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'june', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'july', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'august', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'september', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'october', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'november', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'december', 6.58, 20.85,85.12,35651,4521,500,400,1000)

 



Declare @sql1 as NVarchar(4000)
Declare @sql2 as NVarchar(4000)
Declare @Cols1 as NVarchar(4000)
Declare @Cols2 as NVarchar(4000)
 
Set @Cols1=null
 
Select @Cols1 =  COALESCE(@Cols1 + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'
FROM  [INFORMATION_SCHEMA].[COLUMNS]
WHERE TABLE_NAME='pivot_information' and COLUMN_NAME<>'month_col'

  --Print @Cols1

  Select @sql1=';with mycte as (Select
month_col, col,val
FROM pivot_information t
CROSS APPLY (Values ' + @Cols1 + ' )  d(col,val) )
SELECT   @Cols2  = STUFF( (SELECT  '','' + ''Max(CASE WHEN month_col='' + quotename(month_col,'''''''') + '' THEN val else null end ) as '' + quotename(month_col)  + char(10)+char(13)
                             FROM mycte
							 Group by month_col
							 order by Case 
							 when month_col=''Jan'' then 1
							  when month_col=''Feb'' then 2
							   when month_col=''March'' then 3
							    when month_col=''APril'' then 4
								 when month_col=''May'' then 5
								  when month_col=''June'' then 6
								   when month_col=''July'' then 7
								   when month_col=''August'' then 8
								   when month_col=''September'' then 9
								   when month_col=''October'' then 10
								    when month_col=''November'' then 11
									 when month_col=''December'' then 12
									 End							 
  FOR XML PATH(''''), TYPE).value(''.'', ''varchar(max)''), 1, 1, ''''); '
 
 

 EXEC sp_executesql @sql1,N'@Cols2 NVARCHAR(4000) output',@Cols2  output


  
 Set @sql2  =N';with mycte as (Select month_col, col,val
FROM pivot_information t
CROSS APPLY (Values ' + @Cols1 + ' )  d(col,val) )
Select  col, '+   @Cols2 + '  from  mycte   Group by col';

EXEC sp_executesql @sql2

 
drop table pivot_information


 

https://social.msdn.microsoft.com/Forums/en-US/9e915c1e-ec50-45c9-925b-5a2b89458cc8/conveting-rows-to-columns-vice-versa-in-sql-server?forum=transactsql


Export Flat File Without Header in SSIS Package

When export flat file through bcp, it will take some efforts to add the header row.
This is very easy to hide it from SSIS Flat File Connection.
In SSIS package, we can define a property from Flat File Connection to set “ColumnNamesInFirstDataRow” to “False”.
The result flat file will have data only without hearder row.


Get System Generated Object_Id for Table Variable

  
 
 DECLARE @ColMapTable TABLE (ColumnName varchar(300),ColumnDataType varchar(300),Lengths varchar(10))
declare @tempid int


select top 1 @tempid=id  from tempdb..sysobjects
  where type ='U'
  order by crdate desc

  select top 1  id as object_id  from tempdb..sysobjects
  where type ='U'
  order by crdate desc

 

 ---the table variable is stored as Temporary Tables in tempdb
  select top 1 name  from tempdb..sysobjects
  where type ='U'
  order by crdate desc


 

Get Metadata Information of Table Variable

  
 
 DECLARE @ColMapTable TABLE (ColumnName varchar(300),ColumnDataType varchar(300),Lengths varchar(10))
/*
---your other code
*/

SELECT c.name as COLUMN_NAME 
FROM  tempdb.sys.columns c 
where  c.object_id=(select top 1 id  from tempdb..sysobjects
  where type ='U'
  order by crdate desc)

 

https://social.msdn.microsoft.com/Forums/en-US/bf666d9a-1972-4145-b218-ec47da344872/getting-error-in-sql-server?forum=transactsql


Unpivot Two Columns

  
create table salesorghierarchy(id int,sotl6Id varchar(20),sotl6desc  varchar(20)
, sotl5Id  varchar(20),sotl5desc  varchar(20), sotl4Id  varchar(20),sotl4desc  varchar(20))
insert into salesorghierarchy values (1,'a','a1','b','b1','c','c1'), (2,'a','a2','b','b2','c','c2') 

--option 1
select id, sotlIds,sotldescs 
from salesorghierarchy 
 cross apply(values ( sotl6Id,sotl6desc),(sotl5Id,sotl5desc),(sotl4Id,sotl4desc)) d(sotlIds,sotldescs)

 --option2
 Select  id, sotlId , sotldesc From (
Select   id, sotlId , sotldesc
  , idid = Left(Right(sotlIds,3),1)   
  , iddesc =  Left(Right(sotldescs,5),1)   	
	FROM ( select * from  salesorghierarchy ) src
  unpivot   (sotlId for sotlIds in  (sotl4Id, sotl5Id,sotl6Id ) ) unpvt1
  unpivot   (sotldesc for sotldescs in ([sotl4desc], [sotl5desc],[sotl6desc])) unpvt2 
 ) AS t
WHERE idid=iddesc;

--option 3
 select id, sotl4Id,sotl4desc 
from salesorghierarchy 
Union
 select id, sotl5Id,sotl5desc 
from salesorghierarchy 
Union
select id, sotl6Id,sotl6desc 
from salesorghierarchy 



drop table salesorghierarchy


 

https://social.msdn.microsoft.com/Forums/en-US/704c724f-7ad7-4e88-8532-43b5eb783a46/avoid-unions?forum=transactsql


Relational Division to Find Exact Match Groups

  
create table #A (Name varchar(10), Location varchar(10))

insert into #A values ('A10','sp')
insert into #A values ('A10','lt')
insert into #A values ('A10','pt')
insert into #A values ('A11','sp')
insert into #A values ('A11','lt')
insert into #A values ('A12','sp')

 --Option 1
 ;with mycte as (
select  Name,Location, count(*)Over(Partition by name) cnt  from #a  s 
WHERE Exists (select 1 from   #a  t WHERE t.Location='sp' and s.Name=t.Name)
and Exists (select 1 from   #a  t WHERE t.Location='lt' and s.Name=t.Name)
)
Select Name,Location from mycte
WHERE cnt=2
 

--option 2
 ;with mycte as (
Select Name, Location 
,SUm(
Case
when Location='sp' then POWER(2*1,1) --sp
when Location='lt' then POWER(2*1,2) --lt
--when Location='' then POWER(2*1,3) --other 
End) Over(Partition by name)  bitSUM ,
count(*)Over(Partition by name) cnt
from #a
)
 
 
Select name,location from mycte
WHERE (2 & bitSUM = 2 )  
and  (4 & bitSUM = 4)  
--AND (8 & bitSUM = 8) --other
and cnt=2
 



--option 3
 ;with mycte as (
 Select Location from (values('sp'),('lt')) d(Location )
 )

 ,mycte1 as (
 SELECT src.Name, CASE WHEN COUNT(src.Location) > (SELECT COUNT(Location) FROM mycte ) AND COUNT(checklist.Location) = (SELECT COUNT(Location)FROM mycte )
            THEN 'match with extra'
            WHEN COUNT(src.Location) = (SELECT COUNT(Location) FROM mycte) AND COUNT(checklist.Location) = (SELECT COUNT(Location) FROM mycte)
            THEN 'exactly match'
            WHEN MIN(checklist.Location) IS NULL
            THEN 'none'
            ELSE 'some' END AS location_status
  FROM   #a AS src LEFT JOIN  mycte AS checklist   ON src.Location = checklist.Location
  GROUP BY src.Name )

  Select a.Name,a.Location 
  from #a a join mycte1 m 
  on a.name=m.Name
  Where m.location_status='exactly match'
  
drop table #a

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/23e72326-62e1-450b-a5fd-9c74fd4d4c91/help-in-search-query?forum=transactsql