表A
表B
通過Data_DirecTory_ID查詢資料，將列轉成行橫向輸出，并匹配表B欄位排序輸出表頭，表頭第一列為時間。
表A中ParameterItem_ID=表B中ID，如果沒有則為空。
假設此時通過查詢 Data_DirecTory_ID = ‘210001’OR Data_DirecTory_ID = ‘210002’
最后輸出成
Data_Time Test1 Test2 Test3 Test3
2020-05-27 Null Stout 8 15.63
2020-05-28 904 Amber 222 15.9
這樣行轉列并替代表頭sql該怎么寫
uj5u.com熱心網友回復：
接SQL,用pivot轉換uj5u.com熱心網友回復：
CREATE TABLE #T1
(ID INT IDENTITY(1,1),
DATE_TIME DATE,
PARAMETERITEMID VARCHAR(10),
DATA_DIRECTORY_ID VARCHAR(10),
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
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+']'
FROM #T2
SET @SQL='WITH CTE
AS
(SELECT B.PARAMETER_ITEM,B.DATE_TIME,C.VALUE FROM
(SELECT * FROM #T2 A
JOIN (SELECT DISTINCT DATE_TIME FROM #T1) AS B ON 1=1) AS B
LEFT JOIN #T1 C ON B.ID=C.PARAMETERITEMID AND B.DATE_TIME=C.DATE_TIME)
SELECT * FROM CTE
PIVOT (MAX(VALUE) FOR PARAMETER_ITEM IN('+@SQL+')) AS B'
EXEC(@SQL)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/27921.html
標籤：疑難問題
上一篇：如何讓訪問特定表用戶無法看見System Views
下一篇：mysql用戶資訊亂碼