Sql Help! Anyone

[emcruz]

I have a table EmpAccess with EmpID (datatype BigInt) and in another field
(same table) have the access levels (datatype Int). So if an employee has more than 1 access level (which is possible up to 3 out of 156) there will be 1, 2, or 3 records for the same employee. I am trying to show the resultset per employee.

This is what I have:

empID accesslevel
1234 102
1325 100
1234 100
1234 111
1222 123

This is what I need my result set from a Select statement to look like:

empID accesslevel1 accesslevel2 accesslevel3
1234 102 100 111
1325 100
1222 123


Thanks,

Eric Cruz

[ronverdonk]

I wouldn't know how to accomplish this with pure MySql, but I can do it in PHP and MySql. Next example will show this:
[PHP]
This is how test table 'testit' looks:
| id | emp | lvl |
+----+------+------+
| 1 | 1234 | 1111 |
| 2 | 1235 | 1111 |
| 3 | 1236 | 2222 |
| 5 | 1234 | 1212 |
| 6 | 1236 | 1212 |
| 7 | 1234 | 1313 |
| 8 | 1235 | 8888 |
+----+------+------+
The code to accomplish your request something like this:
$conn = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS)
or die('Cannot connect to database. ' . mysql_error());
mysql_select_db(SQL_DB, $conn);

$query = "SELECT emp, lvl FROM testit";
$links = mysql_query($query) or die(mysql_error());

while($line = mysql_fetch_array($links, MYSQL_ASSOC))
$result[$line[emp]].= ' '.$line[lvl];

while(list($key,$value)=each($result))
for ($i=0; $i<count($value);$i++)
echo "$key :".$value."<br />";

And the result of this snippet is this:
1234 : 1111 1212 1313
1235 : 1111 8888
1236 : 2222 1212
[/PHP]
Hope this helps you.

Ronald :cool: