Ading rows with empty values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Ading rows with empty values

Author

Topic

matrixrep
Starting Member

30 Posts

Posted - 2015-02-24 : 15:15:07

I have the following table

MAT GRP STUDENT
---- --- -------
1234 12 ABC123
1234 12 BRE143
1234 12 AGE345

I want a select to return at leats 20 row even
if my grp is having less than 20 students.
The student is then represented by NULL

MAT GRP STUDENT
---- --- -------
1234 12 ABC123
1234 12 BRE143
1234 12 AGE345
... .. ...
1234 12 NULL
1234 12 NULL

I have a lot of groups that contains from 2 to 20 students.

How to add those lines with NULL value for the student ?

Any helps is greatly appreciated.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-24 : 20:59:16

Use UNION

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-02-25 : 11:21:06

[code]
-- *** Test Data ***
CREATE TABLE #t
(
MAT int NOT NULL
,GRP int NOT NULL
,STUDENT varchar(20) NOT NULL
);
INSERT INTO #t
VALUES (1234, 12, 'ABC123')
,(1234, 12, 'BRE143')
,(1234, 12, 'AGE345');
-- *** End Test Data ***

WITH Grps
-- You may already have a table with this.
AS
(
SELECT DISTINCT MAT, GRP
FROM #t
)
,N1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1)
,N2(N) AS (SELECT N1.N FROM N1, N1 N2)
,N(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM N2)
,StudentGrps
AS
(
SELECT MAT, GRP, STUDENT
-- STUDENT is the only thing to order by here.
-- Your real table may be able to order by id or date.
,ROW_NUMBER() OVER (PARTITION BY MAT, GRP ORDER BY STUDENT) AS rn
from #t
)
SELECT G.MAT, G.GRP, S.STUDENT
FROM Grps G
CROSS JOIN N
LEFT JOIN StudentGrps S
ON G.MAT = S.MAT
AND G.GRP = S.GRP
AND N.N = S.rn
WHERE N.N <= 20;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources