Combining fields in a pivot query

Question

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

Title 
PageNo
LineNo
Key
Value
Units

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

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:

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

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

But that messes up my result set

Can anyone point me in the right direction?

Edit:

Sample data: ________________________________ Title Key Value Units ------------------------------------- Title1 Field_1 4000 lbs Title1 Field_2 150 pages Title1 Field_3 200 ml Title2 Field_2 300 pages Title3 Field_1 350 lbs Title3 Field_3 55 ml

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

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

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

Answer 1

I actually found a rather simple solution - I concatenate Value and Unit with "/" and then I split on the "/" in the select list using CHARINDEX.

SELECT Title,
SUBSTRING([Field_1],0,CHARINDEX('/',[Field_1])) AS [Field_1],
SUBSTRING([Field_1],CHARINDEX('/',[Field_1])+1,LEN([Field_1]-1) AS[Field_1Units], 
SUBSTRING([Field_2],0,CHARINDEX('/',[Field_2])) AS [Field_2],
SUBSTRING([Field_2],CHARINDEX('/',[Field_2])+1,LEN([Field_2]-1) AS [Field_2Units], 
SUBSTRING([Field_3],0,CHARINDEX('/',[Field_3])) AS [Field_3],
SUBSTRING([Field_3],CHARINDEX('/',[Field_3])+1,LEN([Field_3]-1) AS [Field_3Units]
FROM
(SELECT Title, [Key],IIF([Units]<>'',[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

Answer 2

You can try using Aliases for the field names

SELECT
    Title,
    [Field_1] AS [Field_1 (Units)],
    [Field_2] AS [Field_2 (Units)],
    [Field_3] AS [Field_3 (Units)]
FROM
    (SELECT Title,
            [Key],
            IIF(NOT [FieldUnits] IS NULL,
                [Value] + ' ' +[Units],    
                [FieldValue]) 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

Your second query has the concatenated value. Just fix the error by adding a key column.

SELECT
    Title,
    [Field_1] AS [Field_1 (Units)],
    [Field_2] AS [Field_2 (Units)],
    [Field_3] AS [Field_3 (Units)]
FROM
    (SELECT Title,
            [Key],
            ISNULL([Key],'') + ISNULL(' (' + [Value] + ')','') AS KeyValue
      FROM  Table1 
      WHERE [Key] LIKE 'Field_%'
    ) AS Data 
PIVOT( 
    MAX([KeyValue]) 
    FOR [Key] IN ([Field_1],[Field_2],[Field_3]) ) As Piv
ORDER BY
    Title

here's some information to get your started with dynamic pivot.

DECLARE @FieldNames VARCHAR(MAX)
SELECT @FieldNames = COALESCE(@FieldName + ',','') + '[' + Key + ' (' + Units + ')]'   
FROM   Table1

DECLARE @Sql VARCHAR(MAX) = CONCAT(
     ' SELECT Title, ' + @FieldName
    ,' FROM (your subquery.. needs to have Key + ' (' + Units + ')' As Key ) AS sq'
    ,' PIVOT ('
    ,'      MAX(KeyValue)'
    ,'      FOR Key in (' + @FieldNames + ')'
    ,' AS p')

EXEC(@Sql)      

Answer 3

When you need to pivot more than one column, you might find the pre-PIVOT pivoting method of grouping and conditional aggregation simpler and more maintainable. Judge for yourself:

SELECT
  Title,
  Field_1       = MAX(CASE [Key] WHEN 'Field_1' THEN Value END),
  Field_1Units  = MAX(CASE [Key] WHEN 'Field_1' THEN Units END),
  Field_2       = MAX(CASE [Key] WHEN 'Field_2' THEN Value END),
  Field_2Units  = MAX(CASE [Key] WHEN 'Field_2' THEN Units END),
  Field_3       = MAX(CASE [Key] WHEN 'Field_3' THEN Value END),
  Field_3Units  = MAX(CASE [Key] WHEN 'Field_3' THEN Units END)
FROM
  dbo.Table1
GROUP BY
  Title
WHERE
  [Key] LIKE 'Field_%'
;