开发者

Best way to Sort-n-Concatenate 5 columns

!!! WARNING !!!

Dearest SQL expert, please keep reading before to start to scream. this uber-denormalized table structure is obtained after apply some combinatories-sugar upon a nicely normalized set of data : ). I'm avoiding to renormalize this resultset because I want to keep simple the complete process (also this data won't be used in another place in the application). (Yes, Codd. I开发者_开发技巧 know what you're thinking on).


Having five columns containing numeric values

 A | B | C | D | E
-------------------
 2 | 3 | 4 | 1 | 5
 3 | 6 | 1 | 5 | 4
 4 | 5 | 7 | 1 | 3

I want to obtain the concatenation of the values after sort them:

   ABCDE
-----------
 1 2 3 4 5
 1 3 4 5 6
 1 3 4 5 7

What is the best way to do it?


Looking past the serious questions of why you would need to do this, it can be achieved albeit awkwardly:

Select ...
    , Stuff(
            (
            Select ' ' + Z.Col
            From    (
                    Select PKCol, A As Col From Table
                    Union All Select PKCol, B From Table
                    Union All Select PKCol, C From Table
                    Union All Select PKCol, D From Table
                    Union All Select PKCol, E From Table
                    ) As Z
            Where Z.PKCol = Table.PKCol
            Order By Col
            For Xml Path('')
            ), 1, 1, '') As Combined
From Table

Obviously, databases were never designed to sort across columns which is why the solution is cumbersome to achieve.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜