July 20, 2010 at 4:25 am
Hi!
I have two selects with two different conditions, like:
Select Jan, Feb, Mar, etc
From Table1
Where condition1
Select Jan2, Feb2, Mar2, etc
From Table2
Where condition2
But what i need is ONE select where some columns follow condition1 and the others follow condition2, like:
select jan, jan2, feb, feb2, mar, mar2, etc
from table1
where ...
i need to make jan, feb, mar, etc follow condition1
and the others follow condition2
Is this possible? Can it be done?
July 20, 2010 at 4:54 am
You can use JOINS , specifically CROSS JOINS to acheive that.. but i have a query, any columns that are common to both?
July 20, 2010 at 5:02 am
No, none at all.
I mean, they actually are the same columns repeated, but as the data will be different (cause of different conditions) i intend to name them differently.
July 20, 2010 at 5:19 am
Then i guess, CROSS JOIN will help you, as in
WITH CTE1 as
(
select Jan, Feb, Mar,
RN = ROW_NUMBER() OVER(ORDER BY (SELECT 0))
from TABLE where Conditon 1
),
CTE2 as
(
select Jan2, Feb2, Mar2,
RN = ROW_NUMBER() OVER(ORDER BY (SELECT 0))
from TABLE where Conditon 2
)
select t1.Jan , t2.Jan2 , t1.Feb, t2.Feb2 , t3.Mar, t3.Mar2
from
CTE1 t1
CROSS JOIN
CTE2 t2
where t1.RN = t2.RN
Tell us if that helped you..
July 20, 2010 at 5:23 am
If there are diffrent number of rows getting returned by the 2 queries, then u please remove the "WHERE" clause after the cross join..
July 20, 2010 at 7:29 am
That helped alot, but i don't quite have what i need. I forgot to mention, i do have other two columns which are relevant, so i made it like this:
WITH CTE1 as
(
select no, name, Jan, Feb, Mar,
RN = ROW_NUMBER() OVER(ORDER BY (SELECT 0))
from TABLE where Conditon 1
),
CTE2 as
(
select no, name, Jan2, Feb2, Mar2,
RN = ROW_NUMBER() OVER(ORDER BY (SELECT 0))
from TABLE where Conditon 2
)
select no, name, t1.Jan , t2.Jan2 , t1.Feb, t2.Feb2 , t3.Mar, t3.Mar2
from
CTE1 t1
CROSS JOIN
CTE2 t2
where t1.name = t2.name
The problem i have is that the second SELECT returns less rows, so what happens is that many rows which have data from the first SELECT are lost...
Can this be solved?
EDIT: If i remove the where clause, i get more than 250 000 rows, when i should be getting 'only' about 3000 which is the number of rows of the first SELECT
July 20, 2010 at 10:01 am
This is pretty much a stab in the dark - which is all that can be done without more details as per Joe's post.
SELECT
[no],
name,
Jan = SUM(CASE Condition1 THEN Jan ELSE 0 END),
Feb = SUM(CASE Condition1 THEN Feb ELSE 0 END),
Mar = SUM(CASE Condition1 THEN Mar ELSE 0 END),
Jan2 = SUM(CASE Condition2 THEN Jan ELSE 0 END)
Feb2 = SUM(CASE Condition2 THEN Feb ELSE 0 END),
Mar2 = SUM(CASE Condition2 THEN Mar ELSE 0 END)
FROM (
SELECT
[no],
name,
Jan,
Feb,
Mar
FROM TABLE
WHERE Condition1 OR Condition2
) d
GROUP BY [no], name
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply