Need help with restructuring the pivot and concatenating fields

I have a table of the key-value structure with the following fields:

Title         varchar
PageNo   int
LineNo    int
Key          varchar
Value       varchar
Units       varchar

I have the following pivot query that works great:

SELECT * FROM
(SELECT Title, [Key],IIF(NOT [Units] IS NULL,[Value] + ' ' +[Units],    
[Value]) AS ValueUnits FROM Table1 WHERE [Key] LIKE 'Field_%') as Data 
PIVOT(
MAX([ValueUnits])
FOR [Key] IN([Field_1],[Field_2],[Field_3])) As Piv
ORDER BY Title

Select allOpen in new window

I get results as:
Title   Field_1   Field_2   Field_3
-------------------------------------

How do I need to change my pivot query in order to get something like this (where "Units" is the value that comes from the table field called "Units":

Title   Field_1 (Units)   Field_2(Units)   Field_3(Units)
----------------------------------------------------------

and/or something like this:

Title   Field_1   Units   Field_2  Units   Field_3  Units

in my resultset

I tried:

SELECT * FROM
(SELECT Title, [Key] + '(' + [Units] + ')' As KeyAndUnits,[Value] 
FROM Table1 WHERE [Key] LIKE 'Field_%') as Data 
PIVOT(
MAX([Value])
FOR [Key] IN([Field_1],[Field_2],[Field_3])) As Piv

Select allOpen in new window

But that gives me an error "Invalid column name [Key]"

I also tried:

SELECT * FROM
(SELECT Title, [Key], [Key] + '(' + [Units] + ')' As KeyAndUnits,[Value]     
FROM Table1 WHERE [Key] LIKE 'Field_%') as Data 
PIVOT(
MAX([Value])
FOR [Key] IN([Field_1],[Field_2],[Field_3])) As Piv

Select allOpen in new window

But that messes up my result set by displaying each value on a separate line (aka 3 rows with one value per each row instead of all 3 values in one row)

Can anyone point me in the right direction?


Sample Output:

    Title     Field_1    Units     Field_2     Units     Field3     Units
    ------------------------------------------------------------------------------------
    Title1    4000        lbs       150          pages       200       ml
    Title2                         300          pages     
    Title3     350        lbs                                 55       ml



and

[code]
    Title     Field_1 (lbs)         Field_2 (pages)        Field3 (ml)
    -----------------------------------------------------------------------------
    Title1    4000      	       150      	            200       
    Title2                             300          
    Title3     350                                                  55      

Select allOpen in new window

Second one probably might not work if the units for the same column are not always the same