CODEWITHSUNDEEP
sqlsql-server
asmgx
asmgx

Reputation: 7984

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

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.

Upvotes: 1

Views: 633

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

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

Upvotes: 3

Masoud Amidi
Masoud Amidi

Reputation: 183

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.

Upvotes: 1

Related Questions