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

Open 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

Open 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

Open 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      

Open in new window



Second one probably might not work if the units for the same column are not always the same
LVL 35
YZlatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
--first one
SELECT
    Title,
    MAX(CASE WHEN [Key] = 'Field1' THEN [Value] ELSE '' END) AS Field1,
    MAX(CASE WHEN [Key] = 'Field1' THEN [Units] ELSE '' END) AS Units,
    MAX(CASE WHEN [Key] = 'Field2' THEN [Value] ELSE '' END) AS Field2,
    MAX(CASE WHEN [Key] = 'Field2' THEN [Units] ELSE '' END) AS Units,
    MAX(CASE WHEN [Key] = 'Field3' THEN [Value] ELSE '' END) AS Field3,
    MAX(CASE WHEN [Key] = 'Field3' THEN [Units] ELSE '' END) AS Units
FROM Table1
WHERE [Key] LIKE 'Field_%'
GROUP BY Title
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
--second one (not fully tested)
SELECT
    Title,
    MAX(CASE WHEN [Units] IN ('lb', 'lbs', 'pounds') THEN [value] ELSE '' END) AS [Field1 (lbs)],
    MAX(CASE WHEN [Units] IN ('page', 'pages', 'pgs') THEN [value] ELSE '' END) AS [Field2 (pages)],
    MAX(CASE WHEN [Units] IN ('milliliter', 'milliliters', 'ml', 'mls') THEN [value] ELSE '' END) AS [Field3 (ml)]
FROM Table1
WHERE [Key] LIKE 'Field_%'
GROUP BY Title
0
YZlatAuthor Commented:
Thanks Scott! This is great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.