Hi,
I want to create columns dynamically in a select statement. I have a field name like this abc,def,ghi,jkl,& and I want the result in columns:
SELECT a,b,c <columns here> FROM myTable
The problem is that I dont want to do it manually (using SUBSTRING, MID, LEFT, ETC), because I dont know how many I will have.
Right know Im trying to use a Function, but no luck till now.
ALTER FUNCTION dbo.myFunction
(@prc VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @total INT
DECLARE @actual INT
DECLARE @counter INT
DECLARE @SQL VARCHAR(MAX)
SET @SQL = ''
SET @total = LEN(@prc)
SET @actual = 1
SET @counter = 1
WHILE (@actual+3) < @total
BEGIN
SET @SQL = @SQL + ', SUBSTRING(prc,' + CAST((@actual) AS VARCHAR) + ',3) AS PRC' + CAST(@counter AS VARCHAR)
SET @actual = @actual + 3
SET @counter = @counter + 1
END
RETURN @SQL
END
Then I want it to use in the stored procedure:
DECLARE @aaa VARCHAR(MAX)
SET @aaa = 'SELECT ID, data, dbo. myFunction(prc) FROM myTable WHERE ID < 500'
EXEC(@aaa)
The problem is that I get a string for that column and not the columns!!!
Any help ?
