sql server programming:
Thanks for your responses. Well the time can be same and cannot be same For ex: Table1 (timestamp,readingA) (2/2/2005 12:09:26,123) (2/2/2005 12:19:26,324) Table2 (timestamp,readingB) (2/2/2005 12:09:26,321) (2/2/2005 12:29:26,657) Table3 (timestamp,readingC) (2/2/2005 12:09:26,231) (2/2/2005 12:39:26,987) Result Desired (timestamp,readingA,readingB,readingC) 2/2/2005 12:09:26,123,321,231 2/2/2005 12:19:26,324,, 2/2/2005 12:29:26,,657, 2/2/2005 12:39:26,,,987 AS you can see the timestamp which is common in all tables is displayed once alongwith its readings, but the one which is not common is displayed alongwith its readings while other readings are blank.
This is a typical xtab problem. Here is a trick. select timestamp,max(case when tb=1 then reading else 0 end) readingA, max(case when tb=2 then reading else 0 end) readingB, max(case when tb=3 then reading else 0 end) readingC from( select *,1 from tb1 union all select *,2 from tb2 union all select *,3 from tb3) derived(timestamp,reading,tb) group by timestamp Do check out these: http://support.microsoft.com/kb/175574 or http://www.sqlteam.com/item.asp?ItemID=2955 or http://www.rac4sql.net/ -- -oj [quoted text, click to view] "Pradeep" <agarwalp@eeism.com> wrote in message
news:364c5b9b.0502022352.7cddb731@posting.google.com...
> Thanks for your responses.
>
> Well the time can be same and cannot be same
> For ex:
>
> Table1
> (timestamp,readingA)
> (2/2/2005 12:09:26,123)
> (2/2/2005 12:19:26,324)
>
> Table2
> (timestamp,readingB)
> (2/2/2005 12:09:26,321)
> (2/2/2005 12:29:26,657)
>
> Table3
> (timestamp,readingC)
> (2/2/2005 12:09:26,231)
> (2/2/2005 12:39:26,987)
>
> Result Desired
> (timestamp,readingA,readingB,readingC)
> 2/2/2005 12:09:26,123,321,231
> 2/2/2005 12:19:26,324,,
> 2/2/2005 12:29:26,,657,
> 2/2/2005 12:39:26,,,987
>
> AS you can see the timestamp which is common in all tables is
> displayed once alongwith its readings, but the one which is not common
> is displayed alongwith its readings while other readings are blank.
>
> I hope now it is clear
[quoted text, click to view] On 2 Feb 2005 23:52:26 -0800, Pradeep wrote:
>Thanks for your responses.
>
>Well the time can be same and cannot be same
>For ex:
>
>Table1
>(timestamp,readingA)
>(2/2/2005 12:09:26,123)
>(2/2/2005 12:19:26,324)
>
>Table2
>(timestamp,readingB)
>(2/2/2005 12:09:26,321)
>(2/2/2005 12:29:26,657)
>
>Table3
>(timestamp,readingC)
>(2/2/2005 12:09:26,231)
>(2/2/2005 12:39:26,987)
>
>Result Desired
>(timestamp,readingA,readingB,readingC)
>2/2/2005 12:09:26,123,321,231
>2/2/2005 12:19:26,324,,
>2/2/2005 12:29:26,,657,
>2/2/2005 12:39:26,,,987
>
>AS you can see the timestamp which is common in all tables is
>displayed once alongwith its readings, but the one which is not common
>is displayed alongwith its readings while other readings are blank.
Hi Pradeep, Try if this accomplishes what you want: SELECT COALESCE (A."timestamp", B."timestamp", C."timestamp"), A.readingA, B.readingB, C.readingC FROM Table1 AS A FULL OUTER JOIN Table2 AS B ON B."timestamp" = A."timestamp" FULL OUTER JOIN Table3 AS C ON C."timestamp" = COALESCE (A."timestamp", B."timestamp") (untested) By the way, I suggest you use another name for the timestamp column, as timestamp is a reserved word in SQL Server. Best, Hugo --
Don't see what you're looking for? Try a search.
|
