SQL Server Performance

Need to select 2 columns with different requiremen

Discussion in 'SQL Server 2005 General Developer Questions' started by mic_lch83, Jan 26, 2007.

  1. mic_lch83 New Member

    I need to make a "select" statement that will create two columns with different requirements.

    My Database is like this.
    PopTotal Variance
    2 1
    3 1
    4 1
    5 2
    6 2

    I need to create a table from an sql statement that will show.
    PopTotal1 PopTotal2
    2 5
    3 6
    4
    Meaning two columns from the same column with different requirements. How can I do that with sql statements?
  2. MohammedU New Member

  3. mic_lch83 New Member

    I thought of case too. But, I don't think it helps because for case, you need to provide a value to be included into the column of each rows.
    Plus, I do not want to select the Variance.

    "select PopTotal (with Variance=4), PopTotal (with Variance=6) from Population where revid=11"
    What will show up will be two columns for PopTotal with different variances..

    Thanks in advance.
  4. ndinakar Member

    You could write 2 separate SELECT statements with the conditions you have and put an outer SELECT around it

    SELECT
    (SELECT ..... FROM ...) as Col1,
    (SELECT ..... FROM ...) as Col2



    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
  5. khtan New Member


    drop table #tbl
    create table #tbl
    (
    PopTotalint,
    Varianceint
    )

    insert into #tbl(PopTotal, Variance)
    select2, 1 union all
    select3, 1 union all
    select4, 1 union all
    select5, 2 union all
    select6, 2


    alter table #tbl add seq int
    go

    update t
    set seq = (select count(*) from #tbl x where x.Variance = t.Variance and x.PopTotal <= t.PopTotal)
    from#tbl t

    selectPopTotal1 = max(case when Variance = 1 then PopTotal end),
    PopTotal2 = max(case when Variance = 2 then PopTotal end)
    from#tbl
    group by seq

    -- RESULT
    PopTotal1 PopTotal2
    ----------- -----------
    2 5
    3 6
    4 NULL



    KH
  6. mic_lch83 New Member

    Dinakar, I followed your post and I got this error message
    "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
    I thought about this too. I just could not fix this error message.

    KHTAN, actually my database is more complex than the one I showed(that's just an example). I am trying not to create a new table because the data is huge.

    Thanks..


  7. MohammedU New Member

    Post your query...so one will help you...


    Mohammed U.
  8. khtan New Member

    quote:Originally posted by mic_lch83

    Dinakar, I followed your post and I got this error message
    "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
    I thought about this too. I just could not fix this error message.

    KHTAN, actually my database is more complex than the one I showed(that's just an example). I am trying not to create a new table because the data is huge.

    Thanks..


    The script that i posted did not creating a new table. The create table is basically for testing the script. The actual query just a new column, update it with sequence no and then generate your required result set.


    alter table #tbl add seq int
    go

    update t
    set seq = (select count(*) from #tbl x where x.Variance = t.Variance and x.PopTotal <= t.PopTotal)
    from#tbl t

    selectPopTotal1 = max(case when Variance = 1 then PopTotal end),
    PopTotal2 = max(case when Variance = 2 then PopTotal end)
    from#tbl
    group by seq



    KH
  9. mic_lch83 New Member

    Thanks KH for the help. I see where you are going to. However, I am not allow to add another column in the database. I should not edit the database, just select from it. Therefore, I could not add the 'seq' column in that.

    Mohammed, my example of query is this ""select PopTotal (with Variance=4), PopTotal (with Variance=6) from Population where revid=11". All I want is to show two columns from the same column in the database with two different requirements.

    Thanks.
  10. mic_lch83 New Member

    I am still figuring this out...... This has cause a lot of stress and time.
  11. Adriaan New Member

    You do not have a two-column resultset, but two separate one-column resultsets, each with a different number of entries.

    In a multi-column resultset, the column values on a row have a meaningful relationship, which distinguishes them from the set of values on any other row. In your lists, the first value on the first list has nothing to do with the first value on the second list.

    This is a presentation issue. You need to fire two separate queries against the database, not one. Let the client app present the two lists in separate controls.

    Any other approach will lead to serious headaches (like you have noticed).
  12. Adriaan New Member

    But since this is SQL 2005, you could use derived tables with the ROW_NUMBER() function - or whatever it's called - and join on that:

    SELECT Var4.PopTotal, Var6.PopTotal
    FROM
    (SELECT PopTotal, ROW_NUMBER() AS RowNr FROM table WHERE revid = 11 AND variance = 4 ORDER BY PopTotal) AS Var4
    FULL OUTER JOIN (SELECT PopTotal, ROW_NUMBER() AS RowNr FROM table WHERE revid = 11 AND variance = 6 ORDER BY PopTotal) AS Var6
    ON Var4.RowNr = Var6.RowNr
  13. mic_lch83 New Member

    Thanks Adriaan for your thorough explanation. Would you be able to do what you written on SQL 2000?

    I am having problems with the client app present two lists too. I am using ASP.NET gridview component. And I am having difficulties combining two gridview together.
  14. Adriaan New Member

    In 2000, you could use table variables with an IDENTITY column to fake the ROW_NUMBER() function:

    DECLARE @Var4 TABLE (RowNr INT IDENTITY(1,1), PopTotal INT)
    DECLARE @Var6 TABLE (RowNr INT IDENTITY(1,1), PopTotal INT)

    INSERT INTO @Var4 (PopTotal)
    SELECT PopTotal FROM table WHERE revid = 11 AND variance = 4 ORDER BY PopTotal

    INSERT INTO @Var6 (PopTotal)
    SELECT PopTotal FROM table WHERE revid = 11 AND variance = 6 ORDER BY PopTotal

    SELECT @Var4.PopTotal, @Var6.PopTotal
    FROM @Var4
    FULL JOIN @Var6 ON @Var4.RowNr = @Var6.RowNr
    ORDER BY ISNULL(@Var4.RowNr, @Var6.RowNr), ISNULL(@Var6.RowNr, @Var4.RowNr)
  15. mic_lch83 New Member

    I have to tried your code and integrated into the my sql database. But, I would really want to say thanks. Whether it works or not, the help was great.
  16. mic_lch83 New Member

    It is giving me an error "Line 10: Incorrect syntax near '.'"
    I don't think it can take the PopTotal variable from the @Var4.
  17. MohammedU New Member

    Relace last select statement with the following...

    SELECT V4.PopTotal, V6.PopTotal
    FROM @Var4 v4
    FULL JOIN @Var6 v6 ON v4.RowNr = v6.RowNr
    ORDER BY ISNULL(v4.RowNr, v6.RowNr), ISNULL(v6.RowNr, v4.RowNr)


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  18. mic_lch83 New Member

    IT WORKED. THANKS A BUNCH!!
  19. mic_lch83 New Member

    DECLARE @Var4 TABLE (RowNr INT IDENTITY(1,1), PopTotal INT)
    DECLARE @Var6 TABLE (RowNr INT IDENTITY(1,1), PopTotal INT)

    INSERT INTO @Var4 (PopTotal)
    SELECT PopTotal FROM table WHERE revid = 11 AND variance = 4 ORDER BY PopTotal

    INSERT INTO @Var6 (PopTotal)
    SELECT PopTotal FROM table WHERE revid = 11 AND variance = 6 ORDER BY PopTotal

    SELECT V4.PopTotal, V6.PopTotal
    FROM @Var4 v4
    FULL JOIN @Var6 v6 ON v4.RowNr = v6.RowNr
    ORDER BY ISNULL(v4.RowNr, v6.RowNr), ISNULL(v6.RowNr, v4.RowNr)

    Back for more answers. The above works great with 2 columns. What if I have a lot of variance variables? How do I repeat the above code to accomodate as many as I want?
    For example, If I want to 11 different variance, I will pass one variable to tell the sql that I need 11 countries, and then, I will pass the 11 variables. How do I do that?
  20. Adriaan New Member

    Happy headaches!

    I would consider upgrading to SQL 2005, where you have direct syntax for cross-tab queries.

Share This Page