CODEWITHSUNDEEP
sqlsql-servert-sql
JasonRShaver
JasonRShaver

Reputation: 4394

Dynamically order columns in SQL

I have a SQL Server 2008 R2 table with the following Schema

DECLARE @AttributeTable TABLE
(
     Code1 nvarchar(50),
     Value1 nvarchar(50),
     Code2 nvarchar(50),
     Value2 nvarchar(50),
     Stock int
);

With values like:

Color, Red, Size, Large, 15
Color, Blue, Size Large, 5
Size, Large, Color, Green, 4

And I am looking for a way to reorder the columns for each row (dynamically, not in the table itself) so that the result of a query would be

Color, Red, Size, Large, 15
Color, Blue, Size Large, 5
Color, Green, Size, Large, 4

But I can't think of any way to do this without resorting to creating a .NET function, which just seems like overkill.

I admit this is a failed architecture, but the schema belongs to a 3rd party ERP that I am unable to change.

And finally, if anyone has a good idea for a title for this question, please feel free to edit (or comment and I will change it)

EDIT:

The real table this example is based on has 6 different key-value pairs instead of two and the 'Code' value is dynamic (The current database has about 45 different code values).

Upvotes: 0

Views: 194

Answers (2)

JasonRShaver
JasonRShaver

Reputation: 4394

  • If you upvote this answer, please upvote PaulBailey's as his answer made mine possible *

This handles 3 generic Code/Value pairs and correctly re-orders the output. It is not perfect obviouly, but it solved my needs.

The first query finds the code values and puts them in the order of the first item in the table. It would not be hard to extend this as a function or stored procedure and pass in an order.

The second query uses PaulBailey's solution to order the pairs correctly. 

       DECLARE @ItemCode nvarchar(50) = 'ITEM-000001'
       DECLARE @Code1 nvarchar(50)
       DECLARE @Code2 nvarchar(50)
       DECLARE @Code3 nvarchar(50)

        SELECT TOP 1
               @Code1 = ii.AttributeCode1
             , @Code2 = ii.AttributeCode2
             , @Code3 = ii.AttributeCode3
          FROM @AttributeTable ii
         WHERE ii.ItemCode = @ItemCode

        SELECT ii.ItemCode
             , @Code1 as [AttributeCode1]
             , CASE WHEN ii.AttributeCode1 = @Code1 THEN ii.Attribute1 
                    WHEN ii.AttributeCode2 = @Code1 THEN ii.Attribute2 
                    WHEN ii.AttributeCode3 = @Code1 THEN ii.Attribute3
                    ELSE null END as [Attribute1]
             , @Code2 as [AttributeCode2]
             , CASE WHEN ii.AttributeCode1 = @Code2 THEN ii.Attribute1 
                    WHEN ii.AttributeCode2 = @Code2 THEN ii.Attribute2 
                    WHEN ii.AttributeCode3 = @Code2 THEN ii.Attribute3
                    ELSE null END as [Attribute2]
             , @Code3 as [AttributeCode3]
             , CASE WHEN ii.AttributeCode1 = @Code3 THEN ii.Attribute1 
                    WHEN ii.AttributeCode2 = @Code3 THEN ii.Attribute2 
                    WHEN ii.AttributeCode3 = @Code3 THEN ii.Attribute3
                    ELSE null END as [Attribute3]
          FROM @AttributeTable ii
         WHERE ii.ItemCode = @ItemCode
      ORDER BY [AttributeCode1], [Attribute1]
             , [AttributeCode2], [Attribute2]
             , [AttributeCode3], [Attribute3]

Upvotes: 1

paulbailey
paulbailey

Reputation: 5346

If the table is as simple as you show, it's relatively straightforward:

SELECT
    'Color',
    CASE WHEN Code1 = 'Color' THEN Value1 ELSE Value2 END,
    'Size',
    CASE WHEN Code1 = 'Size' THEN Value1 ELSE Value2 END,
    Stock
FROM
    @AttributeTable

Upvotes: 4

Related Questions