T-SQL——透視PIVOT動態獲取待擴充套件元素集

shanzm發表於2021-10-08
SQL套件

目錄

志銘-2021年10月8日 00:57:00

0.背景說明

在T-SQL——資料透視和逆透視文中介紹了透視和逆透視

使用PIVOT對結果集進行資料透視

SELECT FROM Table PIVOT(任意聚合函式(聚合欄位) FOR 待擴充套件欄位名 IN (待擴充套件元素集)) AS T

其中的 待擴充套件元素集需要一個靜態列表

簡單的說：IN(待擴充套件元素集) ，是不支援動態的子查詢

即不能這樣寫IN (select 帶擴充套件元素 from table)

所以帶擴充套件元素集需要我們就直接羅列出來即可

SELECT * FROM PIVOT(SUN(Scores) FOR Subject IN (語文,數學，外語))

若是實際開發中，需要動態的 查詢待擴充套件元素集

這就需要我們使用動態SQL取構建查詢語句，並執行查詢


1.準備測試資料

  • 學生成績表
----學生成績表
IF OBJECT_ID('tempdb..#tempStudent') IS NOT NULL
BEGIN
   DROP TABLE #tempStudent 
END
CREATE TABLE #tempStudent 
( 
    [Name] varchar(4), 
    [SubjectName] varchar(4), 
    [Scores] int, 
    [Class] varchar(10) )
INSERT INTO #tempStudent
VALUES
( '張三', '語文', 100, '八年級一班' ), 
( '張三', '數學', 90, '三年級二班' ), 
( '張三', '英語', 80, '三年級二班' ), 
( '李四', '語文', 90, '三年級二班' ), 
( '李四', '數學', 70, '三年級二班' ), 
( '李四', '英語', 60, '三年級二班' )
  • 考試科目表
----學生科目表
IF OBJECT_ID('tempdb..#tempSubject') IS NOT NULL
BEGIN
    DROP TABLE #tempSubject;
END;

CREATE TABLE #tempSubject
(
    [SubjectName] VARCHAR(4)
);
INSERT INTO #tempSubject
VALUES
('語文'),
('數學'),
('英語');

2.示例1——利用SELECT迴圈賦值

這裡假定我們有單獨的待擴充套件元素集的表，比如說這裡我們有獨立的課程名稱表#tempSubject
所以我們可以單獨的查詢後拼接出靜態的待擴充套件元素集


----拼接待擴充套件元素集
DECLARE @subjectStr VARCHAR(100)='';--注意一定要初始化為空字串，才能實現下面的累加
SELECT @subjectStr=@subjectStr+SubjectName+',' FROM #tempSubject--拼接擴充套件元素集
SET @subjectStr	=LEFT(@subjectStr,len(@subjectStr)-1)--刪除拼接的最後一個逗號
SELECT @subjectStr--返回：語文,數學,英語


----將透視SQL語句定義為字串，並執行
DECLARE @sql NVARCHAR(1000) = 'SELECT * FROM  #tempStudent PIVOT(SUM(Scores)  FOR [SubjectName] IN ({@subjectStr}))T';--使用“{@subjectStr}”做佔位符
SET @sql = REPLACE(@sql, '{@subjectStr}', @subjectStr);--替換佔位符

SELECT @sql;
EXEC sp_executesql @sql;

-- 結果
-- Name Class      語文          數學          英語
-- ---- ---------- ----------- ----------- -----------
-- 張三   八年級一班      100         NULL        NULL
-- 李四   三年級二班      90          70          60
-- 張三   三年級二班      NULL        90          80

假設我們沒有單獨維護待擴充套件元素集的資料表，即這裡沒有提供#tempSubjcet
我看可以直接去重查詢後#tempStudent中的SubjectName欄位值，進行拼接

----拼接待擴充套件元素集
DECLARE @subjectStr VARCHAR(100) = '';
WITH cteSubject
AS 
(
    SELECT DISTINCT SubjectName FROM #tempStudent
)
SELECT @subjectStr = @subjectStr + SubjectName + ',' FROM cteSubject; --拼接擴充套件元素集
SET @subjectStr = LEFT(@subjectStr, LEN(@subjectStr) - 1); --刪除拼接的最後一個逗號
SELECT @subjectStr; --返回：語文,數學,英語

--……後續操作如示例1

3.示例2——使用遊標

  • 若是有必要的話，可以使用遊標拼接待擴充套件元素集
DECLARE @sql NVARCHAR(1000),
        @subjectStr VARCHAR(1000),
        @first INT;

--建立遊標
DECLARE curStudent CURSOR FAST_FORWARD FOR
SELECT DISTINCT SubjectName FROM #tempStudent;

SET @first = 1;--標誌變數：用於區分是否是第一個拼接字串
SET @sql = N'SELECT * FROM  #tempStudent PIVOT(SUM(Scores)  FOR [SubjectName] IN (';

OPEN curStudent;
FETCH NEXT FROM curStudent INTO @subjectStr;
WHILE @@fetch_status = 0
BEGIN
    IF @first = 0
        SET @sql = @sql + N',';
    ELSE
        SET @first = 0;

    SET @sql = @sql + @subjectStr;

    FETCH NEXT FROM curStudent INTO @subjectStr;
END;
CLOSE curStudent;
DEALLOCATE curStudent;

SET @sql = @sql + N')) AS T;';

EXEC sp_executesql @sql;

4.示例3——使用FOR XML PATH()

其實針對構造"value1,value2,value3"格式的字串，使用FOR XML PATH()函式配合STUFF()函式，是極其的方便

DECLARE @subjectStr VARCHAR(100);

WITH cteStudent AS 
(
SELECT DISTINCT SubjectName FROM  #tempStudent
)
SELECT @subjectStr= STUFF((SELECT ',' + SubjectName FROM cteStudent FOR XML PATH('')),1,1,'')
--這裡@subjectStr=數學、英語、語文	

DECLARE @sql NVARCHAR(1000) = 'SELECT * FROM  #tempStudent PIVOT(SUM(Scores)  FOR [SubjectName] IN ({@subjectStr}))T';--使用“{@subjectStr}”做佔位符
SET @sql = REPLACE(@sql, '{@subjectStr}', @subjectStr);--替換佔位符

SELECT @sql;
EXEC sp_executesql @sql;

5. 參考

相關文章