If you know most of the attributes your products will have ahead of time, you could hard-code them into the tables for different product types, such as:
books
-----
id
price
title
author
isbn
etc....
mobile_device
-------------
id
price
size
colour
model
etc...
You could also try something like this:
base_product
------------
id
base_price
product_type
book_product_attributes
-----------------------
base_product_id (FK to base_product)
title
author
mobile_dev_product_attributes
-----------------------------
base_product_id (FK to base_product)
model
colour
This way, you can group your common attributes in a base product table, and more specific attributes are stored in other tables when necessary.
This will work if your product types are mostly static. On the other hand, if you have no idea what product types or attributes you'll need in the future, and entity-attribute-value based system might work better. The idea behind that model is that you have a table for the different attributes you might have, a table for the different entities in your system, and a table for the attribute values that an entity has. Example:
entities
--------
id
attributes
----------
id
name
values
------
id
entity_id
attribute_id
value
The data might look like this:
entities
ID
----
1
attributes
ID | name
----------
1 | title
2 | author
values
ID | attribute_id | entity_id | value
---------------------------------------------------------
1 | 1 | 1 | "Great Expectations"
2 | 2 | 1 | "Charles Dickens"
This data very briefly describes a product which has two attributes: title, and author, with the valus "Great Expectations" and "Charles Dickens", respectively.
Be aware that using an EAV database can make queries very awkward. With enough data and the wrong model design, it's also possible to run into performance problems. The example I gave was very simple but more realistic designs tend to be more complicated than that. It can take time to get this kind of database correct and it's not always the best solution.
No there isn't anything similar to what you are asking built into the product.
Standard SQL has the concept of a NATURAL JOIN that joins on common column names but SQL Server does not implement this and there would be no guarantee that any such names would correspond with the PK anyway.
It would be possible in theory for you to write some sort of script pre-processor or dynamic SQL code generator that did this if it was a massively important need for you.
Best Answer
I suggest doing both. Create an attribute for SKU and two separate attributes for the product and sub-type. SKU would be a key and the product sub-types would be a composite key. Example:
You can give the sub1 and sub2 attributes more meaningful names.
This is not actually a violation of 1NF. SKU is an important value in its own right and as long as you have the separate components in the database as well then there's no reason ever to split SKU into two parts. SKU is nearly always treated as a single value in my experience because although it may have encoded elements within it the encoding scheme is subject to variation. You may at some point have SKUs that don't follow the same scheme.
(note: in the above example I haven't identified a primary key because that's not relevant for this example)