1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
|
Create Table #T (
IDSAL char(10),
IDMAGASIN char(10)
)
INSERT INTO #T (IDSAL, IDMAGASIN)
VALUES
('AAAAAAAAAA', '1111111111'),
('BBBBBBBBBB', '1111111111'),
('CCCCCCCCCC', '1111111111'),
('DDDDDDDDDD', '2222222222'),
('EEEEEEEEEE', '2222222222')
SELECT
IDMAGASIN,
IDSAL
FROM (
SELECT
IDMAGASIN,
IDSAL
FROM #T
UNION ALL
SELECT
CPT.IDMAGASIN,
Null AS IDSAL
FROM
(Select IDMAGASIN, 5 - (((COUNT(*)-1) % 5)+1) AS NOMBRE from #T GROUP BY IDMAGASIN) AS CPT
-- jointure sur une table quelconque qui comporte au moins 5 lignes pour repeter 5 fois les lignes null
INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS NUM_LIGNE FROM TAB_ACHAT) AS GEN ON (NOMBRE >= NUM_LIGNE)
) AS SRC
ORDER BY IDMAGASIN, ISNULL(IDSAL, 'ZZZZZZZZZZ')
DROP Table #T |
Partager