Is there a way to create and populate a Sybase table column with column names based on rows in another table?

Question

Is there a way to pivot a Sybase Attribute-Value table, into an Attribute-per-column result set. Can this be done?

Source table:

| attribute | value |
~~~~~~~~~~~~~~~~~~~~~
| a1        | A1    |
| b2        | BB2   |
| random    | X     |

For simplicity, assume varchar(255) on both attribute and value columns, unique index on attribute; and the attribute names are 100% guaranteed to be strings which are valid Sybase column names.

Desired select result:

| a1   | b2   | random |
~~~~~~~~~~~~~~~~~~~~~~~~
| A1   | BB2  | X      |

Is this possible to do via SQL in Sybase ASE? (12 or 15)?

NOTES:

Just to be clear: we do NOT know beforehand what the set of attributes is!!!
There is no need to persist the result set beyond a #temp pound table.
The limitation is that there is no Java enabled.

_{It seems to me that this is impossible to do, since Sybase 15 ASE docs indicate that column names in ALTER TABLE must not contain dynamic data:}

_{computed_column_expression

is any valid T-SQL expression that does not contain columns from other tables, local variables, aggregate functions, or subqueries. It can be one or a combination of column name, constant, function, global variable, or case expression, connected by one or more operators. You cannot cross-reference between computed columns except when virtual computed columns reference materialize computed columns.}

As a note: this is asked in an attempt to solve this Q: http://stackoverflow.com/questions/14467376/are-there-more-elegant-ways-of-querying-data-based-on-values-stored-in-attribute — DVK, Jan 22 '13 at 20:26
if you are trying to solve another question you should have continued on that question with this idea. — Jacobvdb, Jan 31 '13 at 04:00

score 1 · Accepted Answer · answered Dec 29 '14 at 19:16

1

Yes. I've done that using EXEC

 SELECT @my_stmt = "ALTER TABLE #result ADD " + @param + " varchar(255) NULL" 
 EXEC(@my_stmt)

answered Dec 29 '14 at 19:16

DVK

126,886
32
213
327

Is there a way to create and populate a Sybase table column with column names based on rows in another table?

1 Answers1