Table A

Table B

Through Data_DirecTory_ID querying data, transfer column transverse output line, and matching table B sort output header fields, header first listed as time,
ParameterItem_ID=ID in table B in table A, if not empty,
Assume that at this point by querying Data_DirecTory_ID='210001' OR Data_DirecTory_ID='210002'
The final output into
Data_Time Test1 Test2 Test3 Test3
The 2020-05-27 Null Stout 8 15.63
The 2020-05-28 904 Amber 222
15.9So turn columns and replace header SQL what to write

CodePudding user response:

Connect the SQL, use the pivot transformation

CodePudding user response:

 
# the CREATE TABLE T1 
(ID INT IDENTITY (1, 1), 
DATE_TIME DATE, 
PARAMETERITEMID VARCHAR (10), 
DATA_DIRECTORY_ID VARCHAR (10), 
The VALUE VARCHAR (10)) 

INSERT INTO # T1 
SELECT '2020-05-27', '10002', '210001', 'STOUT' UNION ALL 
SELECT '2020-05-27', '10003', '210001', '2.0' UNION ALL 
SELECT '2020-05-27', '10004', '210001', '5.22' UNION ALL 
SELECT '2020-05-27', '10005', '210001', 'AAA' UNION ALL 
SELECT '2020-05-27', '10006', '210001', '10.016' UNION ALL 
SELECT '2020-05-27', '10007', '210001', 'BBB' UNION ALL 
SELECT '2020-05-28', '10002', '210001', 'CCCCCC 

GO 

The CREATE TABLE # T2 
(ID VARCHAR (10), 
PARAMETER_ITEM VARCHAR (10)) 

INSERT INTO # T2 
SELECT '10001', 'TEST1' UNION ALL 
SELECT '10002', 'TEST2' UNION ALL 
SELECT '10003', 'TEST3' UNION ALL 
SELECT '10004', 'TEST4' UNION ALL 
SELECT '10005', 'TEST5' UNION ALL 
SELECT '10006', 'TEST6' UNION ALL 
SELECT '10007', 'TEST7' 

GO 

DECLARE @ SQL VARCHAR (1000) 

SELECT @ SQL=ISNULL (@ SQL + ', ', ') + '[]' + PARAMETER_ITEM + ' '
The FROM # T2 

The SET @ SQL='WITH CTE 
AS 
(SELECT p. ARAMETER_ITEM, B.D ATE_TIME, c. ALUE FROM 
(SELECT * FROM # T2 A 
The JOIN (SELECT DISTINCT DATE_TIME FROM # T1) AS B ON 1=1) AS B 
LEFT the JOIN # T1 C ON B.I D=C.P ARAMETERITEMID AND B.D ATE_TIME=C.D ATE_TIME) 

SELECT * FROM CTE 
The PIVOT (MAX (VALUE) FOR PARAMETER_ITEM (+ SQL + '@')) IN the AS B '

The EXEC (@ SQL)