select group by with multiple AND conditions on the same columns
You can use INTERSECT which is available in both HSQLDB and SQL Server

SELECT SERVICE_SID
FROM SQLTable
WHERE attribute_sid = 5 AND Value='VAL1'
INTERSECT
SELECT SERVICE_SID
FROM SQLTable
WHERE attribute_sid = 4 AND Value='VAL8';


You can then join the result of this query with your table and group by SERVICE_SID