0

how can you sort data in sql for each column ??

for example C1 column have value in first row as 'CAB' and you want in output as 'ABC'

Input

C1
CAB
ZSA

Output

C1
ABC
ASZ
Cleptus
  • 3,446
  • 4
  • 28
  • 34
hemant sharma
  • 1
  • 1
  • 1
    Not with any built in ansi-sql or t-sql function I am aware of. Please consider adding extra tags to narrow your database. In SQL Server you could create a custom scalar function that orders the data in the desired function and execute it like `select dbo.myStringOrderFunction(columnName) as columnName from myTable` – Cleptus Feb 28 '18 at 11:28
  • Check this: https://stackoverflow.com/questions/2051162/sql-multiple-column-ordering – ersks Feb 28 '18 at 11:31
  • @ersks The OP is not trying to order rows, but the column/cell data of those rows – Cleptus Feb 28 '18 at 12:19

1 Answers1

1

You could try this logic, maybe there is a better solution, but it does the job

DECLARE @t TABLE (Id INT, C1 VARCHAR(255))
INSERT INTO @t VALUES (1, 'CAB'),(2, 'ZSA')

;WITH mcte AS (
    SELECT split.Id, split.C1, split.c, ASCII(split.c) AS asciinr
    FROM (
        SELECT a.Id, a.C1, SUBSTRING(a.C1, v.number+1, 1) AS c
        FROM @t AS a
            join master..spt_values v on v.number < LEN(a.C1)
        WHERE v.type = 'P'
    ) AS split
) 

SELECT  Id, c1, REPLACE(STUFF((SELECT ' ' + mcte2.c FROM mcte AS mcte2 
                               WHERE mcte2.C1 = mcte.C1 and mcte2.Id = mcte.Id 
                               ORDER BY mcte2.asciinr FOR XML PATH('') ), 1, 1, ''), ' ', '') as OrderedC1

FROM    mcte
GROUP BY Id, c1
ORDER BY mcte.C1

Result

Id  C1   OrderderC1
-------------------
1   CAB  ABC
2   ZSA  ASZ
Kevin
  • 751
  • 6
  • 12
  • As long as the OP does not narrow its requirements to a specific database, your answer could be helpful to him/her and does solve the problem. Personally i mostly work with t-sql but I wonder if there is a ansi-sql equivalent to stuff and/or ascii – Cleptus Mar 01 '18 at 15:19