CODEWITHSUNDEEP
sqlsql-servert-sqlselectmatrix
Veljko89
Veljko89

Reputation: 1953

SQL Table as matrix (T-SQL)

I have some interesting complex puzzle, complex for me at least. So this is the problem ....

I have a simple table that looks like this

Create table User(UserID int, DaysCount int, Date int)

Then I have simple select that based on DaysCount gives User some specific grade going from 1 to 4 and adding that to some temporary table.

Select UserID, 
(case
when DaysCount >= 0 and DaysCount <= 10 then 1
when DaysCount >= 11 and DaysCount <= 20 then 2
when DaysCount >= 21 and DaysCount <= 30 then 3
when DaysCount >= 31 and DaysCount <= 40 then 4
end) as Grade, Month
Into #A
from Users
where Date = 20150131

Select UserID, 
(case
when DaysCount >= 0 and DaysCount <= 10 then 1
when DaysCount >= 11 and DaysCount <= 20 then 2
when DaysCount >= 21 and DaysCount <= 30 then 3
when DaysCount >= 31 and DaysCount <= 40 then 4
end) as Grade, Month
Into #B
from Users
where Date = 20150228

And then this is complex part where I have a hard time ... I need to make a query that will select from temp. tables and insert data into new table (this table needs to look like matrix) and it will look like this. 

      Here is how table should look at end

 UserID      1    2    3     4          Date

   11        1    0    0     0        20150131  
   11        0    0    1     0        20150228
   13        0    1    0     0        20150228
   14        0    0    0     1        20150131

Thank you for help and please let me know if you don't understand what my problem exactly is!

Upvotes: 2

Views: 358

Answers (2)

GarethD
GarethD

Reputation: 69789

Unless I am missing something, all the temporary tables are not necessary, you can simply extract this as a single query from your users table, but rather than using a single case expression that returns a value between 1 and 4, use 4 case expressions that return 1 or 0:

SELECT  UserID,
        [1] = CASE WHEN DaysCount >= 0 AND DaysCount <= 10 THEN 1 ELSE 0 END,
        [2] = CASE WHEN DaysCount >= 11 AND DaysCount <= 20 THEN 1 ELSE 0 END,
        [3] = CASE WHEN DaysCount >= 21 AND DaysCount <= 30 THEN 1 ELSE 0 END,
        [4] = CASE WHEN DaysCount >= 31 AND DaysCount <= 40 THEN 1 ELSE 0 END,
        Date 
FROM    Users
WHERE   Date IN ('20150131', '20150228');

Or if you prefer the more traditional column aliases:

SELECT  UserID,
        CASE WHEN DaysCount >= 0 AND DaysCount <= 10 THEN 1 ELSE 0 END AS [1],
        CASE WHEN DaysCount >= 11 AND DaysCount <= 20 THEN 1 ELSE 0 END AS [2],
        CASE WHEN DaysCount >= 21 AND DaysCount <= 30 THEN 1 ELSE 0 END AS [3],
        CASE WHEN DaysCount >= 31 AND DaysCount <= 40 THEN 1 ELSE 0 END AS [4],
        Date 
FROM    Users
WHERE   Date IN ('20150131', '20150228');

Upvotes: 3

Stephan
Stephan

Reputation: 6018

Since they are intervals of ten, here's a shorter way to write it.

SELECT  DaysCount, 
        [1] = CASE WHEN dcID <= 1 THEN 1 ELSE 0 END,
        [2] = CASE WHEN dcID  = 2 THEN 1 ELSE 0 END,
        [3] = CASE WHEN dcID  = 3 THEN 1 ELSE 0 END,
        [4] = CASE WHEN dcID  = 4 THEN 1 ELSE 0 END
FROM Users
CROSS APPLY (SELECT CEILING(DaysCount/10.0)) CA(dcID)
WHERE [Date] IN ('20150131', '20150228');

Upvotes: 0

Related Questions