question with a query

Table1

 sub-id  ref-id    Name
    1       1         Project 1
    2       1         Project 2  
    3       2         Project 3
    4       2         Project 4

Table2

 sub-id  ref-id    log_stamp      Recepient      log_type
    ----------------------------------------------------
    1     1       06/06/2011     person A       1
    1     1       06/14/2011     person B       2
    1     1       06/16/2011     person C       2
    1     1       06/17/2011     person D       3
    2     1       06/18/2011     person E       2
    2     1       06/19/2011     person F       2

    3     2       06/20/2011     person G       1
    4     2       06/23/2011     person H       3

Result

Name        ref-id    start_date    Recepient     latest_comment Recepient completion_date  Receipient
Project1    1         06/06/2011    person A      06/19/2011     person F      06/17/2011   person D
Project3    2         06/20/2011    person G      NULL           NULL          06/23/2011   person H


log_type of 1 stands f开发者_JS百科or start_date
log_type of 2 stands for latest_comment
log_type of 3 stands for completion_date
The Name of the project is just the name of the top-most name in the same group of ref-id

have tried this for now

;with T as (select
    Table2.ref-id, 
        Table2.log_stamp,
        Table2 log.log_type
        when 1 then '1'
        when 2 then '2'    
        when 3 then '3'

    end as title

from 
    Submission sb inner join submission_log log on Table1.[sub-id] = Table2.[sub-id]
)

select * from T
pivot (
    max(log_stamp)
    for title IN ([1],[2],[3],[5],[6],[9],[11])

---

I was unable to do it as a pivot, I dont think it is possible as described

DECLARE @table1 TABLE (sub_id INT, ref_id INT, name VARCHAR(50))
INSERT @table1 VALUES (1, 1, 'Project 1')
INSERT @table1 VALUES (2, 1, 'Project 2')   
INSERT @table1 VALUES (3, 2, 'Project 3' )
INSERT @table1 VALUES (4, 2, 'Project 4') 

DECLARE @Table2 TABLE (sub_id INT, ref_id INT, log_stamp DATETIME, recepient VARCHAR(10), logtype INT)

INSERT @table2 VALUES(1,1,'06/06/2011','person A',1) 
INSERT @table2 VALUES(1,1,'06/14/2011','person B',2) 
INSERT @table2 VALUES(1,1,'06/16/2011','person C',2)
INSERT @table2 VALUES(1,1,'06/17/2011','person D',3)
INSERT @table2 VALUES(2,1,'06/18/2011','person E',2)
INSERT @table2 VALUES(2,1,'06/19/2011','person F',2)
INSERT @table2 VALUES(3,2,'06/20/2011','person G',1)       
INSERT @table2 VALUES(3,2,'06/23/2011','person H',3) 

;WITH a as ( 
SELECT RN = ROW_NUMBER() OVER (PARTITION BY t1.sub_id, t1.ref_id, t1.name, t2.logtype ORDER BY log_stamp DESC), t1.sub_id, t1.ref_id, t1.name, t2.Recepient , t2.logtype ,log_stamp
FROM  @table1 t1 JOIN @table2 t2 ON t1.ref_id = t2.ref_id AND 
t1.sub_id = t2.sub_id),
b as (SELECT * FROM a WHERE RN = 1)
SELECT b1.name, b1.ref_id,b1.log_stamp start_date , b1.Recepient, b2.log_stamp latest_comment , b2.Recepient, b3.log_stamp completion_date , b3.Recepient
FROM b b1
LEFT JOIN b b2 ON b1.sub_id=b2.sub_id AND b1.ref_id = b2.ref_id AND b2.logtype = 2
LEFT JOIN b b3 ON b1.sub_id=b3.sub_id AND b1.ref_id = b3.ref_id AND b3.logtype = 3
WHERE b1.logtype = 1

Result:

name         ref_id      start_date              Recepient  latest_comment          Recepient  completion_date         Recepient
------------ ----------- ----------------------- ---------- ----------------------- ---------- ----------------------- ----------
Project 1    1           2011-06-06 00:00:00.000 person A   2011-06-16 00:00:00.000 person C   2011-06-17 00:00:00.000 person D
Project 3    2           2011-06-20 00:00:00.000 person G   NULL                    NULL       2011-06-23 00:00:00.000 person H