You must Sign In to post a response.
  • Category: SQL Server

    How to create a query for below situation

    I have some dynamic control which is generating from database. Its control details are storing like below in table.

    tblControl

    ControlName. ControlText
    -------------------------------------------------
    chkMaleYes. Sex...
    chkMaleNo. Sex...
    And these control values are saving to another table from page; which is given below.

    tblData

    ID. ControlName.. Value
    -----------------------------------------------
    1. chkMaleYes. Yes
    1. chkMaleNo. No
    2. chkMaleYes. No
    2. chkMaleNo. Yes
    Above table means in one save it will inserts two rows... ID 1 means they selected Male from page; ID 2 means they selected Female.... Form the above data; I need to get result like

    ID. Sex
    ------------------
    1. Male
    2. Female
    If need then we can add some additional columns....
  • #767638
    Pradeep,

    You can make use of case statement in SQL server to get the SEX as male or female.

    I have generated a SQL for your requirement, Kindly check it.


    CREATE TABLE #CONTROL_VALUES
    (
    ID INT
    , CONTROL_NAME VARCHAR(100)
    , VALUE VARCHAR(3)
    );

    INSERT INTO #CONTROL_VALUES(ID, CONTROL_NAME, VALUE)
    VALUES(1, 'chkMaleYes', 'Yes');
    INSERT INTO #CONTROL_VALUES(ID, CONTROL_NAME, VALUE)
    VALUES(1, 'chkMaleNo', 'No');
    INSERT INTO #CONTROL_VALUES(ID, CONTROL_NAME, VALUE)
    VALUES(2, 'chkMaleYes', 'No');
    INSERT INTO #CONTROL_VALUES(ID, CONTROL_NAME, VALUE)
    VALUES(2, 'chkMaleNo', 'Yes');

    -- Select query for your required answer
    SELECT ID, CASE WHEN CONTROL_NAME = 'chkMaleYes' THEN 'Male' ELSE 'Female' END AS SEX
    FROM #CONTROL_VALUES
    WHERE VALUE = 'Yes';

    Please mark this as Answer, if this helps

    Regards,
    Alwyn Duraisingh.M 
    << Database Administrator >>
    Jesus saves! The rest of us better make backups...

  • #767639
    do not add control name in table, instead just add what he have select, suppose if he has select 'chkMaleYes' then directly save it as 1 else directly add 2.
    additionally you have to add some extra column in database that will use to identify your values.

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #767656
    Hi,

    Could you please elaborate your requirement clearly, like why you need to save controlname in your db, what's the wrong if you save some alias name instead of that and what difficulties are you facing now etc...

    Apart from above 2 suggestions, my suggestion is if you want to save ID 1 for male and 2 for female then while saving time you can pass the appropriate value as you want.

    Ex:

    if(chkMaleYes.Checked)
    {
    //insert id 1
    }
    else
    {
    // insert id 2
    }


    Hope this helps you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #767670
    Hai Pradeep,
    You don't need to save the data for Male/Female in to the table, instead, just create an enum in the C# code and use it.
    For saving a record for the customer, you can use the value like 1 & 2 for Male and Female in the customerdetails table.

    enum Gender
    {
    Male = 1,
    Female = 2
    }

    Now wherever you want to save the data, you can use Gender.Male so it will take the value as 1 and Gender.Female will use the value as 2.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com


Sign In to post your comments