locked
How to implement a SQL query based on this requirement RRS feed

  • Question

  • User-1279939347 posted

    Hi All, 

    I have 4 tables here and I need to write a query to get output of last table which is showing in the below image. Please help on this. Thanks in Advance!

    Tuesday, September 17, 2019 11:30 AM

All replies

  • User753101303 posted

    Hi,

    You'll use that where ?

    This is a PIVOT query which is harder to create on the SQL Server side. Reporting tools have often this out of the box or my personal preference is to pivot data on the web server side (you are uisng ADO.NET ?)

    Tuesday, September 17, 2019 11:40 AM
  • User-1279939347 posted

    Hi, 

    Thanks for the response!

    I'm using MVC and Entity Framework. 

    Tuesday, September 17, 2019 11:46 AM
  • User475983607 posted

    There is no way to solve this problem with the given tables.  There's no Id in table 4 that links to the the other tables.  At least the column names do not match nor does the data.  Looks like you need to rethink the design or you are missing a table.

    Tuesday, September 17, 2019 11:47 AM
  • User-1279939347 posted

    Thanks for the reply.

    Table 4 is i'm excepting the output. it not a original table. I just designed the output in sql to show you.

    Here the primary ID is AssignmentID column and we have relations for all tables. Table 1 is primary table.

    Tuesday, September 17, 2019 11:50 AM
  • User475983607 posted

    Oh, I did not see the validation billing ID.   I recommend that you post the CREATE TABLE DDL and script to populate the tables.  That makes it a lot easier from forum members to provide source code.  That way the community does not have to build scripts to create test tables and test data when you can do this rather easily with SSMS.

    Tuesday, September 17, 2019 11:55 AM
  • User77042963 posted

    Join your table together and pivot your result with  Case expression like below sample:

    ;with mycte as 
    (select AdditionalExpemseId,NVCNumber,UserId,Description,Amount 
     
    ,row_number() over(Partition by AdditionalExpemseId Order by amount) rn 
    from (
    --join your table together based on their replationship
    ) t
    )
     
    select  NVCNumber,
    Max(Case when rn =1 then UserId else null end) UserId1
    ,Max(Case when rn =1 then Description else null end) Des1
    ,Max(Case when rn =1 then Amount else null end) Amount1
    Max(Case when rn =2 then UserId else null end) UserId2
    ,Max(Case when rn =2 then Description else null end) Des2
    ,Max(Case when rn =2 then Amount else null end) Amount2
    Max(Case when rn =3 then UserId else null end) UserId3
    ,Max(Case when rn =3 then Description else null end) Des3
    ,Max(Case when rn =3 then Amount else null end) Amount3
      
    from mycte2
     group by   NVCNumber

    Tuesday, September 17, 2019 1:35 PM
  • User665608656 posted

    Hi kishore,

    To get the result as the last table,  first you need to combine the four tables into one table and add row_number to a temporary table #temp.

    Then you need to create a temporary table #AAA to store the last table that need to be displayed.

    Define variables and loop the #temp table to fill in the #AAA table.

    Here is the complete sql (exec it ,then you will get the final result):

     
    --create #Temp
    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    BEGIN
    DROP TABLE #Temp
    END
    SELECT ROW_NUMBER() OVER(ORDER BY UserID ASC) RN,*
    INTO #Temp
    FROM
    (
    SELECT a.NVCNumber,b.UserID,d.Description,d.Amount  FROM Table1 a
    LEFT JOIN Table2 b ON a.AssignmentId = b.AssignmentId
    LEFT JOIN Table3 c ON a.AssignmentId = c.AssignmentId
    LEFT JOIN Table4 d ON c.ValuationAssignmentBillingId = d.ValuationAssignmentBillingId
    )  data 
    
    --get the count of each group 
    declare @countRow int
    set @countRow = (SELECT TOP 1 * FROM
    (
    SELECT Count(UserID) rcount FROM #Temp group by #Temp.UserID) cc)
    
    -- create #AAA 
    IF OBJECT_ID('tempdb..#AAA') IS NOT NULL
    BEGIN
    DROP TABLE [#AAA]
    END
    CREATE TABLE [#AAA]( 
    [NVCNumber] varchar(50),
    )
    
    --loop the count of group to create the struct of #AAA
    declare @j INT
    declare @s  varchar(Max)
    set @j =1
    while @j <= (@countRow)
    begin
    set @s='alter table #AAA add [UserID'+CAST(@j as varchar(10))+'] int,[Desc'+CAST(@j as varchar(10))+'] varchar(50),[Amount'+CAST(@j as varchar(10))+'] int'
    exec(@s)
    set @j=@j+1
    END
    
    --loop #temp to insert the result of #AAA
    declare @i INT
    declare @p INT
    declare @column1 VARCHAR(50)
    declare @str VARCHAR(max)
    set @i =1
    SET @p =1 
    set @str='insert into #AAA values('''+ (SELECT TOP 1 NVCNumber FROM #Temp )+''''
    while @i <= (SELECT COUNT(*) FROM #Temp)
    BEGIN
    IF (@i = 1 )
    BEGIN
    set @str=@str+',(SELECT UserID FROM #Temp WHERE RN = '+CAST(@i as varchar(10))+'),(SELECT Description FROM #Temp WHERE RN = '+CAST(@i as varchar(10))+'),(SELECT Amount FROM #Temp WHERE RN = '+CAST(@i as varchar(10))+')'
    END
    ELSE IF (@i = @p + 1 + @countRow) BEGIN
    set @str=@str+',(SELECT UserID FROM #Temp WHERE RN = '+CAST(@i as varchar(10))+'),(SELECT Description FROM #Temp WHERE RN = '+CAST(@i as varchar(10))+'),(SELECT Amount FROM #Temp WHERE RN = '+CAST(@i as varchar(10))+')'
    SET @p = @i
    end
    set @i=@i+1
    END
    set @str=@str+')'
    exec(@str)
    
    
    -- the result you want
    SELECT * FROM #AAA;
    
    

    Here is the result of #AAA:

    Best Regards,

    YongQing.

    Wednesday, September 18, 2019 5:21 AM