Pivot rows to columns

Question

I need to create one master table to present in reporting services, but i have an issue on how to combine the data. To be more specific, i have one table named "Reservaciones" which store information from residences that have been reserved in certain dates.

Now for example, I´m grouping the information by this fields:

R.ClaTrab AS WorkerId
R.ClaUbicacion AS UbicationID
R.ClaEstancia AS ResidenceID
R.FechaIni AS InitialDay
R.FechaFin AS LastDay

And the result is First result**

As you see in the picture we have two rows duplicated, the number four and number five to be exact.

So far this is my code

SELECT  
R.ClaTrab AS WorkerId,
MAX(E.NomEstancia) AS ResidenceName, 
R.ClaUbicacion AS UbicationID, 
R.ClaEstancia AS ResidenceID, 
DATEDIFF(DAY, R.FechaIni, R.FechaFin) AS NumberDays, 
R.FechaIni AS InitialDay,
R.FechaFin AS LastDay
FROM Reservaciones AS R
INNER JOIN Estancias AS E ON E.ClaEstancia = R.ClaEstancia
WHERE E.ClaUbicacionEst = 3
GROUP BY  R.ClaTrab,R.ClaUbicacion, R.ClaEstancia, R.FechaIni, R.FechaFin
ORDER BY R.FechaIni

I Want the result to be like this desire result, but i dont know how to do it, i have tried PIVOT but i cant get the result i want it.

If u need more information please, ask me. thank you very much.

SOLUTION: What i did is use the ROW NUMBER() and OVER PARTITION BY to create a group of workers in the same residence, then PIVOT the result in new columns.

SNIPPET

SELECT * FROM(
SELECT  
MAX(E.NomEstancia) AS ResidenceName,
R.FechaIni AS InitialDay,
R.FechaFin AS LastDay,
DATEDIFF(DAY, R.FechaIni, R.FechaFin) AS NumberDays, 
T.NomTrab AS Worker,
R.ClaUbicacion AS UbicationID, 
R.ClaEstancia AS ResidenceID,
ROW_NUMBER() OVER(PARTITION BY FechaIni,FechaFin, R.ClaUbicacion, R.ClaEstancia ORDER BY T.NomTrab) AS GUEST
FROM Reservaciones AS R
INNER JOIN Estancias AS E ON E.ClaEstancia = R.ClaEstancia
INNER JOIN Trabajadores AS T ON T.ClaTrab = R.ClaTrab
WHERE E.ClaUbicacionEst = 3
GROUP BY T.NomTrab, R.ClaUbicacion, R.ClaEstancia, R.FechaIni,R.FechaFin) AS ONE 
PIVOT( MAX(Worker) FOR GUEST IN ([1],[2],[3])) AS pvt

In the new query I added a new join to obtain the name of the workers

welcome to SO! Please use the following as a guide on how to post a good question on SO: https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Eli, Sep 12 '17 at 20:49
Take a look at this post on dynamic pivots: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query/10404455#10404455 — Xedni, Sep 12 '17 at 20:58
Will there only ever be two workers assigned, if not, is there any maximum that can be hardcoded? — Error_2646, Sep 12 '17 at 20:58
Thanks Xedni, Error_2646, in this particular case the max quantity of workers are two. — deduardolv, Sep 12 '17 at 21:02
Since the max workers are two, then the solution below should work, no? — S3S, Sep 13 '17 at 14:59
this is a particular case, but in some cases the workers can be three — deduardolv, Sep 13 '17 at 17:31

S3S · Answer 1 · 2017-09-13T17:46:33.977

Since there can only be 2 workers, you can use Min and Max.

with cte as(
SELECT  
    R.ClaTrab AS WorkerId,
    MAX(E.NomEstancia) AS ResidenceName, 
    R.ClaUbicacion AS UbicationID, 
    R.ClaEstancia AS ResidenceID, 
    DATEDIFF(DAY, R.FechaIni, R.FechaFin) AS NumberDays, 
    R.FechaIni AS InitialDay,
    R.FechaFin AS LastDay
FROM 
    Reservaciones AS R
INNER JOIN 
    Estancias AS E 
    ON E.ClaEstancia = R.ClaEstancia
WHERE 
    E.ClaUbicacionEst = 3
GROUP BY  
    R.ClaTrab,
    R.ClaUbicacion, 
    R.ClaEstancia, 
    R.FechaIni, 
    R.FechaFin),

cte2 as(
select
    ResidenceName
    ,UbicationID
    ,ResidenceID
    ,NumberDays
    ,InitalDay
    ,LastDay
    ,Worker1 = max(WorkerId)
    ,Worker2 = min(WorkerId)
from
    cte
group by
    ResidenceName
    ,UbicationID
    ,ResidenceID
    ,NumberDays
    ,InitalDay
    ,LastDay)

select
    ResidenceName
    ,UbicationID
    ,ResidenceID
    ,NumberDays
    ,InitalDay
    ,LastDay
    ,Worker1
    ,Worker2 = case when Worker1 = Worker2 then NULL else Worker2 end
from
    cte2

ONLINE DEMO WITH PARTIAL TEST DATA

I couldnt make it work, got errors on the cte2. Needed to create a group by clause — deduardolv, Sep 13 '17 at 17:32
Sorry scsimon, I was very confused wiht my code BUT I FOUND THE SOLUTION — deduardolv, Sep 13 '17 at 20:39
Sorry i was excited, wasn't yelling at you. I have days with this issue — deduardolv, Sep 13 '17 at 21:23

Pivot rows to columns

1 Answers1