0

Say widgets are stored in a table WIDGETS. Widgets come in 3 flavors: type 0, type 1, and type 2. A trivial WIDGETS table containing 4 widgets (with primary key IDWidget), might be:

IDWidget  wiType
-----------------
501         0
502         0
503         2
504         1

A table TYPEATTRIBUTES lists the structure of attributes for the types of widgets, with primary key IDTypeAttribute. The attributes for type 0 widgets might be described as:

IDTypeAttribute  wiType  taLabel  taStore  taSequence
-----------------------------------------------------
1                 0      'Type0a'  'text'    1
2                 0      'Type0b'  'text'    2
3                 0      'Type0c'  'number'  3

Another table AttributeValues contains the attribute values for individual widgets. The attribute values for some widgets might be:

IDAttributeValue  IDWidget  IDTypeAttribute  avValText  avValNumber
-------------------------------------------------------------------
101               501         1               'val1'     NULL
102               501         2               'val2'     NULL
103               501         3               NULL       123
104               502         1               'vala'     NULL
105               502         2               'valb'     NULL
106               502         3               NULL       789

Now I want to select all the type 0 widgets and display their attributes in a table whose column headings are the names of each attribute. The result set would be:

IDWidget  Type0a  Type0b  Type0c
---------------------------------
501        'val1'  'val2'  123
502        'vala'  'valb'  789

Of course, if I only had 1 type with a fixed structure (e.g., I know the names of the attributes for Type 0), I could do something like:

SELECT 
    w.IDWidget,
    (SELECT avValText FROM ATTRIBUTEVALUES WHERE IDWidget = w.IDWidget AND IDTypeAttribute = 1)Type0a
    (SELECT avValText FROM ATTRIBUTEVALUES WHERE IDWidget = w.IDWidget AND IDTypeAttribute = 2)Type0b
    (SELECT avValNumber FROM ATTRIBUTEVALUES WHERE IDWidget = w.IDWidget AND IDTypeAttribute = 3)Type0c
FROM 
    WIDGETS w 
WHERE 
    w.wiType = 0

But I want a general result where the TYPEATTRIBUTES table gives the column titles based on the attributes for this type of widget, and specifies whether to look for the text or numeric value, (not by hard-coding these).

I've tried various convoluted approaches using a sub-query to extract the proper attributes names based on wiType FROM the TYPEATTRIBUTES table but I run into problems with the requirement that only a single value be returned by the subquery.

Is it possible to name columns in this way with SQL Server?

user1693404
  • 173
  • 1
  • 12
  • This is not possible using static SQL. You have to use dynamic SQL. – Giorgos Betsos Mar 04 '19 at 20:27
  • So putting this into a View is impossible? – user1693404 Mar 04 '19 at 20:35
  • You cannot place dynamic sql inside a View. The View has a predefined set of columns with specific names. You can create a View using dynamic sql though. But then, you would need a separate view for each attribute type. – Giorgos Betsos Mar 04 '19 at 20:44
  • This is starting to sound like [BarCarma](https://www.red-gate.com/simple-talk/opinion/opinion-pieces/bad-carma/) – Sean Lange Mar 04 '19 at 20:53
  • Something like this may come similar to pivot, but even then, you would still have to know the columns to expect. Are you restricted to this design? Or are you open to other ways of storing data like XML or JSON? – alans Mar 04 '19 at 20:54
  • You can use Dynamic Pivot: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – Alex Mar 05 '19 at 04:52

0 Answers0