Home / Questions / 83387
Accepted
SeWaNsX
Asked: 2020-07-04 08:29:48 +0800 CST 2020-07-04 08:29:48 +0800 CST

Is it possible and how to transpose results from a column to rows dynamically? SQL Server 2008 R2

  • 772

I indicate my query immediately:

Example, I have the table 'calendar':

Código | Tramo  | días
Semana1|08001800| 5
Semana1|DESCANSO| 2
Semana2|15302100| 4
Semana2|21000800| 1
Semana2|DESCANSO| 2
Semana3|09001900| 5
Semana3|DESCANSO| 2
Semana3|08001800| 2

The table indicated above is a schedule and my question is whether the result query can be obtained in the following way:

Código | Tramo  | Tramo  | Tramo  | Tramo  | Tramo  | Tramo  | Tramo  | Tramo  | Tramo  |
Semana1|08001800|08001800|08001800|08001800|08001800|DESCANSO|DESCANSO|
Semana2|15302100|15302100|15302100|15302100|21000800|DESCANSO|DESCANSO|
Semana3|09001900|09001900|09001900|09001900|09001900|DESCANSO|DESCANSO|08001800|08001800|

In theory, you should read the 'TRAMO' and repeat it the number of times indicated in 'days' and you should repeat it to the right in columns and without repeating the 'code'.

At the moment I haven't advanced in the code since I can't think of how to do it, I was thinking about using CASE but it doesn't work for me.

any ideas? Is that understood? It is dynamic since the query will be extended to the side according to the number of days that the section has.

Please your guidance.

sql

1 Answers

  1. Best Answer

    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.

    In the first place, I base myself on your maximum limit of sections, this is important, because if this number is indeterminate, you have to go through a dynamic query that is one more layer of complexity: find out the maximum and write a statement dynamic on the basis of what I am answering you.

    The other important issue is that in principle, in your example, you do not indicate a field to manage a chronological order, I am going to add it to logic with the order in which you put together the example.

    The first thing is to define the table with the example cases, plus the order we were talking about:

    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 following is to build a table of Days per section, as you mentioned 2 weeks maximum, in the worst case we would have 1 section of 14 days, so we will generate 14 records, as follows:

    DECLARE @DiasPorTramo TABLE (
    Dias    INT
)

INSERT INTO @DiasPorTramo (Dias)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14)

    We will use this table to generate another Intermediate, where basically we are going to generate a record for each day of each section, from here I tell you that it is not an optimal solution and it will be practical depending on the volume of data you handle. To build 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)

    Seeing a single case, for example Semana1, the data would look like this:

    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 queries to reach the desired result:

    Using a GROUP BY, MAXand a CASEwe 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 PIVOTit is also possible (I don't 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 was saying, it is not the most optimal solution, I suggest you wait for some other response before accepting it as good (obviously if it is useful to you).

    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 doesn't require generating an intermediate table, sorry to boot for the most difficult solution, but I wasn't sure I could solve it any other way

    • 2