t-sql string & table column manipulation

Question

DETAILS table has following columns

Title  First  Second  Third  Fourth Fifth
------------------------------------------
A      null    null   null    null    null


input variable
--------------

@columns = 'Title, Third, Fourth'

I want to generate ouput as

@allcolumns = 'Title, Third, Fourth, First, Second, Fifth'

Variable @allcolumns will contain all columns from DETAILS table but with @columns first and then the remaining columns

So for instance if

@columns = 'Title, Fourth,Fifth' 

then output will be

@allcolumns = 'Title, Fourth, Fifth, First, Second, Third'

Thanks

Answer 1

There are many ways to do this. Being your question is rather general, I would suggest looking at the following link and using your INFORMATION_SCHEMA views if using SQL Server.

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Answer 2

First and most importantly, why not simply return columns First through Fifth and let the calling code determine which columns to use? The SQL language in general was not designed for dynamic column determination and generation. It presumes that the calling code handles the determination of the columns that should be returned. Further, calling code should never depend on the column order in a query and therefore the order of the columns in the output should make no difference. Given that, you should do this type of manipulation in a middle-tier component or reporting tool.

Second, while it is possible to solve this type of problem in T-SQL, it should not be done. T-SQL is awful for string manipulation.

Lastly, if this is the type of query you need to build to get the proper information from your database schema, you might need to re-evaluate your database schema. When you start running into more and more complicated queries in order to retrieve the information you want, it is indicative of a schema that is out of touch with the business needs.

Answer 3

This should work:

DECLARE @columns VARCHAR(max);
DECLARE @allcolumns VARCHAR(max);

SET @columns = 'Title,Fourth,Fifth';
SET @allcolumns = @columns;

SELECT @allcolumns = @allcolumns + ',' + column_name FROM
    INFORMATION_SCHEMA.columns WHERE
    table_name = 'DETAILS' AND
    CHARINDEX(column_name, @columns) = 0;

SELECT @allcolumns;

GO

---

An additional thought: if you want to create a SELECT statement to select the columns in the order generated by the above code, you could do this:

DECLARE @sql VARCHAR(max);
SET @sql = 'SELECT ' + @allcolumns + ' FROM DETAILS';
EXEC(@sql);

...although I can't see why you would want to do that.