Advanced Pivoting in MSSQL

 CREATE VIEW [dbo].[vUnionTestTables]
AS
SELECT     PrimaryKey, ForeignKey, 'Test1' AS FromTable, description, amount
FROM         dbo.Test1
UNION
SELECT     PrimaryKey, ForeignKey, 'Test2' AS FromTable, description, amount
FROM         dbo.Test2
UNION
SELECT     PrimaryKey, ForeignKey, 'Test3' AS FromTable, description, amount
FROM         dbo.Test3
GO

 CREATE procedure [dbo].[sp_TablesSnapshot] @DataPart varchar(30) as
DECLARE @FromShort char(3)
If @DataPart = 'Test1' set @FromShort = 'T1'
else
If @DataPart = 'Test2' set @FromShort = 'T2'
else
If @DataPart = 'Test3' set @FromShort = 'T3'
declare @PivotTableX TABLE
(
    ID Int,
    RECORDID VARCHAR(30),
    FROMTABLE VARCHAR(50),
    DESCRIPTION NCHAR(100),
    AMOUNT NVARCHAR(4000),
    Processed NCHAR(1)
)
declare @RowCount Int
Set @RowCount = (Select Count(ForeignKey) from dbo.vUnionTestTables)
Declare @I INT
Set @I = 1
WHILE (@I <= @RowCount)
BEGIN
    DECLARE @PrimaryKey INT, @ForeignKey INT, @FromTable VARCHAR(3), @Description VARCHAR(100), @Amount NVARCHAR(4000)
    SELECT    @PrimaryKey = PrimaryKey
            , @ForeignKey = ForeignKey
            , @FromTable =
                Case FromTable
                WHEN 'Test1' Then 'T1'
                WHEN 'Test2' Then 'T2'
                WHEN 'Test3' Then 'T3'
                END
            , @Description = description
            , @Amount = Amount
            
    FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY ForeignKey) AS [RowNo] FROM dbo.vUnionTestTables) t1
    WHERE t1.RowNo = @I
    And
    FromTable = @DataPart
    Insert into @PivotTableX (ID, RECORDID, FROMTABLE, DESCRIPTION, AMOUNT, Processed)
    values (@PrimaryKey, @ForeignKey, @FromTable, @Description, @Amount, 'F')
    Set @I = @I + 1
END
declare @PivotTable TABLE
(
    ID Int,
    RECORDID VARCHAR(30),
    FROMTABLE VARCHAR(50),
    DESCRIPTION NCHAR(100),
    AMOUNT NVARCHAR(4000),
    Processed NCHAR(1)
)
insert into @PivotTable (ID,RECORDID,FROMTABLE,DESCRIPTION,AMOUNT,PROCESSED) select distinct * from @PivotTableX where ID is not null
Declare @MaxCount int
declare @CountTable table
(
    RecordID int,
    FromTable nchar(3),
    idCount int
)
insert into @CountTable
(RecordID,FromTable,idCount)
select RECORDID, FromTable, COUNT(ID) from @PivotTable
GROUP by RECORDID, FromTable order by RECORDID
Set @MaxCount = (select MAX(idcount) from @CountTable where FromTable = @FromShort and RecordID is not null)
Declare @ColumnsErString varchar(5000)
Declare @ColumnsMeString varchar(5000)
Declare @ColumnsQmrString varchar(5000)
Declare @BuildColumnsString varchar(5000)
Set @BuildColumnsString = 'CREATE table ' + rtrim(@FromShort) + 'Temp (' + ltrim(rtrim(@FromShort)) + '_ForeignKey varchar(30), '
Set @I = 1
WHILE (@I <= @MaxCount)
Begin
    If @I <> 1 set @BuildColumnsString = @BuildColumnsString + ', '
    Set @BuildColumnsString = @BuildColumnsString + rtrim(@FromShort) + 'Desc' + ltrim(str(@I)) + ' nchar(100), ' + rtrim(@FromShort) + 'Amt' + ltrim(str(@I)) + ' nvarchar(4000)'
    Set @I = @I + 1
End
Set @BuildColumnsString = @BuildColumnsString + ')'
Exec(@BuildColumnsString)
Declare @TableIDStep varchar(30), @RecIDStep varchar(30), @RecID varchar(30)
Declare @CurrentFrom varchar(3), @CurrentDesc nchar(100), @CurrentAmt nvarchar(4000)
Declare @InsertStr varchar(5000), @InsertStep int, @CurrentStep varchar(30)

Select @RecIDStep = min(ID) from @PivotTable where FROMTABLE = @FromShort
Set @TableIDStep = 1
Set @InsertStep = 1
while @TableIDStep <= @MaxCount
begin
    while @RecIDStep is not null
    Begin    
        select
            @RecID = RECORDID,
            @CurrentFrom = Fromtable,
            @CurrentDesc = replace(DESCRIPTION,char(39),char(39)+char(39)),
            @CurrentAmt = AMOUNT
        from @PivotTable where FromTable = @FromShort and ID = @RecIDStep
        
        If @CurrentStep <> @RecID Set @InsertStep = 1
        Set @CurrentStep = @RecID
        Declare @CheckInsertStr nvarchar(100)
        Declare @CheckInsertParam nvarchar(100)
        Declare @ReturnCount int
        select @CheckInsertStr = 'select @ReturnCount = count (*) from ' + ltrim(rtrim(@FromShort)) + 'Temp where ' + ltrim(rtrim(@FromShort)) + '_ForeignKey = ' + @RecID
        select @CheckInsertParam = '@ReturnCount int OUTPUT'
        exec sp_ExecuteSql @CheckInsertStr, @CheckInsertParam, @ReturnCount output
            
        If @ReturnCount = 0                         
            Set @InsertStr = 'insert into ' + @CurrentFrom + 'Temp (' + ltrim(rtrim(@FromShort)) + '_ForeignKey,' + @CurrentFrom + 'Desc' + ltrim(STR(@InsertStep)) + ',' + @CurrentFrom + 'Amt' + ltrim(STR(@InsertStep)) + ') values (' + CHAR(39) + @RecID + CHAR(39) + ',' + CHAR(39) + ltrim(rtrim(@CurrentDesc)) + CHAR(39) + ',' + char(39) + ltrim(rtrim(isnull(@CurrentAmt, 0.00))) + CHAR(39) + ')'
        Else
            Set @InsertStr = 'update ' + @CurrentFrom + 'Temp set ' + @CurrentFrom + 'Desc' + ltrim(STR(@InsertStep)) + ' = ' + CHAR(39) + ltrim(rtrim(@CurrentDesc)) + CHAR(39) + ',' + @CurrentFrom + 'Amt' + ltrim(STR(@InsertStep)) + ' = ' + CHAR(39) + ltrim(rtrim(@CurrentAmt)) + CHAR(39) + ' where ' + ltrim(rtrim(@FromShort)) + '_ForeignKey = ' + @RecID
    
        
        exec(@InsertStr)
        
        update @PivotTable set Processed = 'T' where ID = @RecIDStep
        Select @RecIDStep = MIN(ID) from @PivotTable where FROMTABLE = @CurrentFrom and ID <> @RecIDStep and Processed <> 'T'
        Set @InsertStep = @InsertStep + 1
    End
    Set @TableIDStep = @TableIDStep + 1
End

 CREATE procedure [dbo].[sp_BuildRotatedTempData] as

DECLARE @RC int
DECLARE @DataPart varchar(30)

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T1Temp]') AND type in (N'U'))
DROP TABLE [dbo].[T1Temp]
EXECUTE @RC = [dbo].[sp_TablesSnapshot]
   'Test1'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T2Temp]') AND type in (N'U'))
DROP TABLE [dbo].[T2Temp]
EXECUTE @RC = [dbo].[sp_TablesSnapshot]
   'Test2'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T3Temp]') AND type in (N'U'))
DROP TABLE [dbo].[T3Temp]
EXECUTE @RC = [dbo].[sp_TablesSnapshot]
   'Test3'