I have two tables: Product Table ID (PK), Description, CategoryID, SegmentID, TypeID, SubTypeID, etc. Attribute Table ID (PK), ProductID (FK), Key, Value And I would like to query these two ...

SQL Key Value Pair Query

I have two tables:

Product Table

ID (PK), Description, CategoryID, SegmentID, TypeID, SubTypeID, etc.

Attribute Table

ID (PK), ProductID (FK), Key, Value

And I would like to query these two tables in a join that returns 1 row for each product with all of the Key/Value pair records in the Attribute table returned in a single column, perhaps separated by a pipe character (Key1: Value1 | Key2: Value2 | Key3: Value3 | etc.). Each product could have a different number of key/value pairs, with some products have as few as 2-3 and some having as many as 30. I would like to figure out how to get the query results to look something like this (perhaps selected into a new table):

product.ID, product.Description, [special attributes column], product.CategoryID, product.SegmentID, etc.

example result:

65839, "WonderWidget", "HeightInInches: 26 | WeightInLbs: 5 | Color: Black", "Widgets", "Commerical"

Conversely, it would be helpful to figure out how to take the query results, formatted as mentioned above, and push them back into the original Attribute table. For example, if we output the query above into a table where the [special attributes column] was modified (values updated/corrected by a human), it would be nice to know how to use the table containing the [special attributes column] to update the original Attribute table. I think for that to be possible, the Attribute.ID field would need to be included in the query output.

In the end, what I am trying to accomplish is way to export the Product and Attribute data out to 1 row per product with all the attribute data so that it can be reviewed/updated/corrected by a human in something as simple as an Excel file, and then pushed back into SQL. I think I can figure out how to do all of that once I get over the hurdle of figuring out how to get the products and attributes out as one row per product. Perhaps the correct answer is to pivot all of the attributes into columns, but I’m afraid the query would be incredibly wide and wasteful. Open to suggestions for this as well. Changing to a document type database is not an option right now; need to figure out the best way to handle this in relational SQL.

Answer

You first need to group the Key value pairs. This can be achieved using a concat operatoor like ||, you need to think about nulls as well. NUll concatenated with NULL is still NULL in most DBs.

SELECT ProductID, Key || ':' || Value as KeyValue FROM AttributeTable

Then you would need to group those using an aggregating function like STRING_AGG (Assuming SQL Server above 2017). Other databases have different aggregate functions Mysql f ex uses GROUP_CONCAT

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017 https://www.geeksforgeeks.org/mysql-group_concat-function/

SELECT ProductID, STRING_AGG( Key || ':' || Value, '|') as Key Value FROM AttributeTable GROUP BY ProductId

I can expand on the answer if you can provide more information.

Advertisement

Answer