How to add column columnunit dynamically when FlagAllow is 1?
-
How to add column column unit dynamically when Flag Allow is 1 on table #nonparametric table ?
I work on SQL server 2012 I can't add column unit dynamically when Flag Allow=1
as example below I have two rows have Flag Allow=1
family 90AM will be family 90 and family unit AM on two column because it have Flag Allow=1
parametric 50.4kg will be parametric 50.4 and parametric unit kg on two column because it have Flag Allow =1
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)
DECLARE @sh [dbo].[FeaturesbyPL];
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 +''' as ['+ST1.DKFeatureName +']' AS [text()]
From @sh ST1 order by DisplayOrder
For XML PATH ('')
), 2, 10000) [Columns])
select @Header
select @SQL =CONCAT('
SELECT * Into #NewTable2
FROM #nonparametricdata
PIVOT(max(Tempvalue) FOR DKFeatureName IN ('+@Columns+')) AS PVTTable
',
N'
select
''PART_ID'' as ''PART_ID'' ,''PartNumber'' as ''Z2 Part number'' , ''CompanyName'' as ''Z2 Manufacturer'' , ' +@Header + '
union all
select PART_ID , PartNumber , CompanyName , ' +@Columns + ' from #NewTable2
')
EXEC (@SQL)Expected Result for rows have Allow Flag=1 will be as below
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
I think this might be possible with a case statement. Can you explain how you split the two columns when flagallow=1? Is it always the right two characters from the tempvalue column?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply