CODEWITHSUNDEEP
sqlsql-servert-sql
Litsher - Nathan
Litsher - Nathan

Reputation: 275

Get output column for each Key

I have a database with roughly this fields: 

SELECT [UserId]
  ,[MissionId]
  ,[LevelId]
  ,[Key]
  ,[BooleanAnswer]
  ,[CreationDateTimeUtc]
  ,[QuestionId]
  FROM [Bakman].[dbo].[LevelEntries]
  WHERE LevelId = 2 AND QuestionId = 1 OR QuestionId = 2 OR QuestionId = 3 OR QuestionId = 4 OR QuestionId = 5 OR QuestionId = 6

Now I get this kind of output: 

UserId  MissionId   LevelId Key BooleanAnswer   CreationDateTimeUtc QuestionId
0      1            2       1   1               2017-10-07 11:39:26.350 1
0      1            2       2   1               2017-10-07 11:39:26.350 1
0      1            2       3   0               2017-10-07 11:39:26.350 1
0      1            2       4   1               2017-10-07 11:39:26.350 1
0      1            2       5   0               2017-10-07 11:39:26.350 1
0      1            2       6   1               2017-10-07 11:39:26.350 1

But now I want to get a column for each QuestionId (which are equal to the [key]).

Like this : 

       UserId   MissionId   LevelId Key1  Key2  Key3   Key4   Key5   Key6       CreationDateTimeUtc     QuestionId
      1         2       1     0     1     1     0      1      1      1          2017-10-07 11:39:26.350 1

Where Key[key] has the boolean awnser and the key belonging to that element. Good to know is that the Userid + MissionId + LevelId + Key are the Composite key for this table.

EDIT:

Trying both given answers I now get something like this

    UserId  MissionId   LevelId Key1    Key2    Key3    Key4    Key5    Key6
   1    1   2   1   NULL    NULL    NULL    NULL    NULL    
    1   1   2   NULL    1   NULL    NULL    NULL    NULL    
    1   1   2   NULL    NULL    0   NULL    NULL    NULL    
    1   1   2   NULL    NULL    NULL    1   NULL    NULL    
    1   1   2   NULL    NULL    NULL    NULL    1   NULL    
    1   1   2   NULL    NULL    NULL    NULL    NULL    1

Now I want to combine those so I get the level result of the user.

Upvotes: 0

Views: 53

Answers (2)

Sergey Menshov
Sergey Menshov

Reputation: 3906

Try the following query

SELECT
  UserId,
  MissionId,
  LevelId,
  MAX(CASE WHEN [Key]=1 THEN BooleanAnswer END) Key1,
  MAX(CASE WHEN [Key]=2 THEN BooleanAnswer END) Key2,
  MAX(CASE WHEN [Key]=3 THEN BooleanAnswer END) Key3,
  MAX(CASE WHEN [Key]=4 THEN BooleanAnswer END) Key4,
  MAX(CASE WHEN [Key]=5 THEN BooleanAnswer END) Key5,
  MAX(CASE WHEN [Key]=6 THEN BooleanAnswer END) Key6,
  CreationDateTimeUtc,
  QuestionId
FROM LevelEntries
WHERE LevelId=2
  AND QuestionId IN(1,2,3,4,5,6)
GROUP BY UserId,MissionId,LevelId,CreationDateTimeUtc,QuestionId

The second variant

SELECT
  UserId,
  MissionId,
  LevelId,
  MAX(CASE WHEN [Key]=1 THEN BooleanAnswer END) Key1,
  MAX(CASE WHEN [Key]=2 THEN BooleanAnswer END) Key2,
  MAX(CASE WHEN [Key]=3 THEN BooleanAnswer END) Key3,
  MAX(CASE WHEN [Key]=4 THEN BooleanAnswer END) Key4,
  MAX(CASE WHEN [Key]=5 THEN BooleanAnswer END) Key5,
  MAX(CASE WHEN [Key]=6 THEN BooleanAnswer END) Key6,
  MAX(CreationDateTimeUtc) MaxCreationDateTimeUtc,
  QuestionId
FROM LevelEntries
WHERE LevelId=2
  AND QuestionId IN(1,2,3,4,5,6)
GROUP BY UserId,MissionId,LevelId,QuestionId

Upvotes: 1

Manu
Manu

Reputation: 96

You can use PIVOT for that purpose.

    with Entries as
    (
        select UserId,
            MissionId,
            LevelId,
            'Key' + cast([Key] as varchar) as [Key],
            BooleanAnswer,
            CreationDateTimeUtc,
            QuestionId
        from [Bakman].[dbo].[LevelEntries]
        where LevelId = 2 and QuestionId in (1, 2, 3, 4, 5, 6)
    )

    select UserId,
        MissionId,
        LevelId,
        Key1, 
        Key2, 
        Key3, 
        Key4, 
        Key5,
        Key6,
        CreationDateTimeUtc,
        QuestionId
    from Entries t
    pivot
    (
        max(BooleanAnswer)
        for [Key] in (Key1, Key2, Key3, Key4, Key5, Key6)
    ) p;

Upvotes: 1

Related Questions