近日在專案中遇到一個問題： 如何在報表中統計JSON格式存盤的資料？
例如有個調查問卷記錄表，記錄每個問題的答案， 其結構示意如下（橫表設計）
| Id | user | date | Q1_Answer | Q2_Answer | Q3_Answer |
| 行Id | 答題用戶 | 答題日期 | 問題一結果 | 問題二結果 | 問題三結果 |
在[Q1_Answer]、[Q2_Answer]、[Q3_Answer]中記錄的資料格式是JSON檔案內容，因為是選項值，而且考慮到可能有多選， 所以存盤的格式如下：
1 [
{"code":"a", "desc":"Jan."},
{"code":"b", "desc":"Feb."}
]
其中 code 表示選項， desc 表示選項的文字描述，
現在，用戶想用PowerBI 來實作對結果的統計，有如下幾個問題：
- 在Power BI中，無法直接從JSON資料中讀取到選項值
- 如果是多選，又該如何處理，
比較適合分析的資料結構應該長這樣：
| 行Id | 答題用戶 | 答題日期 | 問題編號 | 用戶選項 | 選項文字 |
| 1 | user1 | 2021-6-26 | Q1 | A | Jan. |
| 2 | user1 | 2021-6-26 | Q2 | A | Mon. |
| 3 | user1 | 2021-6-26 | Q2 | B | Tue. |
| 4 | user1 | 2021-6-26 | Q3 | A | Swimming |
| 6 | user2 | 2021-6-26 | Q1 | B | Feb. |
| 7 | user2 | 2021-6-26 | Q2 | ... | ... |
注意，上述Q2用戶填了2個選項， 本身問卷設定就是支持多選的， 用JSON檔案結構保存資料， 主要是為了方便采集和資料存取，因此要額外做些資料處理， 使采集的資料便于統計，
筆者經過一些調查， 發現可以結合使用UNPIVOT和OPENJSON方法來達到理想的效果， 具體程序如下：
準備表格和初始化資料
-- 1 create table Create Table T_Questionaire(id int identity(1,1) primary key, username varchar(100), t1 nvarchar(500),t2 nvarchar(500),t3 nvarchar(500), dt datetime) -- 2 init data Insert into T_Questionaire( username, t1, t2, t3, dt) values ('John' , '[{"code":"a", "desc":"Monday"}]', '[{"code":"a", "desc":"Jan."}]', '[{"code":"b", "desc":"2021"}]' ,getdate()) , ('Alice' , '[{"code":"b", "desc":"Tuesday"}]', '[{"code":"a", "desc":"Jan."}, {"code":"b", "desc":"Feb."}]', '[{"code":"a", "desc":"2020"},{"code":"b", "desc":"2021"}]' ,getdate())
資料內容：
創建轉換視圖：
Create or alter view V_VerticalQuestionaire as with pt as ( select a.username, a.T, a.answers, a.dt from dbo.T_Questionaire a unpivot ( answers for T in (t1,t2,t3 )) a) select pt.username, pt.dt, pt.T , aw.code, aw.[desc] from pt cross apply openjson(answers) WITH (code NVARCHAR(100) '$.code', [desc] NVARCHAR(100) '$.desc') aw
查詢結果如下：
總結下解決的思路：
1 先用unpivot將列行轉換， 使橫表記錄變成縱表記錄
2 使用openjson 將json資料轉換為集合資料， 然后使用cross apply 將集合展開
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/288706.html
標籤：SQL Server
下一篇：萊姆達表達試——查詢篇