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

    Display the value sql only one time

    hi friends,
    please see the below table format

    Qution1? Option1
    Qution1? Option2
    Qution1? Option3
    Qution1? Option4

    want to display

    Qution1? Option1
    Option2
    Option3
    Option4

    Thank you
  • #763482
    do you have one table and with two fields?

    Can you tell more details about your question?

    By Nathan
    Direction is important than speed

  • #763483
    Thank you response
    i hve two tables.
    Qution is one table and options are another table.

    my query like

    select t1.qution,t2,option from qutiontable t1 inner join optiontable t2 on t1.qutionid=t2.qutionid where t1.branch=1 and t1.usertype=1

  • #763484
    I think 'row_number()' and 'partition by' will help you to achieve this task
    Partition by :
    Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied
    see below link for more detail
    http://stackoverflow.com/questions/17507887/eliminating-duplicate-values-based-on-only-one-column-of-the-table

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

  • #763486
    Tips to do this. You can create on function and handle that. I explained the logic. You can try this.

    declare @FirstRow int = 0
    declare @Firstqution varchar(50) = 0

    select CanShowQuestion(t1.qution, @Firstqution,@FirstRow),t2,option from qutiontable t1 inner join optiontable t2 on t1.qutionid=t2.qutionid where t1.branch=1 and t1.usertype=1


    Function CanShowQuestion(...
    1. Check @FirstRow = 1
    if it is 1 and @Firstqution != t1.qution
    increment the @FirstRow
    set @Firstqution = t1.qution
    return t1.qution value
    if @FirstRow != 1 and @Firstqution == t1.qution
    increment the @FirstRow
    set @Firstqution = t1.qution
    return ""

    By Nathan
    Direction is important than speed


Sign In to post your comments