Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
create table #Personel
(
Ad varchar(100)
)

create table #Masa
(
MasaAdi varchar(100)
)

insert into #Personel values('deneme1')
insert into #Personel values('deneme2')
insert into #Personel values('deneme3')

insert into #Masa values('Masa1')
insert into #Masa values('masa2')
insert into #Masa values('masa3')

select Ad,MasaAdi from
(
select ROW_NUMBER() over(partition by MasaAdi order by ID,Ad ) as Sira,ID,Ad,MasaAdi
from (
select newid() as ID,Ad,MasaAdi 
from #Personel
cross join #Masa
)t
) t2 where Sira = 1 order by Ad

it's creating the result as shown below.

deneme1 - masa2
deneme2 - masa3
deneme1 - masa1 

well,it's to be able to deploy to different tables two times the same personal.

e.g deneme1 - masa2
    deneme1 - masa1

I don't want to do that.I want to do something as shown below.

e.g deneme1-masa2
    deneme2-masa3
    deneme3-masa1

So,I don't want to deploy to different tables two times the same personal.

share|improve this question
    
Why not deneme1-masa1, deneme2-masa2, deneme3-masa3. Please describe the expected output in a clear way. What if Personal and Masa contain different number of rows? –  bernd_k Jun 25 '11 at 7:45
    
@bernd_k - As you mentioned,maybe.I just gave an sample output. –  Cell-o Jun 25 '11 at 7:48
    
you want some unique random assignment? –  bernd_k Jun 25 '11 at 7:50
    
bernd_k - yes, as you said –  Cell-o Jun 25 '11 at 7:51
    
What do you want to do in cases where the number of rows in the two tables is not the same? –  Aaron Bertrand Jun 26 '11 at 23:10

1 Answer 1

up vote 2 down vote accepted
with 
cte1 as
(
    select *, row_number() over(order by Ad) RowNumber
    from #Personel
),
cte2 as
(
    select *, row_number() over(order by newid()) RowNumber
    from #Masa
)
select cte1.Ad, cte2.MasaAdi
from cte1
    join cte2 on
        cte1.RowNumber = cte2.RowNumber
share|improve this answer
    
@Cell-o - did the query work? –  Alex Aza Jun 25 '11 at 20:26
1  
Do you really need both CTEs to be seeded with random ordering? I suspect only one is needed, and the other could just be ordered by a static column (this would be best if the column was clustered or an NC lead column). The extra sort may not be costly in small/trivial cases but I bet it would be easy to come up with cases where the impact is significant. –  Aaron Bertrand Jun 26 '11 at 23:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.