T-SQL Table Variable - Create a column for each row in a table
-
Hello, I need to write a script that creates a table variable with a column for every instance of a row in a table. The data in the table it pulls from may change from time to time, so I'm looking for a dynamic approach. Example table:
ColumnA: ColumnB:
1 Hello
2 WorldThen my table variable declaration should look like:
DECLARE @TableVariable TABLE
(
Hello varchar(20),
World varchar(20)
)Can anyone think of a performance-friendly solution to this? Thanks, Kevin
-
Hello, I need to write a script that creates a table variable with a column for every instance of a row in a table. The data in the table it pulls from may change from time to time, so I'm looking for a dynamic approach. Example table:
ColumnA: ColumnB:
1 Hello
2 WorldThen my table variable declaration should look like:
DECLARE @TableVariable TABLE
(
Hello varchar(20),
World varchar(20)
)Can anyone think of a performance-friendly solution to this? Thanks, Kevin
-
Hello, I need to write a script that creates a table variable with a column for every instance of a row in a table. The data in the table it pulls from may change from time to time, so I'm looking for a dynamic approach. Example table:
ColumnA: ColumnB:
1 Hello
2 WorldThen my table variable declaration should look like:
DECLARE @TableVariable TABLE
(
Hello varchar(20),
World varchar(20)
)Can anyone think of a performance-friendly solution to this? Thanks, Kevin
Kevin Leeds wrote:
Can anyone think of a performance-friendly solution to this?
Yes.. I think. I encounted a similar issue this week and solved it by using a pivot table. There is an excellent article on hear that gives a workable example. Pivot two or more columns in SQL Server 2005[^] Hope this helps... Tim
-
Kevin Leeds wrote:
Can anyone think of a performance-friendly solution to this?
Yes.. I think. I encounted a similar issue this week and solved it by using a pivot table. There is an excellent article on hear that gives a workable example. Pivot two or more columns in SQL Server 2005[^] Hope this helps... Tim
Glad it was useful. Always a buz when an article is used as reference (by someone other than the author :-O )
Never underestimate the power of human stupidity RAH
-
Hello, I need to write a script that creates a table variable with a column for every instance of a row in a table. The data in the table it pulls from may change from time to time, so I'm looking for a dynamic approach. Example table:
ColumnA: ColumnB:
1 Hello
2 WorldThen my table variable declaration should look like:
DECLARE @TableVariable TABLE
(
Hello varchar(20),
World varchar(20)
)Can anyone think of a performance-friendly solution to this? Thanks, Kevin
A pivot may do the job for you, organsing it will be a challenge. Dynamic SQL is more straight forward EXCEPT if you use EXEC (@SQL) to create the table var it is created in a different proc domain and therefore not available to you. Creating a global ##Table will fix this but it is not recommended in a frequently called proc.
Never underestimate the power of human stupidity RAH
-
A pivot may do the job for you, organsing it will be a challenge. Dynamic SQL is more straight forward EXCEPT if you use EXEC (@SQL) to create the table var it is created in a different proc domain and therefore not available to you. Creating a global ##Table will fix this but it is not recommended in a frequently called proc.
Never underestimate the power of human stupidity RAH