locked
How to Display FeatureName of Comptitor First then Npx Feature Name second Based On DisplayOrder? RRS feed

  • Question

  • User696604810 posted

    How to Display Feature Name of Comptitor First then NXP Feature Name second Based On Display Order?

    I work on SQL SERVER 2012 I face issue I can't arrange feature on same display order to start by comptitor

    feature name then nxp

    no issue on display order 1 and 2 because it is correct

    issue exist on display order 3

    so if i have more than one features have same display order then i need all features have same display Order

    to be arranged as :

    comptitor feature

    Nxp feature

    issue I face here all comptitor feature come first then nxp second for same display order and this wrong

    so wrong is features will display for same display order as :

    comptitor function
    comptitor type
    nxp function
    nxp type

    correct is features will display for same display order as :
    comptitor function
    nxp function
    comptitor type
    nxp type



    what i try

    SELECT   FeatureName,displayorder 
      FROM   [ExtractReports].[dbo].[FeaturesOrder]  with(nolock)
      group by FeatureName,displayorder
      ORDER BY  displayorder ASC,FeatureName asc
    ddl and insert statment
    USE [ExtractReports]
    GO
    / Object: Table [dbo].[FeaturesOrder] Script Date: 4/15/2021 4:52:17 AM /
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[FeaturesOrder](
    [FeatureName] [nvarchar](511) NULL,
    [DisplayOrder] [int] NULL
    ) ON [PRIMARY]
    
     GO
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Accelerometers Type', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Battery Type', 3)
     
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Function', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Multiplexer And Demultiplexer', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Type', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Type', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Automotive', 1)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Diode Type', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Normalized Package Name', 2)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Automotive', 1)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Accelerometers Type', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Amplifier Type', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Battery Type', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Function', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Multi-Demultiplexer Circuit', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Multiplexer And Demultiplexer', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Output Type', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Amplifier Type', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Diode Type', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Multi-Demultiplexer Circuit', 3)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Normalized Package Name', 2)
     INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Output Type', 3)

    Expected result as :

    FeatureName displayorder
    Competitor Automotive 1
    NXP Automotive 1
    Competitor Normalized Package Name 2
    NXP Normalized Package Name 2
    Competitor Accelerometers Type 3
    NXP Accelerometers Type 3
    Competitor Battery Type 3
    NXP Battery Type 3
    Competitor Function 3
    NXP Function 3
    Competitor Multiplexer And Demultiplexer 3
    NXP Multiplexer And Demultiplexer 3
    Competitor Type 3
    NXP Type 3
    Competitor Multi-Demultiplexer Circuit 3
    NXP Multi-Demultiplexer Circuit 3
    Competitor Amplifier Type 3
    NXP Amplifier Type 3
    Competitor Diode Type 3
    NXP Diode Type 3
    Competitor Output Type 3
    NXP Output Type 3
    Thursday, April 15, 2021 3:59 AM

All replies

  • User-1716253493 posted

    Use REPLACE Function To Remove NPX, Competior and Type for second order

    REPLACE(REPLACE(REPLACE(CompetitorName,'NPX ',''),'Competitor',''),' Type','')

    Use CASE for thirt order

    CASE
    WHEN FeatuterName LIKE 'NPX%' AND FeatureName LIKE '%Type' THEN 4
    WHEN FeatuterName LIKE 'Competitor%' AND FeatureName LIKE '%Type' THEN 3
    WHEN FeatuterName LIKE 'NPX%' THEN 2
    ELSE 1
    END

    Will be better if you save Competitor, NPX and Type in another Column

    Or Use Computed Column for REPLACE and CASE function above

    Thursday, April 15, 2021 6:25 PM