This is not the most optimal solution, I think there is a possibility to put it in a simpler way, but at least this method would solve your question.
First of all, I rely on your maximum limit of sections, this is important, because if this number is undetermined, you have to pass yes or yes to a dynamic query that is one more layer of complexity: find out the maximum and Write a dynamic sentence based on what I'm answering you.
The other important issue is that in principle, in your example, you do not indicate a field to handle a chronological order, I will add it to logic with the order with which you armed the example.
The first thing is to define the table with the cases of the example, plus the order that we spoke:
DECLARE @Ejemplo TABLE (
NroTramo INT,
Codigo VARCHAR(255),
Tramo VARCHAR(255),
CantTramos INT
)
INSERT INTO @Ejemplo (Codigo, Tramo, CantTramos, NroTramo)
SELECT 'Semana1','08001800',5, 1 UNION
SELECT 'Semana1','DESCANSO',2, 2 UNION
SELECT 'Semana2','15302100',4, 1 UNION
SELECT 'Semana2','21000800',1, 2 UNION
SELECT 'Semana2','DESCANSO',2, 3 UNION
SELECT 'Semana3','09001900',5, 1 UNION
SELECT 'Semana3','DESCANSO',2, 2 UNION
SELECT 'Semana3','08001800',2, 3
The next thing is to assemble a table of Days per section, as you mentioned 2 weeks at the most, in the worst case we would have 1 section of 14 days, so we will generate 14 records, in the following way:
DECLARE @DiasPorTramo TABLE (
Dias INT
)
INSERT INTO @DiasPorTramo (Dias)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14)
This table will be used to generate another Intermedia, where basically we will generate a record for each day of each section, from here I will tell you that it is a not optimal solution and it will be practical depending on the volume of data that drive To assemble our intermediate table:
DECLARE @TablaIntermedia TABLE (
NroTramo INT,
Codigo VARCHAR(255),
Tramo VARCHAR(255),
NroDia INT,
TramoColumna VARCHAR(255)
)
INSERT INTO @TablaIntermedia (NroTramo, Codigo, Tramo, NroDia)
SELECT E.NroTramo,
E.Codigo,
E.Tramo,
ROW_NUMBER() OVER(PARTITION BY E.Codigo ORDER BY E.Codigo,E.NroTramo)
FROM @Ejemplo E
LEFT JOIN @DiasPorTramo D
ON 1 = 1
AND E.CantTramos >= D.Dias
ORDER BY E.Codigo, E.NroTramo
UPDATE @TablaIntermedia
SET TramoColumna = 'Tramo' + CONVERT(VARCHAR, NroDia)
Viewing a single case, for example Semana1 , this would be the data:
NroTramo Codigo Tramo NroDia TramoColumna
======== ========== =========== ======= ============
1,00 Semana1 08001800 1,00 Tramo1
1,00 Semana1 08001800 2,00 Tramo2
1,00 Semana1 08001800 3,00 Tramo3
1,00 Semana1 08001800 4,00 Tramo4
1,00 Semana1 08001800 5,00 Tramo5
2,00 Semana1 DESCANSO 6,00 Tramo6
2,00 Semana1 DESCANSO 7,00 Tramo7
With these data it is much clearer how we can make some consultations to reach the desired result:
Using GROUP BY , MAX and% CASE we can do this (in practice we would have to add the rest of the sections up to 14):
SELECT Codigo,
MAX(CASE WHEN TramoColumna = 'Tramo1' THEN Tramo END) 'Tramo1',
MAX(CASE WHEN TramoColumna = 'Tramo2' THEN Tramo END) 'Tramo2',
MAX(CASE WHEN TramoColumna = 'Tramo3' THEN Tramo END) 'Tramo3',
MAX(CASE WHEN TramoColumna = 'Tramo4' THEN Tramo END) 'Tramo4',
MAX(CASE WHEN TramoColumna = 'Tramo5' THEN Tramo END) 'Tramo5',
MAX(CASE WHEN TramoColumna = 'Tramo6' THEN Tramo END) 'Tramo6',
MAX(CASE WHEN TramoColumna = 'Tramo7' THEN Tramo END) 'Tramo7',
MAX(CASE WHEN TramoColumna = 'Tramo8' THEN Tramo END) 'Tramo8',
MAX(CASE WHEN TramoColumna = 'Tramo9' THEN Tramo END) 'Tramo9'
FROM @TablaIntermedia
GROUP BY Codigo
Using PIVOT is also possible (I do not remember if you have it available in 2008):
SELECT Codigo, Tramo1, Tramo2, Tramo3, Tramo4, Tramo5, Tramo6, Tramo7, Tramo8, Tramo9
FROM
(
SELECT Codigo, Tramo, TramoColumna
FROM @TablaIntermedia
) s
PIVOT
(
MAX(Tramo) FOR TramoColumna IN (Tramo1, Tramo2, Tramo3, Tramo4, Tramo5, Tramo6, Tramo7, Tramo8, Tramo9 )
) p
In both cases the output is the same:
Codigo Tramo1 Tramo2 Tramo3 Tramo4 Tramo5 Tramo6 Tramo7 Tramo8 Tramo9
======= =========== =========== =========== =========== =========== =========== =========== =========== ===========
Semana1 08001800 08001800 08001800 08001800 08001800 DESCANSO DESCANSO NULL NULL
Semana2 15302100 15302100 15302100 15302100 21000800 DESCANSO DESCANSO NULL NULL
Semana3 09001900 09001900 09001900 09001900 09001900 DESCANSO DESCANSO 08001800 08001800
As I said, it is not the most optimal solution, I suggest you wait for some other answer before giving it for good (obviously if you find it useful).
Update
A much better solution is the following:
SELECT Codigo,
MAX(CASE WHEN 1 BETWEEN (Acumulado - CantTramos) + 1 AND Acumulado THEN Tramo END),
MAX(CASE WHEN 2 BETWEEN (Acumulado - CantTramos) + 1 AND Acumulado THEN Tramo END),
MAX(CASE WHEN 3 BETWEEN (Acumulado - CantTramos) + 1 AND Acumulado THEN Tramo END),
MAX(CASE WHEN 4 BETWEEN (Acumulado - CantTramos) + 1 AND Acumulado THEN Tramo END),
MAX(CASE WHEN 5 BETWEEN (Acumulado - CantTramos) + 1 AND Acumulado THEN Tramo END),
MAX(CASE WHEN 6 BETWEEN (Acumulado - CantTramos) + 1 AND Acumulado THEN Tramo END),
MAX(CASE WHEN 7 BETWEEN (Acumulado - CantTramos) + 1 AND Acumulado THEN Tramo END),
MAX(CASE WHEN 8 BETWEEN (Acumulado - CantTramos) + 1 AND Acumulado THEN Tramo END),
MAX(CASE WHEN 9 BETWEEN (Acumulado - CantTramos) + 1 AND Acumulado THEN Tramo END)
FROM (SELECT T.*,
SUM(CantTramos) OVER (partition by Codigo order by Codigo, NroTramo) as 'Acumulado'
from (SELECT
Codigo,
Tramo,
NroTramo,
CantTramos
FROM @Ejemplo) t
) TT
GROUP BY Codigo
This solution does not require generating an intermediate table, sorry for starting for the most difficult solution, but I was not sure I could solve it in another way