Is such a set of raw data:
Single number serial number factors influence department opinion
2021010701 001 material
2021010701 001 is expected to tooling
2021010701 001 people short of personnel
2021010702 001 material
2021010702 002 is no ware
When a single number and serial number are at the same time, will be back three columns of data together, and each field in the symbol interval, empty information need not symbol, has been in existence or repeating information not stack:
Number number factors influence reason department opinion summary information
2021010701 001 materials | equipment | personnel material deficiency affects materials | + tooling influence | short of personnel, material affect people
2021010702 001 material impact effects
2021010702 002 no ware material + no ware
CodePudding user response:
Using STRING_AGG functionCodePudding user response:
The CREATE TABLE # A (
ORDERID INT,
ID INT,
The FACTORS VARCHAR (20),
CAUSES a VARCHAR (20),
DEPT_REMARK VARCHAR (20)
)
# INSERT INTO A VALUES (2021010701, 1, ' ' ' ', 'material effect')
# INSERT INTO A VALUES (2021010701, 1, 'is',' tooling effect ', ')
# INSERT INTO A VALUES (2021010701, 1, 'people', 'lack of personnel,' ')
# INSERT INTO A VALUES (2021010702, 1, ' ' ' ', 'material effect')
# INSERT INTO A VALUES (2021010702, 2, 'material', 'no' ware, ' ')
SELECT XX. ORDERID, XX. ID, XX. The FACTORS, XX. The CAUSES, XX. DEPT_REMARK,
ISNULL (
(CASE XX. The FACTORS WHEN NULL THEN 'ELSE XX. The FACTORS END) +
(CASE XX. CAUSES the WHEN NULL THEN 'ELSE' + '+ XX, CAUSES the END),' ') +
ISNULL (XX) DEPT_REMARK, ' ')
The FROM (
The SELECT A.O RDERID, Anderson D,
STUFF ((SELECT (CASE b. ACTORS WHEN 'THEN' ELSE '/' + b. ACTORS END) FROM # B, WHERE A.O RDERID=B.O RDERID AND Anderson, D=B.I D FOR XML PATH (' ')), 1, 1, ' ') FACTORS,
STUFF ((SELECT (CASE biggest AUSES the WHEN 'THEN' ELSE '/' + biggest AUSES END) FROM # B, WHERE A.O RDERID=B.O RDERID AND Anderson, D=B.I D FOR XML PATH (' ')), 1, 1, ' ') CAUSES,
STUFF ((SELECT (CASE B.D EPT_REMARK WHEN 'THEN' ELSE '/' + B.D EPT_REMARK END) FROM # B, WHERE A.O RDERID=B.O RDERID AND Anderson, D=B.I D FOR XML PATH (' ')), 1, 1, ' ') DEPT_REMARK
The FROM # aa GROUP BY A.O RDERID, Anderson D) XX
DROP TABLE # A
CodePudding user response:
Duplicate information not stack, how to deal with?Single number serial number factors influence department opinion
2021010701 001 material
2021010701 001 tooling material influence
2021010701 001 people short of personnel
2021010701 001 material purchasing stock
Column 3 line 2 and line 4 'material' repeated, influence factors can not achieve repeat?
Number number factors influence reason department opinion summary information
2021010701 001 material tooling influence | | people short of staff | purchasing stock the material influence | man + tooling influence | | short of personnel purchasing stock + materials
CodePudding user response:
With distinct
The CREATE TABLE # A (
ORDERID INT,
ID INT,
The FACTORS VARCHAR (20),
CAUSES a VARCHAR (20),
DEPT_REMARK VARCHAR (20)
)
# INSERT INTO A VALUES (2021010701, 1, ' ' ' ', 'material effect')
# INSERT INTO A VALUES (2021010701, 1, 'is',' tooling effect ', ')
# INSERT INTO A VALUES (2021010701, 1, 'people', 'lack of personnel,' ')
# INSERT INTO A VALUES (2021010702, 1, ' ' ' ', 'material effect')
# INSERT INTO A VALUES (2021010701, 1, 'is',' no 'ware,' ')
SELECT XX. ORDERID, XX. ID, XX. The FACTORS, XX. The CAUSES, XX. DEPT_REMARK,
ISNULL (
(CASE XX. The FACTORS WHEN NULL THEN 'ELSE XX. The FACTORS END) +
(CASE XX. CAUSES the WHEN NULL THEN 'ELSE' + '+ XX, CAUSES the END),' ') +
ISNULL (XX) DEPT_REMARK, ' ')
The FROM (
The SELECT A.O RDERID, Anderson D,
STUFF ((SELECT DISTINCT (CASE b. ACTORS WHEN 'THEN' ELSE '/' + b. ACTORS END) FROM # B, WHERE A.O RDERID=B.O RDERID AND Anderson, D=B.I D FOR XML PATH (' ')), 1, 1, ' ') FACTORS,
STUFF ((SELECT DISTINCT (CASE biggest AUSES the WHEN 'THEN' ELSE '/' + biggest AUSES END) FROM # B, WHERE A.O RDERID=B.O RDERID AND Anderson, D=B.I D FOR XML PATH (' ')), 1, 1, ' ') CAUSES,
STUFF ((SELECT DISTINCT (CASE B.D EPT_REMARK WHEN 'THEN' ELSE '/' + B.D EPT_REMARK END) FROM # B, WHERE A.O RDERID=B.O RDERID AND Anderson, D=B.I D FOR XML PATH (' ')), 1, 1, ' ') DEPT_REMARK
The FROM # aa GROUP BY A.O RDERID, Anderson D) XX
DROP TABLE # A
CodePudding user response:
Use # 2 data
The CREATE TABLE # A (
ORDERID INT,
ID INT,
The FACTORS VARCHAR (20),
CAUSES a VARCHAR (20),
DEPT_REMARK VARCHAR (20)
)
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnull