Organizar los datos de la tabla basados ​​en columnas

votos
2

Tengo una tabla como ésta y tengo que dividirlo pretende analizar mejor los datos

ID | doc | name    | price | pay 
1  | doc1| PERSON1 | 1     | 1
2  | doc2| PERSON1 | 10    | 0
3  | doc3| PERSON2 | 12    | 1
4  | doc4| PERSON2 | 1     | 0
5  | doc5| PERSON2 | 15    | 0
6  | doc6| PERSON3 | 2     | 1
7  | doc7| PERSON3 | 23    | 0
8  | doc8| PERSON3 | 3     | 0
9  | doc9| PERSON4 | 8     | 0

y necesito una salida como esto y no sé cómo hacerlo!

 name         | price | pay 
 PERSON1      |       | 
 Doc1         | 1     | 1
 Doc2         | 10    | 0
 Total payed  | 1     |
 Total per pay| 10    | 
 Total        | 11    |
 PERSON2      |       | 
 Doc3         | 12    | 1
 Doc4         | 1     | 0
 Doc5         | 15    | 0
 Total payed  | 12    | 
 Total per pay| 16    | 
 Total        | 28    |
 PERSON3      |       | 
 Doc6         | 2     | 1
 Doc7         | 23    | 0
 Doc8         | 3     | 0
 Total payed  | 2     | 
 Total per pay| 26    | 
 Total        | 28    |
 PERSON4      |       | 
 Doc9         | 8     | 0
 Total payed  | 0     | 
 Total per pay| 8     | 
 Total        | 8     |
 Payed        | 15    |
 PER PAY      | 60    |
 Total SUM    | 75    |

¿Es posible hacerlo?

Publicado el 18/12/2018 a las 11:04
fuente por usuario
En otros idiomas...                            


2 respuestas

votos
3

No puedo entender cómo se está analizando los datos si el segundo formato es mejor. Usted puede hacer esto, pero es doloroso:

select doc, price, pay
from ((select doc, price, pay, 2 as ord, name
       from t
      ) union all
      (select distinct name as doc, null, null, 1, name
       from t
       group by name
      ) union all
      (select 'total paid', sum(pay * price), null, 3, name
       from t
       group by name
      ) union all
      (select 'total not paid', sum( (1 - pay) * price), null, 3, name
       from t
       group by name
      ) union all
      (select 'total', sum(pay), null, 5, name
       from t
       group by name
      )
     ) t
order by name, ord;
Respondida el 18/12/2018 a las 11:43
fuente por usuario

votos
2

Algo como esto:

DECLARE @DataSource TABLE
(
    [ID] INT
   ,[doc] VARCHAR(24)
   ,[name] VARCHAR(24)
   ,[price] INT
   ,[pay] TINYINT
);

INSERT INTO @DataSource ([ID], [doc], [name], [price], [pay])
VALUES (1, 'doc1', 'PERSON1', 1, 1)
      ,(2, 'doc2', 'PERSON1', 10, 0)
      ,(3, 'doc3', 'PERSON2', 12, 1)
      ,(4, 'doc4', 'PERSON2', 1, 0)
      ,(5, 'doc5', 'PERSON2', 15, 0)
      ,(6, 'doc6', 'PERSON3', 2, 1)
      ,(7, 'doc7', 'PERSON3', 23, 0)
      ,(8, 'doc8', 'PERSON3', 3, 0)
      ,(9, 'doc9', 'PERSON4', 8, 0);

SELECT MIN([ID]) OVER (PARTITION BY [name]) AS [ID]
      ,[ID] AS [IternalID]
      ,[doc]
      ,[price]
      ,[pay]
FROM @DataSource
UNION ALL
SELECT MIN([ID])
      ,0
      ,[name]
      ,NULL
      ,NULL
FROM @DataSource
GROUP BY [name]
UNION ALL
SELECT [ID]
      ,CASE [column]
            WHEN 'Total payed' THEN 997
            WHEN 'Total per pay' THEN 998
            WHEN 'Total' THEN 999
        END
      ,[column]
      ,[value]
      ,NULL
FROM
(
    SELECT IIF([name] IS NULL, 1000, MIN([ID]))
          ,[name]
          ,SUM(IIF([pay] = 1, [Price], 0))
          ,SUM(IIF([pay] = 1, 0, [Price]))
          ,SUM([Price])
    FROM @DataSource
    GROUP BY GROUPING SETS
    (
        [name]
       ,()
    )
) DS ([ID], [name], [Total payed], [Total per pay], [Total])
UNPIVOT
(
    [value] FOR [column] IN ([Total payed], [Total per pay], [Total])
) UNPVT
ORDER BY [ID]
        ,[IternalID];

Se sa ejemplo de trabajo completo que le puede dar la salida. Puede cambiarlo un poco para que coincida con los datos reales.

Respondida el 18/12/2018 a las 11:51
fuente por usuario

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more