Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I think the best way is either do a join against an UNPIVOT, or do a cross-join of Table2 against the numbers with a CASE statement to pull out the columns. Neither way is always a clear winner, despite how nice the UNPIVOT syntax can be.</p> <p>Sometimes the cross-join outperforms UNPIVOT and sometimes the other way around. Here is the cross-join method, which works even in SQL 2000:</p> <pre><code>SELECT A.date, A.name, A.hour, A.amount, A.price, result = CASE WHEN A.amount &gt; 0 THEN 'positive' WHEN A.amount &lt; 0 THEN 'negative' END, actualsold = CASE A.hour WHEN 12 THEN B.H12 WHEN 16 THEN B.H16 END FROM Table1 A INNER JOIN Table2 B ON A.date = B.date and A.name = B.name </code></pre> <p>This is an implicit partial cross-join because Table1 has many rows per date &amp; name but Table2 has only one row per date &amp; name. So each row in Table2 gets repeated many times in the join, but we use a different column from each one.</p> <p>Unpivoting would be similar, putting the UNPIVOT query in a derived table or CTE:</p> <pre><code>SELECT A.date, A.name, A.hour, amount, A.price, result = CASE WHEN A.amount &gt; 0 THEN 'positive' WHEN A.amount &lt; 0 THEN 'negative' END, B.actualsold FROM Table1 A INNER JOIN ( SELECT * FROM (SELECT date, name, [12] = H12, [16] = H16 FROM Table2) X UNPIVOT (actualsold FOR hour IN ([12], [16])) U ) B ON A.date = B.date and A.name = B.name AND A.hour = B.hour </code></pre> <p>The execution plans for the two queries above are very similar, though the cross-join method is slightly simpler, but this doesn't prove anything about actual performance until tried against real data in full tables with many rows. The UNPIVOT does an extra LEFT LOOP JOIN that the cross-join method doesn't.</p> <p>One potential drawback of the UNPIVOT method is that if you want to specify criteria to limit date ranges and names, you'll probably need to do it in two places so the server doesn't unpivot the whole dang table (though it <em>might</em> be smart enough not to do this, I don't know). While the big 24-condition CASE statement is unwieldy, UNPIVOT has its own awkwardness to convert the column names to just numbers.</p> <p>Here is the setup code for anyone that would like to see these queries in action:</p> <pre><code>USE tempdb CREATE TABLE Table1 ( date smalldatetime, name varchar(10), hour int, amount int, price decimal(15,2) ) CREATE TABLE Table2 ( date smalldatetime, name varchar(10), H12 decimal(15,2), H16 decimal(15,2), ) INSERT Table1 VALUES ('20091012', 'tom', 12, 20, 15.43) INSERT Table1 VALUES ('20091013', 'fred', 16, -10, 6.98) INSERT Table2 VALUES ('20091012', 'tom', 15.75, 0) INSERT Table2 VALUES ('20091013', 'fred', 0, 12.54) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POget column data in stored procedure when schema is reversed
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USErikE
    1. USErikE
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POget column data in stored procedure when schema is reversed
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COI like this. clean and fast. worked great. Thanks!
      singulars
      1. PO
      1. USnelsonwebs
    2. CO@nelsonwebs: Thanks! Which method did you use? If you tried them both, did one perform better (have to use SQL profiler to measure, not execution plan)?
      singulars
      1. PO
      1. USErikE
    3. COI did the cross join shown in the first example. Haven't tried the other yet or compare results. Will update if I get that far.
      singulars
      1. PO
      1. USnelsonwebs
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload