SELECT from 50 columns

Question

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

SELECT UserID, Intro_Req_DateTime, Intro_Onset_DateTime, Intro_Comp_DateTime, Info_Req_DateTime, Info_Onset_DateTime, Info_Comp_DateTime, 
Start_Req_DateTime, Start_Onset_DateTime, Start_Comp_DateTime,
Check_Req_DateTime, Check_Onset_DateTime, Check_Comp_DateTime,
Validate_Req_DateTime, Validate_Onset_DateTime, Validate_Comp_DateTime,
....
FROM MyTable

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.

Having 50 `datetime` columns tells me that your database design is wrong. You should instead have a table with maybe 3 `datetime` columns (req, onset, comp) and then a type. As it stands I think you have no choice but to add 50 conditions to your `where` clause - still with cut-and-paste its not going to take that long. — Dale K, May 13 '19 at 04:23
Or potentially write a View/ITVF with the 50 cases statements so that you only need to write it once. — Dale K, May 13 '19 at 04:24
I'd probably have an *Event* table and then some flags or reference table to specify the type. Rethink your layout. — Rob, May 13 '19 at 04:24
As others have said, you would benefit by redesigning your data schema. A minimal design would have one table with four columns: ProcedureNumber, StepNumber, UserID, DateTime. Use a composite primary key that spans the first three columns and use a date-time datatype for the fourth column. — Ken Evans, May 14 '19 at 17:31
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. — asmgx, May 14 '19 at 22:00

score 3 · Accepted Answer · answered May 13 '19 at 05:46

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.

SELECT TOP(1)
    A.StepName
    ,A.dt
FROM
    MyTable
    CROSS APPLY
    (
        VALUES
         ('Intro_Req', Intro_Req_DateTime)
        ,('Intro_Onset', Intro_Onset_DateTime)
        ,('Intro_Comp', Intro_Comp_DateTime)
        .........
    ) AS A (StepName, dt)
WHERE
    MyTable.UserID = 'ABC'
    AND A.dt > '2019-05-02T17:25:36'
ORDER BY dt DESC;

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

score 1 · Answer 2 · answered May 13 '19 at 05:08

The best way is to design your table with your action type and datetime that action was done. Then you can use a simple where clause to find what you want. The table should be like the table below:

ID          ActionType  ActionDatetime      
----------- ----------- ------------------- 
1492        1           2019-05-13 10:10:10          
1494        2           2019-05-13 11:10:10 
1496        3           2019-05-13 12:10:10 
1498        4           2019-05-13 13:10:10 
1500        5           2019-05-13 14:10:10

But in your current solution, you should use UNPIVOT to get what you want. You can find more information in this LINK.

SELECT from 50 columns

2 Answers2