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.

i need to make Sp to distribute students to their sections the procedure take 2 string parameters StuID and SecID

in case I've send '1,2,3,4,5' as StuID and 'a,b' as SecID i'm using spliting function which well return tables

Tb1 |   Tb2
1   |    a
2   |    b
3   |
4   |  
5   |

how can i get the following result

1 a
2 b
3 a
4 b
5 a
....

I've tried to do it via cross join but it did not show the result i want

select US.vItem as UserID,SE.vItem as Section 
from split(@pUserID,',') us 
cross join split(@pSectionID,',') se
share|improve this question
    
In general it looks fine. What results are you getting? Perhaps there's a problem with your split function? What result do you get from split(@pUserID,',')? –  David Cummins Oct 7 '13 at 8:16

2 Answers 2

you can do it by following

CREATE TABLE #Temp(id int)

insert into #Temp values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

CREATE TABLE #Temp2(val varchar(2))

insert into #Temp2 values('a'),('b'),('c')

--Get temp table using split funtion

DECLARE @var int 
SELECT @var = COUNT(0)FROM #Temp2

;WITH 
    CTE AS (SELECT ROW_NUMBER()OVER(ORDER BY ID)AS SNO,* FROM #Temp),
    CTE2 AS (SELECT ROW_NUMBER()OVER(ORDER BY val)AS SNO,* FROM #Temp2) 
SELECT * FROM Cte c JOIN CTE2 d ON d.SNO= CASE WHEN c.Sno%@var = 0 THEN @var ELSE c.SNO%@var END 
ORDER BY c.SNO
;

DROP TABLE #Temp
DROP TABLE #Temp2
share|improve this answer

You can get there with a little modulo math. First you need to assign numbers to the students and to the sections. We also need to know how many sections we have. The following two queries show how to do that:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.Students(Name NVARCHAR(10));
CREATE TABLE dbo.Sections(Name NVARCHAR(10));
INSERT INTO dbo.Students VALUES
 ('St1'),
 ('St2'),
 ('St3'),
 ('St4'),
 ('St5'),
 ('St6'),
 ('St7'),
 ('St8'),
 ('St9'),
 ('St10');

INSERT INTO dbo.Sections VALUES
 ('Sec1'),
 ('Sec2'),
 ('Sec3');

Query 1:

SELECT COUNT(1)OVER() AS total_count,
       ROW_NUMBER()OVER(ORDER BY name) AS number,
       name 
  FROM dbo.Sections;

Results:

| TOTAL_COUNT | NUMBER | NAME |
|-------------|--------|------|
|           3 |      1 | Sec1 |
|           3 |      2 | Sec2 |
|           3 |      3 | Sec3 |

Query 2:

SELECT ROW_NUMBER()OVER(ORDER BY name) AS number,
       name 
  FROM dbo.Students;

Results:

| NUMBER | NAME |
|--------|------|
|      1 |  St1 |
|      2 | St10 |
|      3 |  St2 |
|      4 |  St3 |
|      5 |  St4 |
|      6 |  St5 |
|      7 |  St6 |
|      8 |  St7 |
|      9 |  St8 |
|     10 |  St9 |

Now you can use modulo to distribute the students:

Query 3:

WITH NumberedSections AS(
SELECT COUNT(1)OVER() AS total_count,
       ROW_NUMBER()OVER(ORDER BY name) AS number,
       name 
  FROM dbo.Sections
),
NumberedStudents AS(
SELECT ROW_NUMBER()OVER(ORDER BY name) AS number,
       name 
  FROM dbo.Students
)
SELECT STU.name student_name,SEC.name section_name
FROM NumberedStudents STU
JOIN numberedSections SEC
  ON (STU.number - 1) % SEC.total_count = (SEC.number - 1)
ORDER BY STU.name;

Results:

| STUDENT_NAME | SECTION_NAME |
|--------------|--------------|
|          St1 |         Sec1 |
|         St10 |         Sec2 |
|          St2 |         Sec3 |
|          St3 |         Sec1 |
|          St4 |         Sec2 |
|          St5 |         Sec3 |
|          St6 |         Sec1 |
|          St7 |         Sec2 |
|          St8 |         Sec3 |
|          St9 |         Sec1 |

Important for this to produce repeatable results is that both the students and the sections are unique. The query will still produce correct results if you have eg two students with the same name, but you might get different results if you run the query again.

share|improve this answer

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.