CODEWITHSUNDEEP
sql-server
Felicity Stark
Felicity Stark

Reputation: 31

I need to join rows in a query (pivot)

I have some troubles with my query pivot:

SELECT *
FROM
(
    SELECT [usuario],
           [codtrab],
           [tna],
           [hora],
           [tipo],
           [fecha]
    FROM prueba
) AS SourceTable PIVOT(AVG([tna]) FOR [tipo] IN([entrada], [salida])) AS PivotTable

This return a table 

usuario | codtrab | hora                fecha       entrada salida
name1   | 600     | 08:02:00.0000000    2019-05-17  0       NULL
name1   | 600     | 16:02:00.0000000    2019-05-17  NULL    1
name2   | 200     | 08:10:00.0000000    2019-05-17  0       NULL
name2   | 200     | 16:10:00.0000000    2019-05-17  NULL    1
name3   | 400     | 08:20:00.0000000    2019-05-17  0       NULL
name3   | 400     | 16:20:00.0000000    2019-05-17  NULL    1

I want to show the data into a table like this:

usuario codtrab hora                fecha       entrada salida
name1   600     08:02:00.0000000    2019-05-17  0       1
name2   200     08:10:00.0000000    2019-05-17  0       1
name3   400     08:20:00.0000000    2019-05-17  0       1

Upvotes: 2

Views: 44

Answers (2)

M. Kanarkowski
M. Kanarkowski

Reputation: 2195

I.e. you can aggregated the outcome of your pivot. I'm not sure what are your requirements regarding i.e. hora.

WITH Pivoted
AS
(
SELECT 
    *
FROM
(
    SELECT [usuario],
           [codtrab],
           [tna],
           [hora],
           [tipo],
           [fecha]
    FROM prueba
    group by [usuario], 
) AS SourceTable PIVOT(AVG([tna]) FOR [tipo] IN([entrada], [salida])) AS PivotTable
) 
SELECT 
  [usuario],
  [codtrab],
  min([hora]) as [hora],
  [fecha],
  max(entrada) as entrada,
  max(salida) as salida
FROM Pivoted
GROUP BY [usuario], [codtrab], [fecha] ;

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

Can you try after removing column [hora] from the selection? As this column has multiple value for a name like- 'Name1', i think this creating the double rows.

SELECT *
FROM
(
    SELECT [usuario],
       [codtrab],
       [tna],
       --[hora],
       [tipo],
       [fecha]
FROM prueba
) AS SourceTable 
PIVOT(AVG([tna]) FOR [tipo] IN([entrada], [salida])) AS PivotTable

Upvotes: 2

Related Questions