CODEWITHSUNDEEP
sql-server
Royi Namir
Royi Namir

Reputation: 148724

Sql server Zip-Up results?

I created this sql DEMO :

I have #tbl1 :

___cola__
  1
  2
  3
  4

and #tbl2 :

_colb_
  a
  b
  c
  d

I want this :

_colb____|__cola____
  a           1
  b           2
  c           3
  d           4

I have found a solution ( bad IMHO)

SELECT table1.cola, table2.colb FROM
(SELECT cola, ROW_NUMBER() OVER (ORDER BY cola) AS rn1 FROM #tbl1) table1,
(SELECT colb, ROW_NUMBER() OVER (ORDER BY colb) AS rn2 FROM #tbl2) table2
WHERE table1.rn1 = table2.rn2

For knowledge , How ELSE can I do it ?

I started with :

SELECT cola , s.f FROM #tbl1 cross apply(select colb as f from #tbl2) s

But there is a problem at the right section.

Upvotes: 2

Views: 170

Answers (2)

AnandPhadke
AnandPhadke

Reputation: 13506

;with CTE1 as (select col1,row_number() over (order by (select 0)) as rn from #tbl1)
,with CTE2 as (select col1,row_number() over (order by (select 0)) as rn from #tbl2)

select c1.col1,c2.col1 from CTE1 c1 inner join CTE2 c2
on c1.rn=c2.rn

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107816

Here's an alternative:

SELECT cola, colb
FROM (
  SELECT a.cola, b.colb,
         rna=row_number() over (partition by colb
                                order by cola),
         rnb=row_number() over (partition by cola
                                order by colb)
  FROM #tbl1 a
  CROSS JOIN #tbl2 b
  ) X
WHERE rna=rnb;

And another:

;WITH
 a1 AS (SELECT cola, ROW_NUMBER() OVER (ORDER BY cola) AS rn1 FROM #tbl1),
 a2 AS (SELECT colb, ROW_NUMBER() OVER (ORDER BY colb) AS rn2 FROM #tbl2)
SELECT a1.cola, a2.colb
FROM a1
JOIN a2 on a1.rn1=a2.rn2;

But to be honest, the 2nd one is just rearranging the parts of the query - the execution plan is exactly the same as what you had. It works, and is the most efficient plan for such a zip-up query.

Upvotes: 6

Related Questions