Prev: BULK INSERT with Format File..
Next: Row_Number() Sorting
From: jtertin on 26 Mar 2010 13:00
I have a table with the following structure (plus a couple of other
unused columns):

dtDateTime; intID; intValue

There are multiple entries with the same dtDateTime value such as:
1/1/2010 00:00:10; 1, 10
1/1/2010 00:00:10; 2, 34
1/1/2010 00:00:10; 3, 23
1/1/2010 00:00:10; 4, 75

The goal is to get a result with the following structure:
dtDateTime; ID1; ID2; ID3; ID4...

Example results:
1/1/2010; 10; 34; 23; 75

One way to accomplish this is as follows:

SELECT T0.dtDateTime, T1.Val AS ID1 T2.Val AS ID2, T3.Val AS ID3,
T4.Val AS ID4
FROM (SELECT DISTINCT dtDateTime FROM tbDataTable) T0,
(SELECT dtDateTime, Val FROM tbDataTable WHERE intID=127) T1,
(SELECT dtDateTime, Val FROM tbDataTable WHERE intID=132) T2,
(SELECT dtDateTime, Val FROM tbDataTable WHERE intID=128) T3,
(SELECT dtDateTime, Val FROM tbDataTable WHERE intID=133) T4
WHERE T0.dtDateTime=T1.dtDateTime
AND T0.dtDateTime=T2.dtDateTime
AND T0.dtDateTime=T3.dtDateTime
AND T0.dtDateTime=T4.dtDateTime

Understandably, this query takes a long time to execute since there
are so many joins, but I cannot think of a more efficient way of
structuring the query?

Any suggestions?

I have a clustered index of the dtDateTime column and am considering
adding a non-clustered index to the intID column also.

Much appreciated.
 | 
Pages: 1
Prev: BULK INSERT with Format File..
Next: Row_Number() Sorting