All geek questions in one place

Select the same column multiple times.

hope someone can help. I have two tables:

Users
  -UserID
  -UserName

UsersType
  -UserTypeID
  -UserID

The possible values ​​for UserTypeID are from 1 to 6. In this scenario, users can have several types, and I need to get a separate row for each user with the columns described below.

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

In this case, Joe has two different user types (2,3)

It may be as easy as a pie, but I have been working on it for so long that I don’t know. Can someone help.

+3
source share
7 answers

This is the standard crosstab output you should have on Google. Although this is 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)

+4
source 
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]
+1

,

SELECT 
    UserName, 
    [1] as 'type1', 
    [2] as 'type2', 
    [3] as 'type3', 
    [4] as 'type4', 
    [5] as 'type5', 
    [6] as 'type6'
FROM (
        SELECT 
            UserName, 
            userTypeId 
        FROM 
            users LEFT OUTER JOIN UsersType
            ON users.userId = usersType.userid
    ) AS src
PIVOT (
    count(userTypeId) FOR userTypeId IN ([1],[2],[3],[4],[5],[6]) ) AS pvt
+1

UserTypeID () . , , .

Select U.UserName, Case When UT1.UserType Is Null Then 0 Else 1 End As Type1,   
    Case When UT2.UserType Is Null Then 0 Else 1 End As Type2,
    Case When UT3.UserType Is Null Then 0 Else 1 End As Type3, 
    Case When UT4.UserType Is Null Then 0 Else 1 End As Type4, 
    Case When UT5.UserType Is Null Then 0 Else 1 End As Type5, 
    Case When UT6.UserType Is Null Then 0 Else 1 End As Type6, 
From Users As U
    Left Join UsersType As UT1 On U.UserID = UT1.UserID
    Left Join UsersType As UT2 On U.UserID = UT2.UserID     
    Left Join UsersType As UT3 On U.UserID = UT3.UserID     
    Left Join UsersType As UT4 On U.UserID = UT4.UserID     
    Left Join UsersType As UT5 On U.UserID = UT5.UserID     
    Left Join UsersType As UT6 On U.UserID = UT6.UserID     
Where UT1.UserTypeID = 1 And UT2.UserTypeID = 2
    And UT3.UserTypeID = 3 And UT4.UserTypeID = 4
    And UT5.UserTypeID = 5 And UT6.UserTypeID = 6

, UserTypes . udf , .

0

LEFT JOIN. -

SELECT
UserName
U1.UserTypeID AS Type1
U2.UserTypeID AS Type2
U3.UserTypeID AS Type3
U4.UserTypeID AS Type4
FROM Users U
LEFT JOIN UsersType U1 ON U.UserID  = U1.UserID AND UserTypeID  = 1
LEFT JOIN UsersType U2 ON U.UserID  = U2.UserID AND UserTypeID  = 2
LEFT JOIN UsersType U3 ON U.UserID  = U3.UserID AND UserTypeID  = 3
LEFT JOIN UsersType U4 ON U.UserID  = U4.UserID AND UserTypeID  = 4

, true false, ( SQL Server):

SELECT
UserName
CASE WHEN U1.UserTypeID IS NOT NULL THEN 1 ELSE 0 END AS Type1
CASE WHEN U2.UserTypeID IS NOT NULL THEN 1 ELSE 0 END AS Type2
CASE WHEN U3.UserTypeID IS NOT NULL THEN 1 ELSE 0 END AS Type3
CASE WHEN U4.UserTypeID IS NOT NULL THEN 1 ELSE 0 END AS Type4
FROM Users U
LEFT JOIN UsersType U1 ON U.UserID  = U1.UserID AND UserTypeID  = 1
LEFT JOIN UsersType U2 ON U.UserID  = U2.UserID AND UserTypeID  = 2
LEFT JOIN UsersType U3 ON U.UserID  = U3.UserID AND UserTypeID  = 3
LEFT JOIN UsersType U4 ON U.UserID  = U4.UserID AND UserTypeID  = 4
0

, , (, 100 ),

  select 
     u.username,
     isnull(ut1.Usertypeid,0) as Type1,
     isnull(ut2.Usertypeid,0) as Type2,
     isnull(ut3.Usertypeid,0) as Type3,
     isnull(ut4.Usertypeid,0) as Type4,
     isnull(ut5.Usertypeid,0) as Type5,
     isnull(ut6.Usertypeid,0) as Type6

    from 
        users u
    left outer join 
        userstype ut1 on u.userid = ut1.userid and ut1.usertypeid = 1
    left outer join 
        userstype ut2 on u.userid = ut2.userid and ut2.usertypeid = 2
    left outer join 
        userstype ut3 on u.userid = ut3.userid and ut3.usertypeid = 3
    left outer join 
        userstype ut4 on u.userid = ut4.userid and ut4.usertypeid = 4
    left outer join 
        userstype ut5 on u.userid = ut5.userid and ut5.usertypeid = 5
    left outer join 
        userstype ut6 on u.userid = ut6.userid and ut6.usertypeid = 6

10- , , (, 10-), , !

,

select 
     u.username,
     ut..Usertypeid
    from 
        users u
    left outer join 
        userstype ut
    on u.userid = ut.userid

,

  Username | UserTypeID
  ---------------------
  Joe      | 3
  Joe      | 4

, !

0

, 6 , :

SELECT  UserName
        ,(
            SELECT  CONVERT(bit, COUNT(UserTypeID))
            FROM    UsersType AS ut
            WHERE   ut.UserID = u.UserID
                    AND ut.UserTypeID = 1
        ) AS Type1
        ,(
            SELECT  CONVERT(bit, COUNT(UserTypeID))
            FROM    UsersType AS ut
            WHERE   ut.UserID = u.UserID
                    AND ut.UserTypeID = 2
        ) AS Type2
        ,...
FROM    Users AS u
0

Source: https://habr.com/ru/post/1736133/

More articles:


All Articles