SQL server statement, a great god, please help-CodePudding

Date name and order number revenue
2021/1/1 1 set A 12341234 10
2021/1/1 1 set of 43342340 B
2021/1/1 1 set of C 32432421 7
2021/1/1 2 group E 65767867 20
2021/1/2 2 groups, 67856677, 100 E
2021/1/2 1, 56745676, 200 group A
2021/1/2 1 set of 13413240 30 B
2021/1/2 3 group H 45324532 40
2021/1/3 2 group J 75646746 20
2021/1/3 3 set U 97646746 20
2021/1/3 4 set of P 68646746 20
Omit countless rows behind ~ ~ ~

Now form:
1, the order number in the form not repeat;
2, between the two departments, a few names are the same, suggest name & amp; Unit combination,

A predicate sentence:
Name department name & amp; Department 2021/1/1 2021/1/2 single amount of single 2021/1/3 orders ~ ~ ~ ~ ~ ~ ~ ~ ~
A group 1 A & amp; 1 set of 5 1 9
1 B group B & amp; 1 set of 3 2 7
C 1 set of C& 1 set of 4 0 8
E 2 group E & amp; 2 set of 10 8 5
F 3 group F & amp; Three groups of 23 March 1
J 2 groups J& 2 set of 12 2 0
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

CodePudding user response:

Can use the sum (order number) group by date, department, name and quantity, turn to the pivot line column

CodePudding user response:

Is the count is not the sum

 
The CREATE TABLE # A (
DATE DATE, 
Department VARCHAR (20), 
Name a VARCHAR (20), 
The order number INT, 
Income INT 
) 
# INSERT INTO A VALUES (' 2021/1/1 'group' 1 ', 'A', 12341234, 10) 
# INSERT INTO A VALUES (' 2021/1/1 'group' 1 ', 'A', 12341234, 10) 
# INSERT INTO A VALUES (' 2021/1/1 'group' 1 ', 'B', 43342340, 10) 
# INSERT INTO A VALUES (' 2021/1/1 'group' 1 ', 'C', 32432421, 10) 
# INSERT INTO A VALUES (' 2021/1/1 ', '2 groups',' E ', 65767867, 10) 
# INSERT INTO A VALUES (' 2021/1/2 ', '2 groups',' E ', 67856677, 10) 
# INSERT INTO A VALUES (' 2021/1/2 'group' 1 ', 'A', 56745676, 10) 
# INSERT INTO A VALUES (' 2021/1/2 'group' 1 ', 'B', 13413240, 10) 
# INSERT INTO A VALUES (' 2021/1/2 ', '3 groups',' H ', 45324532, 10) 
# INSERT INTO A VALUES (' 2021/1/3 ', '2 groups',' J, 75646746, 10) 
# INSERT INTO A VALUES (' 2021/1/3 ', '3 groups',' U ', 97646746, 10) 
# INSERT INTO A VALUES (' 2021/1/3 ', 'four groups',' P ', 68646746, 10) 

DECLARE @ DATECOLUMN VARCHAR (MAX)=', @ SQL VARCHAR (MAX)='

SELECT @ DATECOLUMN=@ DATECOLUMN + ', '+ QUOTENAME (a. date) FROM # A GROUP BY a. a. date 

The SELECT STUFF (@ DATECOLUMN, 1, 1, ' ') 

The SET @ SQL='
SELECT * FROM (
Date, name, SELECT a. a. a. department, a. name + '+' ' '& amp; "' + + a. department [] name & amp; department, COUNT (a. order number) single quantity FROM # AA GROUP BY a. date, A., a. name) AA 
The PIVOT 
(
MAX (single) FOR the date IN (' + STUFF (@ DATECOLUMN, 1, 1, ' ') + ') 
) PT '

The EXEC (@ SQL) 

DROP TABLE # A

CodePudding user response:

If you want to switch to 0 temporarily just thought of this method:
Add a string concatenation

DECLARE @ DATECOLUMN1 VARCHAR (MAX)='

SELECT @ DATECOLUMN1=@ DATECOLUMN1 + ', ISNULL (' + QUOTENAME (a. date) + ', 0 '+ QUOTENAME (a. date) FROM # A GROUP BY a. a. date

SELECT name, department, the department of [name & amp;], '+ STUFF (@ DATECOLUMN1, 1, 1,' ') + 'FROM (

CodePudding user response:

Do hope the results of A, 1 set of 2021/1/1=5 is how to calculate the single amount?

CodePudding user response:

reference 4 floor started the first response:

do hope results, A, 1 set of 2021/1/1=5 is how to calculate the single amount?

just reflect sample format, not the actual operation result...

CodePudding user response:

reference 5 floor m0_53811915 reply:

Quote: refer to 4th floor started the first response:
do hope results, A, 1 set of 2021/1/1=5 is how to calculate the single amount?

just reflect sample format, not the actual operation result...

Moderator ask you is how to calculate the amount of single field, the amount of single value is where come of, is the amount of the order number?

CodePudding user response:

 
The create table # t (date varchar (20), the department varchar (20), name varchar (20), the order number varchar (20), income int) 

Insert into # t (date, department, name, order number, income) 
Select '2021/1/1' group '1', 'A', '12341234', 10 union all 
Select '2021/1/1' group '1', 'A', '12341235', 10 union all 
Select '2021/1/1' group '1', 'A', '12341236', 10 union all 
Select '2021/1/1' group '1', 'B', '43342340', 9 union all 
Select '2021/1/1' group '1', 'C', '32432421', 7 union all 
Select '2021/1/1', '2' group ', 'E', '65767867', 20 union all 
Select '2021/1/2', '2' group ', 'E', '67856677', 100 union all 
Select '2021/1/2' group '1', 'A', '56745676', 200 union all 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull