Skip to content
Advertisement

SELECT from 50 columns

I have a table that has many columns around 50 columns that have datetime data that represent steps user takes when he/she do a procedure

I want to find the Step the user did after certain datetime

example I want to find user ABC what the first step he did after 2 May 2019 17:25:36

I cannot use case to check this will take ages to code

is there an easier way to do that?

P.S. Thanks for everyone suggested redesigning the database.. not all databases can be redesigned, this database is for one of the big systems we have and it is been used for more than 20 years. redesigning is out of the equation.

Advertisement

Answer

You can use CROSS APPLY to unpivot the values. The syntax for UNPIVOT is rather cumbersome.

The actual query text should be rather manageable. No need for complicated CASE statements. Yes, you will have to explicitly list all 50 column names in the query text, you can’t avoid that, but it will be only once.

See also How to unpivot columns using CROSS APPLY in SQL Server 2012

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