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.
精彩评论