locked
Can't generate query for this scenario RRS feed

  • Question

  • User269846090 posted

    TableName  :  TBLPAGE

    PageName    

    Page1             

    Page2                 

    TableName :  TBLROLE

    RoleName  

    Role1

    Role2

    TableNAme : TBLROLEPAGES

    PageName   RoleName 

    Page1            Role1

    Page2            Role1

    Page1            Role2

    I need the below output.......

    PageName    Role1   Role2

    Page1             Y           Y

    Page2             Y            N

    How can i generate query for this?  I have tried but it display repeating values and I can't set the Role1, Role2 as column names.

    Note: The abouve given values are changeable, Many roles and meny page are available in DB.

    Monday, January 4, 2016 10:27 AM

Answers

  • User1083584480 posted

    Please use pivot query to produce output as you desired

    please go thru https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/ for more information

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 4, 2016 10:45 AM
  • User-271186128 posted

    Hi ssjGanesh,

    ssjGanesh

    How can i generate query for this?  I have tried but it display repeating values and I can't set the Role1, Role2 as column names.

    Note: The abouve given values are changeable, Many roles and meny page are available in DB.

    As for this issue, I suggest you could use LINQ GroupBy clause to get all of the PageName and RoleName, then create a table and add columns and rows. Please refer to the following code:

                DataTable dt2 = new DataTable();
                dt2.Columns.AddRange(new DataColumn[2] { new DataColumn("PageName"), new DataColumn("RoleName") });
                dt2.Rows.Add("Page1", "Role1");
                dt2.Rows.Add("Page2", "Role1");
                dt2.Rows.Add("Page1", "Role2");
    
                DataTable dt3 = new DataTable();
                dt3.Columns.Add("PageName");
    
                //Get all of the role name, then, according it to add columns
                var RoleNameList = dt2.AsEnumerable().GroupBy(c => c.Field<string>("RoleName")).Select(c => c.Key).ToList();
                foreach (var item in RoleNameList)
                {
                    dt3.Columns.Add(item);
                }
    
                //get all of the page name and relevant roles
                var PageNameList = dt2.AsEnumerable().GroupBy(c => c.Field<string>("PageName")).Select(c => new { pagename = c.Key, roleGroup = c.Select(d => d.Field<string>("RoleName")).ToList() });
                //Add roles
                foreach (var item in PageNameList)
                {
                    List<string> list = new List<string>();
                    list.Add(item.pagename);
                    foreach (var rolename in RoleNameList)
                    {
                        if (item.roleGroup.Contains(rolename))
                        {
                            list.Add("Y");
                        }
                        else
                        {
                            list.Add("N");
                        }
                    }
                    dt3.Rows.Add(list.ToArray());
                }
    
                GridView2.DataSource = dt3;
                GridView2.DataBind();

    The output:

    GridView2:

    PageName Role1 Role2
    Page1 Y Y
    Page2 Y N

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 28, 2016 6:37 AM

All replies

  • User1083584480 posted

    Please use pivot query to produce output as you desired

    please go thru https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/ for more information

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 4, 2016 10:45 AM
  • User503812343 posted

    you will have to try pivot

    SELECT *
        FROM 
        (
            SELECT
            PageName, Role
            FROM StudentDetails
        ) as s
        PIVOT
        (         
             FOR [Subject] IN (Role1, Role2)
        )
        AS Pivot_alias
    

    for more details  how to use pivot in SQL server

    Wednesday, January 6, 2016 11:51 AM
  • User-271186128 posted

    Hi ssjGanesh,

    ssjGanesh

    How can i generate query for this?  I have tried but it display repeating values and I can't set the Role1, Role2 as column names.

    Note: The abouve given values are changeable, Many roles and meny page are available in DB.

    As for this issue, I suggest you could use LINQ GroupBy clause to get all of the PageName and RoleName, then create a table and add columns and rows. Please refer to the following code:

                DataTable dt2 = new DataTable();
                dt2.Columns.AddRange(new DataColumn[2] { new DataColumn("PageName"), new DataColumn("RoleName") });
                dt2.Rows.Add("Page1", "Role1");
                dt2.Rows.Add("Page2", "Role1");
                dt2.Rows.Add("Page1", "Role2");
    
                DataTable dt3 = new DataTable();
                dt3.Columns.Add("PageName");
    
                //Get all of the role name, then, according it to add columns
                var RoleNameList = dt2.AsEnumerable().GroupBy(c => c.Field<string>("RoleName")).Select(c => c.Key).ToList();
                foreach (var item in RoleNameList)
                {
                    dt3.Columns.Add(item);
                }
    
                //get all of the page name and relevant roles
                var PageNameList = dt2.AsEnumerable().GroupBy(c => c.Field<string>("PageName")).Select(c => new { pagename = c.Key, roleGroup = c.Select(d => d.Field<string>("RoleName")).ToList() });
                //Add roles
                foreach (var item in PageNameList)
                {
                    List<string> list = new List<string>();
                    list.Add(item.pagename);
                    foreach (var rolename in RoleNameList)
                    {
                        if (item.roleGroup.Contains(rolename))
                        {
                            list.Add("Y");
                        }
                        else
                        {
                            list.Add("N");
                        }
                    }
                    dt3.Rows.Add(list.ToArray());
                }
    
                GridView2.DataSource = dt3;
                GridView2.DataBind();

    The output:

    GridView2:

    PageName Role1 Role2
    Page1 Y Y
    Page2 Y N

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 28, 2016 6:37 AM
  • User-62323503 posted

    Need to implement pivot for this. I believe rolename would not be  static, so you required to implement dynamic pivot.

    Refer below posts:

    http://www.itdeveloperzone.com/2011/11/dynamic-pivot-in-sql-server.html

    http://www.itdeveloperzone.com/2011/01/pivot-with-dynamic-columns.html

    Thursday, January 28, 2016 11:53 AM