Cross Join Split of Data

Question

I have a table Employee, another table Department and a relational table:

Employee Table
id  Name
1   A
2   B
3   C
4   D
5   E
6   F

Department Table
id  Name
1   Accounting
2   Finance

Relation Table
id   EmployeeId   DepartmentId

I want to make a dynamic query to be able to distribute in my relational table same quantity of employee for each department, I know that I can use cross join but is going to put all employees in each department. I just want the distribution, for example 3 employees for Account and the other 3 for finance, but this quantity could change. Thanks

score 1 · Accepted Answer · edited May 23 '17 at 12:31

1

That was fun, it seems to work pretty well, employees are distributed randomly in each departments:

select EmployeeId, DepartmentId
from 
(  select 
    ROW_NUMBER() OVER(ORDER BY NEWID()) as RankEmployee
  , Id as EmployeeId
    from Employee
 ) e , 
(  select 
    ROW_NUMBER() OVER(ORDER BY NEWID()) as RankDepartment
  , (select count(1) from Department) as CountDepartment
  , Id as DepartmentId
    from Department
 ) d
where (RankEmployee + RankDepartment) % CountDepartment = 0

The condition

(RankEmployee + RankDepartment) % CountDepartment = 0

will retrieve only one row for each employee.
And ranks are computed randomly with OVER(ORDER BY NEWID()). See this answer for details.

SQLFiddle here.

edited May 23 '17 at 12:31

Community

1
1

answered Apr 11 '13 at 15:16

Cyril Gandon

16,830
14
78
122

@tbag: Added a *comment* regarding this solution. – Bogdan Sahlean Apr 11 '13 at 19:26
question, if I replaces RANK() by ROW_NUMBER() OVER(ORDER BY EmployeeId) is not doing the same way? the same for department – tbag Apr 11 '13 at 19:35
ROW_NUMBER is a better idea since it assured you have no gap in the sequence. The results should be different for every execution since it's random! – Cyril Gandon Apr 12 '13 at 06:48

score 0 · Answer 2 · edited Apr 05 '16 at 13:42

declare @columns varchar(max)='',@str varchar(maX) = '',@columns1 varchar(max)=''

select   @columns = @columns+category+',' from piv group by category
set @columns= left(@columns,len(@columns)-1)

select   @columns1 = @COLUMNS1+'ISNULL(['+category+'],0) AS ['+category+'],' from piv group by category
set @columns1= left(@columns1,len(@columns1)-1)



set @str = 
'select date,'+@columns1+'  from 
(
    select date,category, amount as amounts from piv
)res
pivot
(
    max(amounts)
    for category in ('+@columns+')
)pv'
--print @str
exec(@str)

Cross Join Split of Data

2 Answers2