StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<ul> <li>If you upvote this answer, please upvote PaulBailey's as his answer made mine possible *</li> </ul> <p>This handles 3 generic Code/Value pairs and correctly re-orders the output. It is not perfect obviouly, but it solved my needs.</p> <p>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.</p> <p>The second query uses PaulBailey's solution to order the pairs correctly. </p> <pre><code> 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] </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload