Select the same column multiple times

Question

hope someone can help. I have two tables:

Users
  -UserID
  -UserName

UsersType
  -UserTypeID
  -UserID

Possible values for UsersTypeID is 1 to 6. In that scenario Users may have multiple types and I need to retrieve a distinct row for each user with the columns described below.

UserName - Type1 - Type2 - Type3 - Type4
Joe            0       1       1       0

In this scenario, Joe has two different User type (2,3)

This might be easy as pie but I have been working around this for so long that I am clueless. Can some one help please.

Answer 1

This is a standard crosstab output which you should be able to google. Although not recommended in SQL you can do something like:

Select Users.Username
    , Max( Case When UsersType.UserTypeId = 1 Then 1 Else 0 End ) As Type1
    , Max( Case When UsersType.UserTypeId = 2 Then 1 Else 0 End ) As Type2
    , Max( Case When UsersType.UserTypeId = 3 Then 1 Else 0 End ) As Type3
    , Max( Case When UsersType.UserTypeId = 4 Then 1 Else 0 End ) As Type4
From Users
        Join UsersType
            On UsersType.UserId = Users.UserId
Group By Users.UserName

(Updated to Max instead of Min)

Answer 2

SELECT U.[UserName]
    , AVG(CASE WHEN UT.[UserTypeID] IS 1 THEN 1 ELSE NULL END) AS N'Type 1'
    , AVG(CASE WHEN UT.[UserTypeID] IS 2 THEN 2 ELSE NULL END) AS N'Type 2'
    , AVG(CASE WHEN UT.[UserTypeID] IS 3 THEN 3 ELSE NULL END) AS N'Type 3'
    , AVG(CASE WHEN UT.[UserTypeID] IS 4 THEN 4 ELSE NULL END) AS N'Type 4'
    , AVG(CASE WHEN UT.[UserTypeID] IS 5 THEN 5 ELSE NULL END) AS N'Type 5'
    , AVG(CASE WHEN UT.[UserTypeID] IS 6 THEN 6 ELSE NULL END) AS N'Type 6'
FROM [Users] U
    INNER JOIN [UserType] UT ON UT.[UserID] = U.[UserID]
GROUP BY U.[UserName]
ORDER BY U.[UserName]