March 4, 2003 at 2:28 am
Hi
my table (sree)is having the fallowing schema
with sample data as given below
fldiddate value1value2item
112/12/20031020id1
212/13/20033040id3
now write a query to display in this manner
date value1value2
12/12/20031020
query --- select date,value1,Value2 from sree where date=12/12/2003
date value1value2
12/13/20033040
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
value1value2 value1value2
1020 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