Organizować dane z tabeli oparte na kolumnach

głosy
2

Mam jedną tabelę tak i muszę podzielić ją analizuję dane lepiej

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

i muszę wyjście takiego i nie wiem jak to zrobić!

 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    |

Czy to się da zrobić?

Utwórz 18/12/2018 o 11:04
źródło użytkownik
W innych językach...                            


2 odpowiedzi

głosy
3

Nie mogę pojąć, jak analizują dane, jeżeli drugi format jest lepszy. Można to zrobić, ale jest to bolesne:

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;
Odpowiedział 18/12/2018 o 11:43
źródło użytkownik

głosy
2

Coś takiego:

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];

To sa pełny przykład roboczej, która może dać wyjście. Można go zmienić trochę dopasować swoje prawdziwe dane.

Odpowiedział 18/12/2018 o 11:51
źródło użytkownik

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