Hi ahmedsa,
Use Case statement to split in two columns then apply pivot to the result.
Refer below query and set the Header.
SQL
create table #nonparametricdata
(
PART_ID nvarchar(50) ,
CompanyName nvarchar(50),
PartNumber nvarchar(50),
DKFeatureName nvarchar(100),
Tempvalue nvarchar(50),
FlagAllow bit
)
insert into #nonparametricdata
values
('1222','Honda','silicon','package','15.50Am',0),
('1900','MERCEIS','GLASS','family','90.00Am',1),
('5000','TOYOTA','alominia','source','70.20kg',0),
('8000','MACDA','motor','parametric','50.40kg',1),
('8900','JEB','mirror','noparametric','75.35kg',0)
create table #FinalTable
(
DKFeatureName nvarchar(50),
DisplayOrder int
)
insert into #FinalTable (DKFeatureName,DisplayOrder)
values
('package',3),
('family',4),
('source',5),
('parametric',2),
('noparametric',1)
SELECT *
INTO #Test
FROM (SELECT PART_ID, PartNumber, CompanyName,
CASE WHEN FlagAllow = 1 THEN SUBSTRING(Tempvalue,0,6)
ELSE Tempvalue
END 'Value',
CASE WHEN FlagAllow = 1 THEN SUBSTRING(Tempvalue,6,LEN(Tempvalue)-1)
ELSE NULL
END 'Unit',
ft.DKFeatureName
FROM #nonparametricdata npd
INNER JOIN #FinalTable ft ON npd.DKFeatureName = ft.DKFeatureName) t
--SELECT * FROM #Test
DECLARE @sh AS TABLE (DKFeatureName VARCHAR(100),DisplayOrder INT)
INSERT into @sh select Distinct DKFeatureName , DisplayOrder from #FinalTable
declare @SQL NVARCHAR (MAX) = ''
---------------------------------------
declare @Columns nvarchar(max)=( select
substring(
(
Select ',['+ST1.DKFeatureName +']' AS [text()]
From @sh ST1 order by DisplayOrder
For XML PATH ('')
), 2, 10000) [Columns])
--select @Columns
DECLARE @Header nvarchar(max)=( select
substring(
(
Select ', '''+ST1.DKFeatureName +''', '''+ST1.DKFeatureName +'Unit'''
From @sh ST1 order by DisplayOrder
For XML PATH ('')
), 2, 10000) [Columns])
--select @Header
declare @ColumnsN nvarchar(max)=( select
substring(
(
Select ',n3.['+ST1.DKFeatureName +'] as '+ST1.DKFeatureName +'' ,',n4.['+ST1.DKFeatureName +'] as '+ST1.DKFeatureName +'Unit'
From @sh ST1 order by DisplayOrder
For XML PATH ('')
), 2, 10000) [Columns])
select @SQL = N'
SELECT * Into #NewTable3 FROM (select * from #Test PIVOT(max(Value) FOR DKFeatureName IN ('+@Columns+')) AS PVTTable )q
SELECT * Into #NewTable4 FROM (select * from #Test PIVOT(max(Unit) FOR DKFeatureName IN ('+@Columns+')) AS PVTTable1 )w
select ''PART_ID'' as ''PART_ID'' ,''PartNumber'' as ''Z2 Part number'' , ''CompanyName'' as ''Z2 Manufacturer'' , ' +@Header + '
union all
SELECT n3.PART_ID,n3.PartNumber,n3.CompanyName,'+@ColumnsN+' FROM #NewTable3 n3
INNER JOIN #NewTable4 n4 ON n3.PART_ID = n4.PART_ID'
--PRINT @SQL
EXEC (@SQL)
DROP TABLE #nonparametricdata
DROP TABLE #FinalTable
DROP TABLE #Test
