March 4, 2003 at 2:28 am
Hi
my table (sree)is having the fallowing schema
with sample data as given below
fldid date value1 value2 item
1 12/12/2003 10 20 id1
2 12/13/2003 30 40 id3
now write a query to display in this manner
date value1 value2
12/12/2003 10 20
query --- select date,value1,Value2 from sree where date=12/12/2003
date value1 value2
12/13/2003 30 40
query --- select date,value1,Value2 from sree where date=12/13/2003
now if u write 2 sqls using unions..etc get
in two rows
but i want u to write only one sql so that u display in one row in this manner
value1 value2 value1 value2
10 20 30 40
both sql conditions should be specfied in one sql only
Can any one solve?
March 4, 2003 at 3:57 am
It can be solved. Usually this type of item means a ivot table. The question is it however th values relate. Is it related on item and does item have a fixed number of possiblites or is it impossible to know for sure? There has to be some relation that will make sense in order to build a query that will work beyond just this once.
If is however just this once then
SELECT
SUM(case fldid when 1 then value1 else 0 end) value11,
SUM(case fldid when 1 then value2 else 0 end) value21,
SUM(case fldid when 2 then value1 else 0 end) value12,
SUM(case fldid when 2 then value2 else 0 end) value22
from sree
as I don't see date in the output this will do it in a single query.
March 4, 2003 at 4:03 am
Try this:
SELECT A.value1,A.value2,B.value1,B.value2 FROM
sree A
CROSS JOIN
sree B
WHERE A.[date] = '2003-12-12' AND B.[date] = '2003-12-13'
'date' is a reserved T-SQL keyword hence it is good practice to use []
Using 'datetime' constants at run time could be a problem in the burgeoning. A date string that works for the date formats expected by one language may be unrecognizable if the query is executed by a connection using a different language and date format setting.
H. Lindgren
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply