Create procedure to produce table joining different tables where tables can have multiple rows repeating?

Question

I have three tables T1, T2 and T3 with every table as bpm_no common in all the tables. Table T1 is the main table. T1 have every row with unique bpm_no (not repeating). Table T2 includes two column one is bpm_no and another is user, here one bpm_no can occur multiple times with different users working on same bpm_no. Table T3 includes two column one is bpm_no and another is total_outstanding, here also one bpm_no can occur multiple times with different total_outstanding, as one bpm_no can have different outstanding from different bank.

Now, I have to write the procedure which will construct a table using all the above tables (inner join) and it must include three column, one which will include bpm_no (unique for each row), another with users with comma separated for each individual bpm_no, and the last column with the sum of total_outstanding. Idea is to have the end table with every bpm_no as unique and its resulting values with comma separated and loan as sum.

I, tried using Views, but It will not work as in that case I will have to create lots of views. Kindly suggest other ways.

Below is the table structure for better understanding:

Table T1:
|---------------------|------------------|
|      **bpm_no**     |     **name**     |
|---------------------|------------------|
|      abc_0011       |      john        |
|---------------------|------------------|

Table T2:
|---------------------|------------------|
|      **bpm_no**     |     **user**     |
|---------------------|------------------|
|      abc_0011       |      abc         |
|---------------------|------------------|
|      abc_0011       |      bcd         |
|---------------------|------------------|
|      abc_0011       |      lmn         |
|---------------------|------------------|

Table T3:
|---------------------|------------------|
|      **bpm_no**     |     **loan_os**  |
|---------------------|------------------|
|      abc_0011       |      14,500      |
|---------------------|------------------|
|      abc_0011       |      4000        |
|---------------------|------------------|
|      abc_0011       |      5000        |
|---------------------|------------------|

Final Table required:
|---------------------|------------------|------------------|
|      **bpm_no**     |     **user**     |     **loan_os**  |
|---------------------|------------------|------------------|
|     abc_0011        |   abc,bcd,lmn    |     23,500       |
|---------------------|------------------|------------------|

Your sample data and desired output do not have much of anything in common. Where are all those values in your sample data? And your tables can't possibly have bpm_no as the primary key because your sample data has duplicate values for that column. The topic at hand appears to be generating a delimited list of values. This has been asked and answered hundreds if not thousands of times on SO. — Sean Lange, May 14 '18 at 13:08
@SeanLange. Can you please provide a link for the answer. Or can you suggest what are the ways to achieve the above query. — Md Kamran Azam, May 14 '18 at 13:12
https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — Sean Lange, May 14 '18 at 13:13
@SeanLange. The link you gave shows how to make comma separated value, I can do that via Stuff but I want a common procedure to achieve my final table which will also include the summing of loan_os column. How can we do that in a single procedure. Also, is there anyway to achieve this using cursor? — Md Kamran Azam, May 14 '18 at 13:17
This seems to be a matter of using groupby (douplicate of: https://stackoverflow.com/questions/39922986/pandas-group-by-and-sum) Your only difference is that: 1. one of your tables does nothing 2. you have 2 tables you want to use groupby on, then join the results. — ntg, May 14 '18 at 13:17
You do NOT want to use a cursor here. You need to think about what you want to do to a column, not what you want to do for each row. To get a SUM you need to use SUM. https://learn.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql?view=sql-server-2017 — Sean Lange, May 14 '18 at 13:19
@SeanLange. Understood, I already have used Sum function and all your approaches, but end result is not as expected. When I run sum statement separately, its works fine and display result for every bpm_no as unique, but whenever I try this by using procedure, it gives unexpected output like bpm_no repeating several time, also the sum value turns up wrong. Just, help me in achieving this using procedure. If you want I can share my procedure. — Md Kamran Azam, May 14 '18 at 13:24
You should always share what you have tried. Often times it just needs a minor tweak. — Sean Lange, May 14 '18 at 13:44

score 1 · Answer 1 · answered May 14 '18 at 13:40

If you're already on SQL Server 2017 you can use string_agg() to get the list of users:

SELECT [T3].[bpm_no],
       [T2].[user],
       [T3].[loan_os]
       FROM (SELECT [T3].[bpm_no],
                    sum([T3].[loan_os]) [loan_os]
                    FROM [T3]
                    GROUP BY [T3].[bpm_no]) T3
            LEFT JOIN (SELECT [T2].[bpm_no],
                              string_agg([T2].[user], ',') [user]
                              FROM [T2]
                              GROUP BY [T2].[bpm_no]) [T2]
                      ON [T2].[bpm_no] = [T3].[bpm_no];

DxTx · Accepted Answer · 2018-05-14T13:39:21.973

Try this one...

Table Script and Sample data

CREATE TABLE [T1](
    [bpm_no] [nvarchar](50) NULL,
    [name] [nvarchar](50) NULL
) 


CREATE TABLE [T2](
    [bpm_no] [nvarchar](50) NULL,
    [user] [nvarchar](50) NULL
) 


CREATE TABLE [T3](
    [bpm_no] [nvarchar](50) NULL,
    [loan_os] [decimal](18, 0) NULL
) 

INSERT [T1] ([bpm_no], [name]) VALUES (N'abc_0011', N'john')

INSERT [T2] ([bpm_no], [user]) VALUES (N'abc_0011', N'abc')
INSERT [T2] ([bpm_no], [user]) VALUES (N'abc_0011', N'bcd')
INSERT [T2] ([bpm_no], [user]) VALUES (N'abc_0011', N'lmn')

INSERT [T3] ([bpm_no], [loan_os]) VALUES (N'abc_0011', CAST(14500 AS Decimal(18, 0)))
INSERT [T3] ([bpm_no], [loan_os]) VALUES (N'abc_0011', CAST(4000 AS Decimal(18, 0)))
INSERT [T3] ([bpm_no], [loan_os]) VALUES (N'abc_0011', CAST(5000 AS Decimal(18, 0)))

Query

SELECT t1.bpm_no, 
       sq1.[user], 
       sq2.loan_os 
FROM   t1 
       INNER JOIN (SELECT bpm_no, 
                          Stuff((SELECT ', ' + [user] 
                                 FROM   t2 t21 
                                 WHERE  t21.bpm_no = t22.bpm_no 
                                 FOR xml path('')), 1, 2, '') AS [user] 
                   FROM   t2 t22 
                   GROUP  BY bpm_no) sq1 
               ON t1.bpm_no = sq1.bpm_no 
       INNER JOIN (SELECT bpm_no, 
                          Sum(loan_os) AS loan_os 
                   FROM   t3 
                   GROUP  BY bpm_no) sq2 
               ON t1.bpm_no = sq2.bpm_no

Note: If you want to get values (including null values) for all bpm_no in T1 main table, use left join instead of inner join.

Query (Same expected outcome without T1 main table)

SELECT sq1.bpm_no, 
       sq1.[user], 
       sq2.loan_os 
FROM   (SELECT bpm_no, 
               Stuff((SELECT ', ' + [user] 
                      FROM   t2 t21 
                      WHERE  t21.bpm_no = t22.bpm_no 
                      FOR xml path('')), 1, 2, '') AS [user] 
        FROM   t2 t22 
        GROUP  BY bpm_no) sq1 
       INNER JOIN (SELECT bpm_no, Sum(loan_os) AS loan_os 
                   FROM   t3 
                   GROUP  BY bpm_no) sq2 
               ON sq1.bpm_no = sq2.bpm_no

Output

+----------+---------------+---------+
|  bpm_no  |     user      | loan_os |
+----------+---------------+---------+
| abc_0011 | abc, bcd, lmn |   23500 |
+----------+---------------+---------+

Demo: http://www.sqlfiddle.com/#!18/b6362/2/0

If I'm misunderstood, please let me know.

Thanks, will try that and will revert you. – Md Kamran Azam May 14 '18 at 13:25 — Md Kamran Azam, May 14 '18 at 13:25
Great Explanation sir. Exactly what I require. Thankyou. – Md Kamran Azam May 14 '18 at 13:49 — Md Kamran Azam, May 14 '18 at 13:49
@MDKAMRANAzam, Welcome. Thank you. Glad to hear that. – DxTx May 14 '18 at 13:58 — DxTx, May 14 '18 at 13:58

Create procedure to produce table joining different tables where tables can have multiple rows repeating?

2 Answers2