none
Create a matrix table with count of combination using t-sql RRS feed

  • Question

  • I have a sql query which will list the rows where game has won. 

    select 
    S1.[Group] as [Female Group],
    S2.[Group] as [Male Group],
    case when M.HasWon= 1 Then 'Yes' Else 'No' End as [HasWon]
    FROM [dbo].[GameList] G
    LEFT JOIN [dbo].[SelectionList] S1 ON G.XUIDTag=S1.UIDTag AND S1.Sex='F'
    LEFT JOIN [dbo].[SelectionList] S2 ON G.YUIDTag=S2.UIDTag AND S2.Sex='M'
    where m.HasWon = 1

    The result from the query is 

    Female Group Male Group HasWin
    18B39005 18B39053 Yes
    18B39061 18B39053 Yes
    18B39053 18B39133 Yes
    18B39104 18B39102 Yes
    18B39019 18B39102 Yes
    18B39088 18B39006 Yes
    18B39088 18B39006 Yes
    18B39088 18B39055 Yes
    18B39088 18B39055 Yes
    18B39055 18B39088 Yes


    The female and male can be from same group but will play game only with another group candidate. For example in selection List table the list of selected candidates is as below

    CandidateNo Sex Group
    123 F 18B39055
    999 M 18B39055
    456 F 18B39055
    888 M 18B39102

    I have to get a matrix with count of times the group combination has played game.

    How to get the matrix from t-sql query. 

    Unique female group vs unique male group with count

    Matrix Plot

    Thanks alot for the guidance.

    Regards,

    NewB


    NewBInVB


    Friday, March 8, 2019 10:07 AM

Answers

  • Hi,

    You can create a dataset with the query bellow : 

    select 
    S1.[Group] as [Female Group],
    S2.[Group] as [Male Group],
    COUNT(*) CntPlayedGame
    FROM [dbo].[GameList] G
    LEFT JOIN [dbo].[SelectionList] S1 ON G.XUIDTag=S1.UIDTag AND S1.Sex='F'
    LEFT JOIN [dbo].[SelectionList] S2 ON G.YUIDTag=S2.UIDTag AND S2.Sex='M'
    where m.HasWon = 1
    GROUP BY S1.[Group], S2.[Group]

    And then in the SSRS report, you can group by [Female Group] in Rows group, [Male Groupe] in the columns group 

    , and use the =MAX(CntPlayedGroup.Value) in the expression of cell.


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]


    • Edited by Ousama EL HOR Friday, March 8, 2019 10:26 AM
    • Marked as answer by NewBInCoding Friday, March 8, 2019 11:18 AM
    • Unmarked as answer by NewBInCoding Friday, March 8, 2019 1:01 PM
    • Marked as answer by NewBInCoding Friday, March 8, 2019 1:36 PM
    Friday, March 8, 2019 10:25 AM
  • It is the same logic.

    with the same query in Excel, you can use the same grouping in the matrix (Female Group in rows and Male group in Columns with the CntPlayedGroup in measures).


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by NewBInCoding Friday, March 8, 2019 11:18 AM
    • Unmarked as answer by NewBInCoding Friday, March 8, 2019 11:46 AM
    • Marked as answer by NewBInCoding Friday, March 8, 2019 1:35 PM
    Friday, March 8, 2019 10:33 AM

All replies

  • Hi,

    You can create a dataset with the query bellow : 

    select 
    S1.[Group] as [Female Group],
    S2.[Group] as [Male Group],
    COUNT(*) CntPlayedGame
    FROM [dbo].[GameList] G
    LEFT JOIN [dbo].[SelectionList] S1 ON G.XUIDTag=S1.UIDTag AND S1.Sex='F'
    LEFT JOIN [dbo].[SelectionList] S2 ON G.YUIDTag=S2.UIDTag AND S2.Sex='M'
    where m.HasWon = 1
    GROUP BY S1.[Group], S2.[Group]

    And then in the SSRS report, you can group by [Female Group] in Rows group, [Male Groupe] in the columns group 

    , and use the =MAX(CntPlayedGroup.Value) in the expression of cell.


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]


    • Edited by Ousama EL HOR Friday, March 8, 2019 10:26 AM
    • Marked as answer by NewBInCoding Friday, March 8, 2019 11:18 AM
    • Unmarked as answer by NewBInCoding Friday, March 8, 2019 1:01 PM
    • Marked as answer by NewBInCoding Friday, March 8, 2019 1:36 PM
    Friday, March 8, 2019 10:25 AM
  • Dear Ousama, 

    Thank you for the initial help. I'm not using SSRS report I have to extract the report on excel sheet so can you guide me how I can get on excel?


    NewBInVB

    Friday, March 8, 2019 10:29 AM
  • It is the same logic.

    with the same query in Excel, you can use the same grouping in the matrix (Female Group in rows and Male group in Columns with the CntPlayedGroup in measures).


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by NewBInCoding Friday, March 8, 2019 11:18 AM
    • Unmarked as answer by NewBInCoding Friday, March 8, 2019 11:46 AM
    • Marked as answer by NewBInCoding Friday, March 8, 2019 1:35 PM
    Friday, March 8, 2019 10:33 AM