Query on a row which returns the column that the data occurs in and all the columns before it

Hi i have a table and when i query for a row i want to know which column a specific data appears in

                        TABLE
            | A1 | A2 | A3 | A4 | A5 | ID |
              QA    W    E    R    T    1
               Y    U    I   QA    O    2
               G    H   QA    U    T    3 

So for example when i query for table where ID=1 i want to know which column QA occurs in and all the columns before it, a data in a single row will never appear twice.

example

         Select * from table where ID=1
                | A1 | ID |
                  QA    1

         Select * from table where ID=2
           | A1 | A2 | A3 | A4 | ID |
             Y     U    I   QA   2

Im thinking of maybe using a case statement 5 times for each column, but i dont know how to create a different select statement after the THEN clause inside the case statement. so is it something like

    select case when A3 = 'QA' then select A1,A2,A3 from table where ID = 1)

? Thanks

Answer

There are a couple of options. Essentially, you need to unpivot your columns so that you can get the values. And you can use a CTE to filter your results for your needed criteria. You can either use T-SQL’s UNPIVOT or a CROSS APPLY (which is likely faster and probably gives a slightly cleaner query plan).

Given your data:

CROSS APPLY EXAMPLE:

; WITH myList AS (
  SELECT t1.ID, ca.ColName, ca.Val
      , ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY ca.ColName) AS rn
  FROM t1
  CROSS APPLY (
      VALUES 
        ('Row_1', A1)
      , ('Row_2', A2)
      , ('Row_3', A3)
      , ('Row_4', A4)
      , ('Row_5', A5)
    ) ca(ColName, Val)
  WHERE t1.ID = 2
)
, myPicks AS (
    SELECT ID, ColName, Val, rn
    FROM myList
    WHERE Val = 'QA'
)
SELECT l.ID, l.ColName, l.Val
FROM myList l
INNER JOIN myPicks p ON l.ID = p.ID
    AND l.rn <= p.rn

UNPIVOT EXAMPLE:

; WITH myList AS (
  SELECT unpiv.ID, unpiv.ColName, unpiv.Val
    , ROW_NUMBER() OVER (PARTITION BY unpiv.ID ORDER BY unpiv.ColName) AS rn
  FROM t1
  UNPIVOT 
  (
      Val 
      FOR ColName IN ( A1, A5, A3, A2, A4 )
  ) unpiv
  WHERE unpiv.ID = 2
)
, myPicks AS (
    SELECT ID, ColName, Val, rn
    FROM myList
    WHERE Val = 'QA'

)
SELECT l.ID, l.ColName, l.Val
FROM myList l
INNER JOIN myPicks p ON l.ID = p.ID
    AND l.rn <= p.rn

…will give you…

ID  ColName Val
2   A1      Y
2   A2      U
2   A3      I
2   A4      QA

This will put your data in rows which may be easier to work with in your programming language to re-pivot the way you need it. I’m not really sure how you plan on using this code or how your C# will work with it. That could change the best way to get at it.

Regardless, a column list in SQL isn’t necessarily guaranteed to come back in a specific order, and as was previously mentioned, column order should not be relied upon. The current behavior of a PIVOT/UNPIVOT will currently order the “columns” in the order that you put them in the IN clause, but that’s not documented behavior and can change at any update. The most reliable way for ordering in SQL is to use an ORDER BY clause. You can alias the columns you are unpivoting (but it requires more work), or just assign an alias as one of the values in the CROSS APPLY.

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=2ee6086ef661901542ba4d7a6b20181f