Sort Values of Multiple Columns Per-Row and Update Columns Accordingly?-CodePudding

I'm trying to replace a function in VBA that goes row-by-row and grabs the values of 3 columns in a table, sorts them in ascending order, and places them in the correct column. Here's an example of what the table looks like before and after:

Before:

ID	Column A	Column B	Column C
96	25	13	84
97	101	78	8

Pseudo Code

    For each rw in tblSQL:
        array = quicksort(rw.col1, rw.col2, rw.col3)
        update tblSQL SET rw.col1 = array(0), 
                          rw.col2 = array(1),
                          rw.col3 = array(2)

After:

ID	Column A	Column B	Column C
96	13	25	84
97	8	78	101

This was never supposed to be a permanent solution but it ran lightning fast when the data was local. However, we now use an Azure SQL DB, and it's too slow for my liking to go updating row-by-row with an ODBC connection.

Is there a way to do this with TSQL or something else in SQL Server? I'm having a hard time finding any answers because all my search results are about sorting all the records in a column, not the individual values in each row across the columns. I could also create a quick and easy Azure function using Python or C# but I'm not sure if that would give me any performance gains because I'd still need to go row-by-row with some type of remote connection correct?

CodePudding user response：

You can UNPIVOT and then PIVOT with a new order.

declare @MyTable table (ID int, ColumnA int, ColumnB int, ColumnC int);

insert into @MyTable (ID,   ColumnA, ColumnB, ColumnC)
values
(96, 25, 13, 84),
(97, 101, 78, 8);

with cte1 as (
    select Id, ColumnName, ColumnValue
    from @MyTable
    unpivot (
        ColumnValue for ColumnName in (ColumnA, ColumnB, ColumnC)
    ) up
), cte2 as (
    select id, ColumnValue
      , 'Column'   char(64   row_number() over (partition by Id order by ColumnValue)) ColumnName
    from cte1
), cte3 as (
    select id, ColumnA, ColumnB, ColumnC
    from cte2
    pivot (
        sum(ColumnValue) for ColumnName in ([ColumnA],[ColumnB],[ColumnC])
    ) p
)
update my set
    ColumnA = c3.ColumnA
    , ColumnB = c3.ColumnB
    , ColumnC = c3.ColumnC
from cte3 c3
inner join @MyTable my on my.Id = c3.id;

select * from @MyTable;

Returns

id	ColumnA	ColumnB	ColumnC
96	13	25	84
97	8	78	101

CodePudding user response：

Sounds like a job for CASE!

DROP TABLE IF EXISTS #EverydayImShuffling
CREATE TABLE #EverydayImShuffling (ID INT, ColA INT, ColB INT, ColC INT)
INSERT INTO #EverydayImShuffling (ID, ColA, ColB, ColC) VALUES
(96, 25 ,   13, 84),
(97, 101,   78, 8 )

UPDATE #EverydayImShuffling
   SET ColA = CASE WHEN ColA < ColB AND ColA < ColC THEN ColA
                   WHEN ColB < ColC THEN ColB
                   ELSE ColC
               END,
       ColB = CASE WHEN ColA > ColB AND ColA < ColC THEN ColA
                   WHEN ColB < ColA AND ColB > ColC THEN ColB
                   ELSE ColC
               END,
       ColC = CASE WHEN ColA > ColB AND ColA > ColC THEN ColA
                   WHEN ColB > ColA AND ColB > ColC THEN ColB
                   ELSE ColC
               END
  FROM #EverydayImShuffling

SELECT *
  FROM #EverydayImShuffling

ID  ColA    ColB    ColC
------------------------
96  13      25      84
97  8       78      101

Edit:

Needed a little work to get the case exactly right, but this should demo that it's valid (or at least it was for me with a couple of hundred random rows)

DROP TABLE IF EXISTS #EverydayImShuffling
CREATE TABLE #EverydayImShuffling (ID INT IDENTITY, ColA INT, ColB INT, ColC INT)
INSERT INTO #EverydayImShuffling (ColA, ColB, ColC) VALUES
(25 ,  13, 84 ),
(101,  78, 8  )

WHILE (SELECT COUNT(*) FROM #EverydayImShuffling) < 200
BEGIN
 INSERT INTO #EverydayImShuffling (ColA, ColB, Colc) VALUES
 (ROUND(((100 - 1 -1) * RAND()   1), 0) - 1,
 ROUND(((100 - 1 -1) * RAND()   1), 0) - 1,
 ROUND(((100 - 1 -1) * RAND()   1), 0) - 1)
END

SELECT *
  FROM #EverydayImShuffling

UPDATE #EverydayImShuffling
   SET ColA = CASE WHEN ColA <= ColB AND ColA <= ColC THEN ColA
                   WHEN ColB <= ColC AND ColB <= ColA THEN ColB
                   WHEN ColC <= ColB AND ColC <= ColA THEN ColC
               END,                           
       ColB = CASE WHEN ColA >= ColB AND ColA <= ColC THEN ColA
                   WHEN ColB >= ColA AND ColB <= ColC THEN ColB
                   WHEN ColC >= ColA AND ColC <= ColB THEN ColC
                   WHEN ColC >= ColB AND ColC <= ColA THEN ColC
                   WHEN ColB >= ColC AND ColB <= ColA THEN ColB
                   WHEN ColA >= ColC AND ColA <= ColB THEN ColA
               END,                           
       ColC = CASE WHEN ColA >= ColB AND ColA >= ColC THEN ColA
                   WHEN ColB >= ColA AND ColB >= ColC THEN ColB
                   WHEN ColC >= ColA AND ColC >= ColA THEN ColC
               END
  FROM #EverydayImShuffling

SELECT *
  FROM #EverydayImShuffling

I validated it in excel, comparing the before and after totals, and then validating the numbers where in the right order for each row.

You'll probably want to validate yourself before you run it against any real data.