Need to select 2 columns with different requiremen

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?

Check CASE statement for your solution...

http://www.craigsmullins.com/ssu_0899.htm
http://www.devx.com/tips/Tip/15633

Mohammed U.

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.

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
***********************

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

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..

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


Mohammed U.

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

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.

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

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).

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

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.

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)

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.

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

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

IT WORKED. THANKS A BUNCH!!

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?

Happy headaches!

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