SQL is very complicated if problem, repeat the question-CodePudding

Table
Type type 1 product name date amount
Floret 2020-07-01 500 106 1818181 9
Small beautiful 2020-08-06 356 135 106 1818
Xiaohua, 2020-09-08, 485, 208, 2635291 8
Xiao zhang, 588, 146, 1456197 2020-06-01 8
Little red 2020-09-12 500 158 146 1456181 8

It is concluded that table 1 (when have type 1, type; When a type 1, type 1=the column type)
Name date amount door products
Floret, little beauty the 2020-07-01, 856, 106, 1818181 9
Xiao zhang, little red 2020-06-01 1088 146 1456181 8197 8
Xiaohua, 2020-09-08, 485, 208, 2635291 8

CodePudding user response:

 



The CREATE TABLE # T 
(
Name a VARCHAR (10), 
DATE DATE, 
The amount of MONEY, 
Type VARCHAR (10), 
Type 1 VARCHAR (10), 
Product VARCHAR (20) 
) 


INSERT INTO # T VALUES (' flowers', '2020-07-01', 500106, ' ', '1818181') 
INSERT INTO # T VALUES (' little beauty ', '2020-08-06', 356135, 106, '1818') 
INSERT INTO # T VALUES (' xiaohua ', '2020-09-08', 485208, ' ', '2635291') 
INSERT INTO # T VALUES (' zhang ', '2020-06-01', 588146, ' ', '1456197') 
INSERT INTO # T VALUES (' red ', '2020-09-12', 500158146, '1456181') 


- sql2017 + 
SELECT 
String_agg (name, ', ') AS the name, 
MIN (date) AS the date and the SUM AS the amount of (amount), ISNULL (NULLIF (type 1, ' '), type) AS type, 
String_agg (products, ', ') AS product 
The FROM # T 
GROUP BY ISNULL (NULLIF (type 1, ' '), type) 

- sql2008 + 

; WITH ct 
AS 
(
SELECT the name, date, amount, ISNULL (NULLIF (type 1, ' '), type) AS types, products FROM # T 
) 
SELECT 
STUFF (SELECT the ', '+ name FROM ct=WHERE type a. types FOR XML PATH ('')), 1, 1, ' ') AS the name, 
MIN (date) AS the date, the SUM (amount) AS the amount, type, 
STUFF (SELECT the ', '+ product FROM ct WHERE type=a. types FOR XML PATH ('')), 1, 1, ' ') AS product 
The FROM ct a 
GROUP BY type 

DROP TABLE # T

CodePudding user response:

 
The CREATE TABLE # A (
The NAME VARCHAR (50), 
DDATE DATE, 
The AMOUNT INT, 
TTYPE INT, 
TTYPE1 INT, 
The PRODUCT VARCHAR (50) 
) 
# INSERT INTO A VALUES (' flowers', '2020-07-01', 500106, NULL, '1818181') 
# INSERT INTO A VALUES (' little beauty ', '2020-08-06', 356135106, '1818') 
# INSERT INTO A VALUES (' xiaohua ', '2020-09-08', 485208, NULL, '2635291') 
# INSERT INTO A VALUES (' zhang ', '2020-06-01', 588146, NULL, '1456197') 
# INSERT INTO A VALUES (' red ', '2020-09-12', 500158146, '1456181') 

SELECT XX. The NAME, XX DDATE, XX. AMOUNT, XX. TTYPE, 
STUFF ((SELECT DISTINCT ', '+ value FROM xxsplit (XX. The PRODUCT', ') FOR XML PATH (' ')), 1, 1, ' ') PRODUCT FROM (
SELECT STUFF ((SELECT DISTINCT ', '+ B.N AME FROM # B, WHERE ISNULL (A.T TYPE1, A.T TYPE)=ISNULL (B.T TYPE1, B.T TYPE) FOR XML PATH ('')), 1, 1, ' ') the NAME, 
DDATE MIN (a. d. DATE), SUM (A.A MOUNT) AMOUNT, ISNULL (A.T TYPE1, A.T TYPE) TTYPE, 
STUFF ((SELECT DISTINCT ', '+ p. RODUCT FROM # B, WHERE ISNULL (A.T TYPE1, A.T TYPE)=ISNULL (B.T TYPE1, B.T TYPE) FOR XML PATH ('')), 1, 1, ' ') PRODUCT 
The FROM # aa GROUP BY ISNULL (A.T TYPE1, A.T TYPE)) XX 


DROP TABLE # A

Good familiar problems

 

The create function xxsplit (@ SQL varchar (MAX), @ xx varchar (20)) 
Returns the @ temp table (value varchar (20)) 
As 
The begin 
Declare @ I int 
Xx, set @ I=charindex (@ @ SQL) 
While @ i>=1 
The begin 
Insert the @ temp values (left (SQL, @ @ I - 1)) 
Set @ SQL=the substring (SQL, @ @ I + 1, len (SQL) @ @ I) 
Xx, set @ I=charindex (@ @ SQL) 
End 
Insert into @ temp values (@ SQL) 
Return 
End

CodePudding user response:

I'm sorry, I don't have accurate expression, is not necessarily the minimum date, if you have type 1 should take type date

CodePudding user response:

 
DECLARE @ data TABLE (
Name NVARCHAR (Max) is NOT NULL, DATE DATE is NOT NULL, the value of the INT the NOT NULL, type VARCHAR (10) NOT NULL, type 1 VARCHAR (10) NOT NULL, product VARCHAR (Max) is NOT NULL) 
INSERT the @ data (name, date, amount, type, type 1, product) 
VALUES (' flowers', '2020-07-01', 500, '106', ', '1818181'), (' little beauty ', '2020-08-06', 356, '135', '106', '1818'), 
(' xiaohua ', '2020-09-08', 485, '208', ', '2635291'), (' zhang ', '2020-06-01', 588, '146', ', '1456197'), (' red ', '2020-09-12', 500, '158', '146', '1456181'); 
- determine t1 type, add Seq column 
WITH t1 AS (SELECT name, date, 
Amount, CASE WHEN type 1="' THEN type ELSE type 1 END, 
Products, ROW_NUMBER () OVER (PARTITION BY CASE WHEN type 1="' THEN type ELSE type 1 END ORDER BY date) seq 
The FROM @ data a), 
- t2 generated column names and products, and taking minimum date, amount and 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull