Question How to add column column unitmeasure dynamically based on Flag Allow is 1?

ahmedsalah

Member
Joined
Sep 26, 2018
Messages
21
Programming Experience
3-5
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
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)

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 :
 
Last edited by a moderator:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,575
Location
Virginia Beach, VA
Programming Experience
10+
Looks like a SQL question rather than a C# question, I'm not seeing even an ounce of C# in that code above.
 

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,378
Location
UK
Programming Experience
10+
Top Bottom