Can I store a column name in an array?

Question

I want to find the minimum value for a given row in a SQL table (where all values in the table other than the column names are integers), with only inputting the column names in one location.

In this example, Column_Names are = A, B, C, D, and E

Screenshot of Sample Table, where minimums are (7, 1, 11)

I got to a point where I can run this code and get the values for a given row. These values are stored in a temporary table, #Test, and the minimum value in #Test is stored in another temporary table, #Answers. I then use a While loop with a running counter to iterate through all the rows and find the minimum's of the rows. The list of minimums are in the temporary table, #Answers.

CREATE TABLE #Test (Calc INT);

CREATE TABLE #Answers (Minimum INT);

DECLARE @Counter INT
SET @Counter = 1

WHILE (@Counter <= (SELECT COUNT(Counter_Name) FROM #Data))
BEGIN
    INSERT INTO #Test
    SELECT A
    FROM (SELECT ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) AS RowNum, * FROM #Data) sub
    WHERE
        RowNum = @Counter

    INSERT INTO #Test
    SELECT B
    FROM (SELECT ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) AS RowNum, * FROM #Data) sub
    WHERE
        RowNum = @Counter

    INSERT INTO #Test
    SELECT C
    FROM (SELECT ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) AS RowNum, * FROM #Data) sub
    WHERE
        RowNum = @Counter

    INSERT INTO #Test
    SELECT D
    FROM (SELECT ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) AS RowNum, * FROM #Data) sub
    WHERE
        RowNum = @Counter

    INSERT INTO #Test
    SELECT E
    FROM (SELECT ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) AS RowNum, * FROM #Data) sub
    WHERE
        RowNum = @Counter

    INSERT INTO #Answers
    SELECT min(Calc) FROM #Test

    DROP TABLE #Test

    CREATE TABLE #Test (Calc INT);
    SET @Counter = @Counter + 1

How can I run this code for a table with n number of columns? How do I generalize this code, or at the very least make it so that I don't have to copy & paste this whole piece of code for every column.:

INSERT INTO #Test
SELECT Counter_Name
FROM (SELECT ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) AS RowNum, * FROM #Data) sub
WHERE
    RowNum = @Counter

The ideal solution would be storing column names in an array and then iterate through that array using the above code. I have been unable to write up a working script for this step.

Your question would be much clearer if you added a [mre] with sample data and desired results. There might be a totally different way to do it, but thats pretty hard for us to know if we have to try and work out what your code is attempting to do. — Dale K, Feb 16 '23 at 21:00
doesn't look like sql server code because your using row_number() over. — danny117, Feb 16 '23 at 21:02
Thanks for the feedback! This is my first post, so I appreciate it. I updated the post accordingly. — Thubis, Feb 16 '23 at 21:12
Even before that, @JohnCappelletti, it was added in SQL Server 2005. — Thom A, Feb 16 '23 at 21:14
Is this what you are asking? https://dbfiddle.uk/dzav8oOF (if so what version of SQL Server are you on?) — Martin Smith, Feb 16 '23 at 21:44

score 0 · Answer 1 · answered Feb 16 '23 at 22:11

As alternative, you can use something like this:

create table #t (A INT, B INT, C INT, D INT, data varchar(30))

INSERT INTO #t 
VALUES  (1, 2, 3, 4,'test')
,   (6, 5,7, 8,'test2')
,   (9, 10, 11, 2,'test3')

SELECT  *
FROM    #t t
CROSS APPLY (
    select  ROW_NUMBER() over(order by v) as sort
    ,   x.v
    from (
            VALUES  
                (A)
            ,   (B)
            ,   (C)
            ,   (D)
        ) x (v)
    ) r
where sort = 1

You explode the A,B,C,D as values and then sort them by value and return the first one. If you need more columns, it's easy to add them to the VALUES(...) list.

You can also use GROUP BY if you have some good ID column in your #t table.

You might also use `MIN(x.v)`. See [https://dbfiddle.uk/aSI93Fic] — T N, Feb 17 '23 at 03:35

Can I store a column name in an array?

1 Answers1