locked
Need dynamic t-sql with group by based on tablename and column names RRS feed

  • Question

  • Hi All,

    I have two tables - dbo.employee, dbo.productorder and 1 stored procedure - dbo.uspSample with 2 input parameters.
    Employee Table:
    EmpID      EmpName      SalaryCalc    HireDateYear
    1            Tom         5000         2015               
    2            Tom         8000         2015               
    3            Tom         9000         2016               
    4            Sam         4000         2016               

    ProductOrder Table:
    ProductID      ProductName      AmountCalc    QuantityCalc
    1                Laptop           500          1
    2                PC               800          2
    3                Laptop           550          2
    4                Ipad             700          5

    Stored Proc name: dbo.uspSample (@TableName, @ColNames)

    Now I need a dynamic t-sql with in the sp that needs to be executed when a @TableName and @ColNames are passed as input parameters and based on input values it should return the following output scenarios.The values to the @ColNames would vary every time and based on the column names
    Note: All the calculated or summation fields are indicated with 'Calc' at the end of the columnnames to identify.

    Scenario-1) When @TableName = dbo.employee and @ColNames = 'EmpName, SalaryCalc' (2 columns) are passed as input parameters to the sp, the output should be

    Output:
    EmpName      SalaryCalc   
     Tom          22000      
     Sam          4000

    Scenario-2) When @TableName = dbo.employee and @ColNames = 'EmpName, SalaryCalc, HireDateYear' (3 columns) are passed as input parameters to the sp, the output should be

    Output:
    EmpName      SalaryCalc    HireDateYear
    Tom          13000           2015
    Tom          9000            2016
    Sam          4000            2016

    Scenario-3) When @TableName = dbo.productOrder and @ColNames = 'ProductName, AmountCalc' (2 columns) are passed as input parameters to the sp, the output should be

    Output:
    ProductName      AmountCalc   
     Laptop           1050      
     PC               800
     Ipad             700

    Scenario-4) When @TableName = dbo.productorder and @ColNames = 'ProductName, AmountCalc, QuantityCalc' (3 columns) are passed as input parameters to the sp, the output should be

    Output:
    ProductName      AmountCalc    QuantityCalc
     Laptop           1050            3
     PC               800             2
     Ipad             700             5


    Create Table dbo.Employee (EmpID int null, EmpName varchar(25) null, SalaryCalc money null, HireDateYear int null )
    Insert into table dbo.Employee (EmpID, EmpName, SalaryCalc,HireDateYear)
    Values
    (1 , 'Tom', '5000', 2015 ),              
    (2 , 'Tom', '8000', 2015 ),              
    (3 , 'Tom', '9000', 2016 ),              
    (4 , 'Sam', '4000', 2016 )

    Create Table dbo.ProductOrder(ProductID int null, ProductName varchar(50) null , AmountCalc money null, QuantityCalc int null)
    Insert into table dbo.productorder (ProductID , ProductName , AmountCalc, QuantityCalc)
    Values
    (1 ,  'Laptop' , '500', 1),
    (2 ,  'PC'     , '800', 2),
    (3 ,  'Laptop',  '550', 2),
    (4 ,  'Ipad',    '700', 5)

    Based on above data need Dynamic t-sql query which will be executed through strored proc based on any given table or columns names.

    Thanks in advance
    RH


    sql

    Wednesday, September 2, 2020 2:10 AM

Answers