Click here to Skip to main content
14,579,188 members
Rate this:
Please Sign up or sign in to vote.
See more: , +
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


Expected Result for rows have Allow Flag=1 will be as below :

File sharing and storage made simple[^]

What I have tried:

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)
Posted
Updated 19-Jun-20 6:51am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Your question is not at all clear but there are a couple of things I can point out that might help you.

If you are going to use temporary tables in dynamic SQL then you need to create them first.
The table created by
SELECT *  Into #NewTable2
is not available to the "outer" SQL session because it was created only within the scope of that EXEC, which might explain why you are not seeing the results of your efforts.

Try creating the table first and using
INSERT INTO #NewTable2 SELECT *  ...
There is a discussion on this here[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100