Q:

How to transpose a table with a variable but limited number of matching entries

I am working with the following table DDL:
 CREATE TABLE foo (
   globalkey INTEGER NOT NULL,   
   subkey INTEGER NOT NULL,   
   type VARCHAR(3) NOT NULL,   
   bar VARCHAR(4) NOT NULL 
  );

The table contains records of different type (e.g. "t1", "t2" and "t3"). The primary key of the table is (globalkey, subkey). For each globalkey there can be 1-n records some of them may share the same type. However there are only 0-3 number of records with type "t1".
I'd like to create a query that gives me all globalkeys that have at least one record with type "t1" and returns the 3 bar values as 3 different columns in the result.
So for the given data:
GLOBALKEY, SUBKEY, TYPE, BAR
1          1     t1     hit1
1          28    t1     hit1234
1          315   t2     miss
1          967   t1     hit4711
2          1     t5     miss
2          13    t5     miss
2          18    t1     hit9876
3          1     t2     miss

I'd like to get:
GLOBALKEY, BAR1,   BAR2,   BAR3
1,         "hit1", "hit1234", "hit4711"
3,         "hit9876",  NULL , NULL

I created a SQLFiddle under: http://sqlfiddle.com/#!9/04855
I'd greatly appreciate any pointer on how to get this done!
Bye,
Markus
UPDATE #1: I did not make it clear enough in my initial version that the column BAR contains any arbitrary values, so I can't use the exact version of @Mita statement as this assumes that the values in the case statements are fixed ('hit1', 'hit2', ...). However in the meantime I found out how this problem can be overcome by using the row_number() window function. Check out my edit of @Mita's answer. Updated the SQLFiddle accourdingly...

A:

The JSFiddle inserts are not with the data you gave in the example. The correct data should be:
INSERT INTO foo VALUES (1,1,'t1','hit1');
INSERT INTO foo VALUES (1,28,'t1','hit2');
INSERT INTO foo VALUES (1,315,'t2','miss');
INSERT INTO foo VALUES (1,967,'t1','hit3');
INSERT INTO foo VALUES (2,1,'t5','miss');
INSERT INTO foo VALUES (2,13,'t5','miss');
INSERT INTO foo VALUES (2,18,'t1','hit1');
INSERT INTO foo VALUES (3,1,'t2','miss');

And the query (if I understood you right) should be:
SELECT
    globalkey,
    MAX(CASE WHEN bar = 'hit1' THEN 'hit1' ELSE NULL END) AS bar1,
    MAX(CASE WHEN bar = 'hit2' THEN 'hit2' ELSE NULL END) AS bar2,
    MAX(CASE WHEN bar = 'hit3' THEN 'hit3' ELSE NULL END) AS bar3
FROM
    foo
WHERE
    type = 't1'
GROUP BY
    globalkey

