Skip to content
Advertisement

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

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

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

? Thanks

Advertisement

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:

UNPIVOT EXAMPLE:

…will give you…

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

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement