CODEWITHSUNDEEP
sqlsql-server
Aryan Sena
Aryan Sena

Reputation: 219

Query for below pattern in Sql server

I am trying to achieve the output shown at bottom, but do not get the idea how can it to be done.

#T1 and #T2 are the input tables. 

CREATE TABLE #T1
(ID VARCHAR(10))
INSERT INTO #T1
SELECT 'A'
UNION 
SELECT 'B'
UNION
SELECT 'C'

CREATE TABLE #T2
(ID VARCHAR(10))
INSERT INTO #T2
SELECT 1
UNION 
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION 
SELECT 5
UNION
SELECT 6
UNION
SELECT 7

and the expected output by using the #T1 and #T2 is

OUTPUT 
ID ID
A  1
B  2
C  3
A  4
B  5
C  6
A  7

Upvotes: 0

Views: 103

Answers (4)

Alfin E. R.
Alfin E. R.

Reputation: 928

Try this one, it's bit longer process in the row numbering and grouping, but pretty much that's all the processes, putting on row number and grouping, pretty simple

with cte as (
    SELECT ID, ceiling(cast(row_number() over
    (order by ID ASC) as decimal)/ 3) as grp FROM #T2
)
SELECT T1.ID, T2.ID FROM 
(SELECT ID, cast(row_number() over
                   (order by ID ASC) as decimal) as grp FROM #T1) T1
LEFT JOIN
(SELECT ID, cast(row_number() over
                   (PARTITION BY grp order by ID ASC) as decimal) as grp FROM cte) T2
ON T1.grp = T2.grp
ORDER BY T2.ID

the result is as follows

enter image description here

Upvotes: 2

Pittsburgh DBA
Pittsburgh DBA

Reputation: 6772

SQL Fiddle: http://sqlfiddle.com/#!18/03a3f/39

SELECT
  T1.ID
  , T2.ID
FROM
  (SELECT
     ROW_NUMBER() OVER (ORDER BY ID ASC) AS ROW_NUM
   , T2.ID FROM T2
  ) AS T2
  INNER JOIN
    (SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS ROW_NUM
     , (SELECT COUNT(*) FROM T1) AS SEQ_COUNT
     , T1.ID FROM T1
    ) AS T1 ON
      T1.ROW_NUM =
        CASE (T2.ROW_NUM % T1.SEQ_COUNT)
          WHEN 0 THEN T1.SEQ_COUNT
          ELSE (T2.ROW_NUM % T1.SEQ_COUNT)
        END
ORDER BY
  T2.ROW_NUM ASC;

Results:

+----+----+
| ID | ID |
+----+----+
| A  |  1 |
| B  |  2 |
| C  |  3 |
| A  |  4 |
| B  |  5 |
| C  |  6 |
| A  |  7 |
+----+----+

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use CTE

with t1 as (
    select *, 
           ROW_NUMBER() over (order by id) Seq 
    from #t1
), t2 as (
    select *, 
           1+(ROW_NUMBER() over (order by id)-1) % (select COUNT(1) from t1) Seq
    from #t2
) 
select t1.Id, t2.Id
from t2
inner join t1 on t1.Seq = t2.Seq
order by t2.Id;

Upvotes: 2

Thom A
Thom A

Reputation: 95574

It's not particularly pretty, however:

WITH T1 AS(
    SELECT ID,
           ROW_NUMBER() OVER (ORDER BY ID) AS RN
    FROM #T1),
T1M AS(
    SELECT *, MAX(RN) OVER () AS MAXRN
    FROM T1),
T2 AS(
    SELECT ID,
           ROW_NUMBER() OVER (ORDER BY ID) AS RN
    FROM #T2)

SELECT T1.ID AS T1ID,
       T2.ID AS T2ID
FROM T1M T1
     JOIN T2 ON T1.RN = CASE T2.RN % T1.MAXRN WHEN 0 THEN T1.MAXRN ELSE T2.RN % T1.MAXRN END
ORDER BY T2.ID ASC;

Edit: note, this assumes that T1 has less rows than T2. if the inverse is true, this won't work.

Upvotes: 4

Related Questions