left join of 3 tables and display like a dynamic pivot

Question

i have 3 tables:

payorderType : 
---------
typeID | TypeName   |
   1   | accounting |
   2   | budget     |
----------          
step:
----------
 StepID | StepName   | typeID
   1   | payorder   |   1
   2   | cheque     |   1
   3   | cheque     |   2

----------
user:
----------
  userID | StepName   | StepID
   7878  | payorder   |   1
   4547  | cheque     |   2
   6538  | cheque     |   1


----------

I want to make a table which users exists in row and columns includes with concat of step and payorderType. same as below:

           users | accounting_payorder | accounting_cheque | budget_cheque |
           7878  |          1          |        0          |       0       |
           4547  |          0          |        1          |       0       |
           6538  |          0          |        1          |       0       |

My quesdtion is : if i don't know number of payorderType rows and number of step rows, how should i write it? My Script is here : First I create a table in cursor for concat payorderType and step:

CREATE PROC sp_payOrderType

AS

BEGIN

    DECLARE a CURSOR

    FOR SELECT DISTINCT p.TypeName,s.StepName 
    FROM 
        dbo.PayOrderType p LEFT JOIN 
        dbo.vStep s ON s.TypeID = p.TypeID
    FOR READ ONLY 

    DECLARE @payOrderType NVARCHAR(50),@stepName NVARCHAR(50)
    DECLARE @SQL NVARCHAR(MAX)=''

    OPEN a
    FETCH NEXT FROM a INTO @payOrderType, @stepName 
    WHILE @@FETCH_STATUS=0

    BEGIN
        DECLARE @b VARCHAR(max), @b2 VARCHAR(max)

        SELECT @b  =  ISNULL(@b ,'') +'['+ ISNULL(@payOrderType ,'')+ '____'+ISNULL(@stepName ,'')+ ']'+ ' NVARCHAR(1000) ,'

        FETCH NEXT FROM a INTO @payOrderType,@stepName    
    END

    CLOSE a
    DEALLOCATE a 
    SELECT @SQL  = 'ALTER table AA(' + SUBSTRING(@b,1, LEN(@b)-1) + ')'
    SELECT @SQL  

END

but i don't know how i should relate rows(userID) with columns ?

Answer 1

You should have determined output structure. Its little risky to have variable output structure.

But here we go:

1. Make your structure simple (remove most of variables) - create view (or use derived table) payorderType + step

   -- should be inner join probably instead of left join
   -- if you use left join you have to isnull s.StepName
   SELECT 
       u.userID, 
       s.StepID, 
       p.TypeName, 
       s.StepName, 
       p.TypeName + '_' + s.StepName StepType, 
       -- Your column can be like `coalesce(p.TypeName + '_' + s.StepName, p.TypeName, s.StepName) StepType` for left joins
       1 Point
   FROM dbo.PayOrderType p 
   INNER JOIN dbo.vStep s ON s.TypeID = p.TypeID 
   INNER JOIN dbo.user u ON u.StepID = s.StepID
   

Make your queries more clear (can all yours fields have null values?. Now you can work with only one column/variable.

2. Now is time for pivot:

   SELECT 
       userID,
       [accounting_payorder], 
       [accounting_cheque], 
       [budget_cheque]
   FROM newview v
   PIVOT(MAX(point) FOR StepType in ([accounting_payorder], [accounting_cheque], [budget_cheque])
   

3. If its necessary you can use dynamic query:

   declare @header varchar(max),
           @columns varchar(max)
   
   select @header = coalesce(@header + ', ', '') + 'isnull(''' + StepType + ''', 0) ' + '[' + StepType + ']',
          @columns = coalesce(@columns + ', ', '') + '[' + StepType + ']'
   from newview
   group by StepType
   
   declare @sqlpvt varchar(4000) -- limited by lenght of exec statement
   set @sqlpvt = 'select userID, $HEADER FROM newview v PIVOT(MAX(point) FOR StepType in ($COLUMNS)'
   -- replace pseudovariables
   set @sqlpvt = replace(@sqlpvt, '$HEADER', @header)
   set @sqlpvt = replace(@sqlpvt, '$COLUMNS', @columns)
   
   print @sqlpvt
   exec (@sqlpvt)
   

Sorry if somethink is wrong (writed on blind), but i think for guide it's enough. But you should prefer end on step 2 (non-static code is dangerous).