Huzaifa M Aamir Huzaifa M Aamir - 3 months ago 11
SQL Question

How to summarize records into multiple columns in SQL Server

Sorry if the title does not make sense but I cant seem to find the word for what exactly I am trying to do

I know how to create pivots using group by function and sum, max values in SQL server. However, my question is this

I have data like so:

Filename Branch Name
1 1 Joe Test
2 2 Joseph Test
3 3 Smith Test


Each filename has multiple borrowers associated, I need to join borrowerid to the table on filename when I do so this happens:

Filename Branch Name Borrowerid
1 1 Joe Test 123
1 1 Joe Test 345
1 1 Joe Test 678
2 2 Joseph Test 412
2 2 Joseph Test 214
3 3 Smith Test 333


How can place each borrower in its own column such as borrower1, borrower2, borrower?

For example

Filename Branch Name BorrowerId BorrowerId2 BorrowerId3
1 1 Joe Test 123 345 678
2 2 Joseph Test 412 214
3 3 Smith Test 333


Please assist in helping how to do this

Thank you

Answer

PIVOT and/or DYNAMIC PIVOT is what you are looking for, however, I use a stored procedure for the bulk of my dynamic pivots

Exec [prc-Pivot] 'Select *,NewCol=concat(''Borrowerid'',Row_Number() over (Partition By FileName,Branch,Name Order By Borrowerid)) from YourTable','NewCol','max(Borrowerid)[]','FileName,Branch,Name','count(*)[Records]'

Returns

FileName    Branch  Name         Records    Borrowerid1 Borrowerid2   Borrowerid3
1           1       Joe Test      3         123         345           678
2           2       Joseph Test   2         214         412           NULL
3           3       Smith Test    1         333         NULL          NULL

The stored procedure

CREATE PROCEDURE [dbo].[prc-Pivot] (
    @Source varchar(1000),          -- Any Table or Select Statement
    @PvotCol varchar(250),          -- Field name or expression ie. Month(Date)
    @Summaries varchar(250),        -- aggfunction(aggValue)[optionalTitle]
    @GroupBy varchar(250),          -- Optional additional Group By 
    @OtherCols varchar(500) )       -- Optional Group By or aggregates
AS

--Exec [prc-Pivot] 'Select Year=Year(TR_Date),* From [Chinrus-Series].[dbo].[DS_Treasury_Rates]','''Q''+DateName(QQ,TR_Date)','avg(TR_Y10)[-Avg]','Year','count(*)[Records],min(TR_Y10)[Min],max(TR_Y10)[Max],Avg(TR_Y10)[Avg]'
--Exec [prc-Pivot] '#Temp','Attribute','max(Description)[]','ID','count(*)[Records]'

Set NoCount On

Declare @Vals varchar(max),@SQL varchar(max);
Set @Vals = ''
Set @OtherCols= IsNull(', ' + @OtherCols,'')
Set @Source = case when @Source Like 'Select%' then @Source else 'Select * From '+@Source end
Create Table #TempPvot  (Pvot varchar(100))
Insert Into #TempPvot
Exec ('Select Distinct Convert(varchar(100),' + @PvotCol + ') as Pvot FROM (' + @Source + ') A')
Select @Vals = @Vals + ', isnull(' + Replace(Replace(@Summaries,'(','(CASE WHEN ' + @PvotCol + '=''' + Pvot +  ''' THEN '),')[', ' END),NULL) As [' + Pvot ) From #TempPvot Order by Pvot
Drop Table #TempPvot
Set @SQL = Replace('Select ' + Isnull(@GroupBy,'') + @OtherCols + @Vals + ' From (' + @Source + ') PvtFinal ' + case when Isnull(@GroupBy,'')<>'' then 'Group By ' + @GroupBy + ' Order by ' + @GroupBy else '' end,'Select , ','Select ')
--Print @SQL
Exec (@SQL)