Turning Key-Value Pairs Into Columns for Reporting

The settings table accepts a new attribute easily, but the report needs a new column. Key-value pairs move that work from storage design into reporting logic. A fixed projection with explicit conversions gives you readable output and exposes the values that need cleanup.

Define One Value per Entity and Attribute

Use one entity table and one attribute table for this demonstration. The composite primary key allows one current value for each entity and attribute name. That is an important reporting rule. If several values are allowed, define their ordering or collection semantics rather than letting MAX choose whichever text sorts last.

CREATE TABLE #Device(DeviceID int NOT NULL PRIMARY KEY,DeviceName nvarchar(60));
CREATE TABLE #Attribute
(
    DeviceID int NOT NULL,
    AttributeName varchar(40) NOT NULL,
    AttributeValue nvarchar(100) NULL,
    PRIMARY KEY(DeviceID,AttributeName)
);
INSERT #Device VALUES(1,N'North'),(2,N'South'),(3,N'West');
INSERT #Attribute VALUES
    (1,'Color',N'Blue'),(1,'Capacity',N'250'),(1,'Enabled',N'1'),
    (2,'Color',N'Green'),(2,'Capacity',N'unknown'),(2,'Enabled',N'0');

Run the examples together in one session. A permanent implementation also needs a foreign key to the entity table and a governed attribute catalog. That catalog should define names, expected types, allowed values, and ownership. Flexibility without ownership eventually gives you Color, Shade, and PreferredHue describing the same idea with three different names.

Turn Key-Value Pairs Into Columns With Conditional Aggregation

Conditional aggregation selects the attribute for each report column. The left join keeps entities with no attributes. MAX collapses the single matching value, and GROUP BY returns one row per entity. The primary key makes that collapse deterministic. Keep type conversion outside or inside the aggregate according to the validation behavior you want.

SELECT d.DeviceID,d.DeviceName,
       MAX(CASE WHEN a.AttributeName='Color' THEN a.AttributeValue END) AS Color,
       TRY_CONVERT(int,MAX(CASE WHEN a.AttributeName='Capacity' THEN a.AttributeValue END)) AS Capacity,
       TRY_CONVERT(bit,MAX(CASE WHEN a.AttributeName='Enabled' THEN a.AttributeValue END)) AS Enabled
FROM #Device AS d
LEFT JOIN #Attribute AS a ON a.DeviceID=d.DeviceID
GROUP BY d.DeviceID,d.DeviceName
ORDER BY d.DeviceID;

I prefer this form when each field has its own conversion or business rule. You can see exactly which stored name supplies each column. A missing attribute yields NULL. An invalid integer also yields NULL after TRY_CONVERT. Those are different data conditions, so the display query needs a separate validation report before someone treats both as simply unavailable.

Keep Missing and Invalid Values Distinct

The next query shows the stored value alongside a status. It distinguishes an absent row, a stored null, and an invalid integer. Empty strings also need an explicit policy, because TRY_CONVERT turns an empty string into 0 for int and bit. SQL Server conversion rules are not identical to your business rules, so normalize blanks and reject anything outside the intended input contract before calculating a total.

SELECT d.DeviceID,a.AttributeValue AS RawCapacity,
       TRY_CONVERT(int,NULLIF(LTRIM(RTRIM(a.AttributeValue)),N'')) AS Capacity,
       CASE WHEN a.DeviceID IS NULL THEN N'Missing attribute'
            WHEN a.AttributeValue IS NULL THEN N'Stored null'
            WHEN NULLIF(LTRIM(RTRIM(a.AttributeValue)),N'') IS NULL THEN N'Blank value'
            WHEN TRY_CONVERT(int,a.AttributeValue) IS NULL THEN N'Invalid integer'
            ELSE N'Valid integer' END AS CapacityStatus
FROM #Device AS d
LEFT JOIN #Attribute AS a
  ON a.DeviceID=d.DeviceID AND a.AttributeName='Capacity';

TRY_CONVERT protects a report from a conversion exception. It does not certify that a capacity is positive, within range, or measured in the correct unit. Add those checks explicitly. Bit conversion accepts nonzero numeric inputs as true, so a strict zero or one flag needs its own validation. A cast cannot negotiate a business rule for you.

Use PIVOT for the Same Fixed Fields

PIVOT expresses the column rotation directly. Limit the source to DeviceID, AttributeName, and AttributeValue. Extra source columns become grouping dimensions and can produce more than one output row per entity. The fixed IN list defines the result schema. Missing keys remain NULL, and attributes outside that list do not appear as new columns.

WITH shaped AS
(
    SELECT DeviceID,AttributeName,AttributeValue
    FROM #Attribute
    WHERE AttributeName IN('Color','Capacity','Enabled')
), pivoted AS
(
    SELECT DeviceID,[Color],[Capacity],[Enabled]
    FROM shaped
    PIVOT(MAX(AttributeValue)
          FOR AttributeName IN([Color],[Capacity],[Enabled])) AS p
)
SELECT d.DeviceID,d.DeviceName,p.Color,
       TRY_CONVERT(int,p.Capacity) AS Capacity,
       TRY_CONVERT(bit,p.Enabled) AS Enabled
FROM #Device AS d
LEFT JOIN pivoted AS p ON p.DeviceID=d.DeviceID
ORDER BY d.DeviceID;

Compare both result sets by entity and field. Neither syntax is inherently the faster option for every workload. Inspect actual plans and IO on representative data. Conditional aggregation is convenient for varied expressions. PIVOT is concise when several fields share one aggregate. Pick the one your team can review correctly, then measure rather than deciding from the keyword's appearance.

Index Key-Value Pairs for the Access Direction

The primary key begins with DeviceID, which helps read attributes for selected devices. A report that starts with an attribute name and filters its value needs a different access path. This second index leads with AttributeName, then DeviceID, and includes the stored value. Test whether its read benefit justifies the extra storage and write work.

CREATE INDEX IX_Attribute_Name_Device
ON #Attribute(AttributeName,DeviceID) INCLUDE(AttributeValue);
SELECT DeviceID,AttributeValue
FROM #Attribute
WHERE AttributeName='Color' AND AttributeValue=N'Blue';

I inspect which side drives the query before proposing an index. A device detail screen and an all-device attribute report need different reads. Repeated TRY_CONVERT predicates on text values do not provide the same type specific access as a real numeric column. Large scans caused by typed range filters are a signal to reconsider the storage contract, not just add another text index.

Resist an Uncontrolled Dynamic Column List

A fixed report should usually have a fixed column schema. Generating every discovered attribute into a column changes the result when somebody inserts a new name. That can break export consumers and dashboards. If dynamic reporting is required, define an approved attribute list, validate identifier lengths, quote identifiers, and keep filter values parameterized.

Which attributes does your reader actually need for this decision? A report with every miscellaneous setting becomes hard to interpret. Preserve a documented stable projection for operational reporting, even when an exploratory tool offers dynamic attributes. Never concatenate a user supplied name or value into SQL text merely because the source table calls it metadata.

Move Important Key-Value Pairs Into Real Columns

An attribute used in joins, foreign keys, strict validation, ordering, and range searches has earned stronger typing. Add a real column or a related typed table when those requirements become central. This improves constraints, statistics, and predictable query shape. Key-value pairs remain useful for sparse optional metadata whose meanings and retrieval patterns are genuinely flexible.

Plan the conversion as a migration. Inventory distinct stored values, reject invalid entries, and backfill typed values with an auditable mapping. Define which source is authoritative while old and new code coexist. A temporary dual write arrangement needs verification and an end date. Otherwise the application inherits two settings that disagree and calls the mismatch flexibility.

Validate the Output as a Contract

Test entities with every key, no keys, stored nulls, blanks, malformed numeric text, and unexpected names. Confirm the output has exactly one row per intended entity. Check case and collation rules for attribute names. Decide whether color and Color refer to the same attribute before loading data, rather than discovering the answer through a duplicate key error.

I reconcile raw values and converted values before a report becomes an operational source. Save the chosen attribute definitions with the report release. The projection should make ordinary data easy to read while keeping invalid data visible through a companion exception query. Flexible storage becomes useful reporting when the types, missing values, and result schema are explicit.

Related reading on this blog: Exploring PIVOT and UNPIVOT and Example of PIVOT UNPIVOT Cross Tab Query in Different SQL Server Versions.

A reporting column is not just a rotated value, it is a defined meaning with a type.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.