SQLTeam.com | Weblogs | Forums

SQL order by Cell value


#1

Dear All,

Can we order the cell value alphabetically? Sort by can be used for columns be I need for the cell values of a column
Ex:
Select 'qwerty' order by asc

Result:
eqrtwy

or
Select Ename (cell value) order by alphabetically

Thanks,


#2

what does your data look like, please post sample


#3

I think given a column value of 'qwerty' the O/P wants to output 'eqrtwy' - i.e. all letters in alphabetical order.

I reckon it needs splitting into individual characters, perhaps using DISTINCT, and then sorting and re-combining, but I ain't got enough time to write example code.


#4

You will need a "standard" tally table to use with this code:

SELECT cell_chars /*, cell_value AS original_value*/
FROM (
    VALUES('qwerty'),('Johnny')
) AS test_data(cell_value)
CROSS APPLY (
    SELECT (
        SELECT '' + cell_char
        FROM (
            SELECT SUBSTRING(cell_value, t.N, 1) AS cell_char
            FROM dbo.tally t
            WHERE t.N BETWEEN 1 AND LEN(cell_value)
        ) AS derived
        ORDER BY cell_char
        FOR XML PATH('')
    ) AS cell_chars        
) AS ca1