Multiply rows based on a filed

Transact-SQL
1

hi there, i am a beginner when it comes to SQL and i have an issue which i struggle with for some time.
I have 2 tables: in table1 i have columns: teritory group name, territory member name, territory group ID
in table 2: teritory group name, territory group ID and sales rep.
I need to bring the sales repes from table 2 to table 1 based on the territory ID. My problem is that territory group name can have many territory member name. Lets say for example that in table 1 i have for territory group name=emea i have 4 territory member name: france, germany, italy and spain. and territory group ID =101
in table2 for territory group ID=101 i have 3 sales reps: ana, mary and cami. i need to brong those selas reps from table 2 to table 1 and each of them to have all 4 territory member name.
i left joined those 2 tables but its not right because i need those rows to be multiplied for each sales rep.

i would really apreciate your help.

2

Show us the query you have so far.

3

update [dbo].[Account Gorups2]
set [dbo].[Account Gorups2].[Sales Rep]=[dbo].[anaplam].[Sales Rep Name]
from [dbo].[Account Gorups2]left join [dbo].[anaplam]
on [dbo].[Account Gorups2].[Territory Group ID]=[dbo].[anaplam].[Territory Group ID]
where [dbo].[Account Gorups2].[Territory Group ID]=[dbo].[anaplam].[Territory Group ID] and [dbo].[Account Gorups2].[Territory Group Name]=[dbo].[anaplam].[Territory Name

4

An update can't "multiply" rows.
On your first description I can see the "sales rep name" i one table, but it seems you're trying to update this field from one table to the other.

Now if this is for viewing purpose, you could do:

select *
  from table1 as a
       inner join table2 as b
               on b.[teritory group name]=a.[teritory group name]
              and b.[territory group id]=a.[territory group id]
;

I assume your tables are similar to this (like in your first description):

with table1([teritory group name],[territory member name],[territory group ID])
  as (select *
        from (values('emea','france',101)
                   ,('emea','germany',101)
                   ,('emea','italy',101)
                   ,('emea','spain',101)
             ) as table1([teritory group name],[territory member name],[territory group ID])
     )
    ,table2([teritory group name],[territory group ID],[sales rep])
  as (select *
        from (values('emea',101,'ana')
                   ,('emea',101,'mary')
                   ,('emea',101,'cami')
             ) as table2([teritory group name],[territory group ID],[sales rep])
     )
5

actually i added the sales rep column in table1 and wanted to updated it based on the sales rep from table 2
but when i made the join i realised that its not correct because i needed more rows.

6

table 1
territory group name territory member name territoy group ID
EMEA france 101
EMEA germany 101
EMEA italy 101
EMEA spain 101

table 2
teritory group name territory group ID sales rep
EMEA 101 ana
EMEA 101 mary

and i want table1 to look like this

territory group name territory member name territoy group ID sales rep
EMEA france 101 ana
EMEA germany 101 ana
EMEA italy 101 ana
EMEA spain 101 ana
EMEA france 101 mary
EMEA germany 101 mary
EMEA italy 101 mary
EMEA spain 101 mary

7

You could do this, to generate the results to a temp table:

select a.[territory group name]
      ,a.[member name territoy]
      ,a.[ territoy group id]
      ,b.[sales rep]
  into #tmptable
  from table1 as a
       inner join table2 as b
               on b.[teritory group name]=a.[teritory group name]
              and b.[territory group id]=a.[territory group id]
;

Then you could merge the temp table to table1.
Or maybe it's easier to clean out then content from table1 and then copy the temp table to table1.

1 Like
8

thaink you so much. it was easier than i expected :smiley: