SQL Help Please

[N_R_D]

I use sql analyzer from Microsoft....

here is what I need help with


Loan number....................Call type
123456................................120
123456................................118


I need a way to show this


Loan Number......Type1......Type2
1233456..............120..........118


How ?

[baileys]

I'm not sure if SQL statements will work with what you have however essentially you need to run two statements. The first will pull out a list of distinct Loan numbers, ie. just once instance of them. Then you take that list and for each number in it you run a second statement pulling out all call types where the loan number is the number you're looking at.

in pseudo code with a bit of SQL in there I would do (I could write it in PHP if it's easier or you have access to that)

retrieve all loan numbers in a distinct list ie SELCT DISTINCT loan number FROM table
for each loan number run the following:
retrieve all call types where the loan number equals the loan number you have ie.
SELECT call type FROM table WHERE loan number = retrieved loan number
end the previous table row and start a new table row
print the loan number in the Loan number table cell
for each call type retrieved
print the call type in a table cell

[N_R_D]

That is basically what I have now, to get the top listing there..

what I need is after that, is to make it all onw one row.

[baileys]

What I wrote was for the second line. In PHP you would use...

<table>
<tr>
 <th scope="col">Loan Number</th>
 <th scope="col">Type 1</th>
 <th scope="col">Type 2</th>
 etc.

<?php
$sql = mysql_query("SELECT DISTINCT loan_number FROM table");

while ($loandetail = mysql_fetch_object($sql)) {
  $sql2 = mysql_query("SELECT call_type FROM table WHERE loan_number = '$loandetail->loan_number'");

  print "</tr>";
  print "<tr>";
  print "<td>".$loandetail->loan_number."</td>";
  
  while ($call = mysql_fetch_object($sql2)) {
    print "<td>".$call->call_type."</td>";
  }
}
?>
</tr>
</table>

You'd have to either limit the number of table rows or create a typical table ie. print the number of columns required on the fly.

[N_R_D]

what is the -> ??

[baileys]

the arrow is just a way to access the detail with the function - mysql_fetch_object() used. The function is just my preferred method of accessing database table results, it's easier to read at a glance, requires less code that other methods such as mysql_fetch_array() and is better to use.

http://www.php.net/mysql_fetch_object will explain it in detail.

[N_R_D]

Very cool... very cool indeed. But I still can not get this to work right outside of PHP