none
Customising pivot on two columns with master data RRS feed

  • Question

  • Hi,

    I am having issue in getting desired output. Please help on same.

    Below are the sample data.

    CREATE TABLE #ConfigMaster(
    WFConfigID int IDENTITY(1,1) NOT NULL,
    WFConfigType nvarchar(200) NOT NULL,
    VarAmount numeric(23, 0) NOT NULL
    )

    CREATE TABLE #RoleMaster(
    RoleId int IDENTITY(1,1) NOT NULL,
    RoleName nvarchar(200) NOT NULL
    )

    CREATE TABLE #RoleConfigDtls(
    RoleConfigId int IDENTITY(1,1) NOT NULL,
    RoleID int,
    WFConfigID int,
    IsApprover bit,
    IsNotifier bit
    )

    INSERT INTO #ConfigMaster(WFConfigType,VarAmount)
    VALUES ('Work FLow',100.00),('WF two',100.00),('Work Flow added from UI',100.00),('Work Flow Item',100.00),('asas',100.00),('Petrol',100.00),('new work flow',100.00),('abc',100.00)


    INSERT INTO #RoleMaster(RoleName)
    VALUES ('Manasa'),('kirti'),('Avinash'),('Head of Department'),('CEO'),('Division Manager'),('Manager'),('Delivery Manager')
    ,('Project Coordinator'),('Project Manager'),('Team leader'),('Technical architect'),('Software Engineer'),('Senior Engineeer')

    insert into #RoleConfigDtls values(7, 2, 1, 2)
    ,(6, 3, 1, 3),(8, 3, 0, 3),(7, 3, 0, 3),(9, 3, 0, 3),(10, 3, 1, 3),(11, 3, 0, 3),(12, 3, 0, 3),(13, 3, 0, 3),(14, 3, 0, 3),(16, 3, 0, 3),(15, 3, 0, 3),(17, 3, 0, 3),(4, 3, 0, 3)
    ,(6, 4, 1, 4),(8, 4, 0, 4),(7, 4, 0, 4),(9, 4, 0, 4),(10, 4, 0, 4),(11, 4, 0, 4),(12, 4, 1, 4),(13, 4, 0, 4),(14, 4, 0, 4),(16, 4, 0, 4),(15, 4, 0, 4),(17, 4, 0, 4),(4, 4, 0, 4)
    ,(6, 5, 1, 5),(8, 5, 0, 5),(7, 5, 0, 5),(9, 5, 0, 5),(10, 5, 0, 5),(11, 5, 0, 5),(12, 5, 1, 5),(13, 5, 0, 5),(14, 5, 0, 5),(16, 5, 0, 5),(15, 5, 0, 5),(17, 5, 0, 5),(4, 5, 0, 5)
    ,(6, 5, 1, 5),(8, 5, 0, 5),(7, 5, 1, 5),(9, 5, 0, 5),(10, 5, 1, 5),(11, 5, 0, 5),(12, 5, 0, 5),(13, 5, 0, 5),(14, 5, 0, 5),(16, 5, 0, 5),(15, 5, 0, 5),(17, 5, 0, 5),(4, 5, 0, 5)
    ,(6, 6, 0, 6),(8, 6, 1, 6),(7, 6, 1, 6),(9, 6, 0, 6),(10, 6, 0, 6),(11, 6, 0, 6),(12, 6, 0, 6),(13, 6, 0, 6),(14, 6, 0, 6),(16, 6, 0, 6),(15, 6, 0, 6),(17, 6, 0, 6),(4, 6, 0, 6)
    ,(6, 7, 1, 7),(8, 7, 0, 7),(7, 7, 0, 7),(9, 7, 1, 7),(10, 7, 0, 7),(11, 7, 0, 7),(12, 7, 0, 7),(13, 7, 0, 7),(14, 7, 0, 7),(16, 7, 0, 7),(15, 7, 0, 7),(17, 7, 0, 7),(4, 7, 0, 7)
    ,(6, 8, 1, 8),(8, 8, 0, 8),(7, 8, 0, 8),(9, 8, 0, 8),(10, 8, 1, 8),(11, 8, 0, 8),(12, 8, 0, 8),(13, 8, 1, 8),(14, 8, 0, 8),(16, 8, 0, 8),(15, 8, 0, 8),(17, 8, 0, 8),(4, 8, 0, 8)

    Below is the desired result. 

    

    Tuesday, November 12, 2019 3:15 PM

All replies

  • Below is the desired result. ???????

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 12, 2019 3:19 PM
    Answerer
  • Hi Srini2121,

    Thank you for your issue .

    Did you forget to post your expected result?  Please check .

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 13, 2019 2:23 AM
  • Sorry. msdn network didn't allow me to post desired result until my account is verified. Old account I forgot username and pwd. :(

    Wednesday, November 13, 2019 3:53 AM
  • Hi Srini2121, 

    I have voted you and you got points ,please try again (I am not sure that if it will be ). Is your expected result a picture ? Also , you can try to post it as a text and provide your logic . Just like below:

    /*

    id          A

    ----------- -----------

    3           12

    */

    If you could not post any more , it is difficult for us to provide some advice . 

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, November 13, 2019 5:47 AM