軟體工程師的小事：到底要不要 Close

0

UNPIVOT 情境：非正規化的商品庫存資料表設計

情境說明

此為進銷存系統既有資料庫設計，尺寸與庫存的對應關係為尺寸對應的欄位索引庫存量。

範例 T-SQL 語法
GitHub - 在 T-SQL 使用 unpivot 進行資料表轉置範例

資料表說明
本範例資料庫提供既有系統的部分：商品尺寸、庫存資訊 ... etc。

商品資料表 [dbo].[Products]

尺寸資料表 [dbo].[Sizes]

商品庫存資料表 [dbo].[ProductStorages]

此資料表設計的問題

每新增一個商品會建立一列最少十個欄位的庫存資料列。當尺寸單位只有一個時，僅會使用一個欄位儲存庫存資訊。

當尺寸索引不夠使用時，可能要修改資料表內容。

商品、尺寸、庫存在各自資料表中並沒有實際關聯。

轉置資料表支援正規化設計

透過 UNPIVOT 指令將以資料行基礎的尺寸與庫存資料表修改成以資料列為基礎。

原始設計中資料表欄位數是固定的，FOR 指令碼區塊指定的資料行數就可以寫死。

尺寸資料表透過 UNPIVOT 進行轉置指令碼

--
--使用 UNPIVOT 語法做尺寸對應表轉置
--
SELECT unpivoted.[No]
 ,unpivoted.[Code]
 ,CONVERT(TINYINT,RIGHT(unpivoted.[ColumnName],2))-1 [Index]
 ,unpivoted.[Name]
FROM
(
 SELECT a.[No]
  ,a.[Code]
  ,a.[Size01]
  ,a.[Size02]
  ,a.[Size03]
  ,a.[Size04]
  ,a.[Size05]
  ,a.[Size06]
  ,a.[Size07]
  ,a.[Size08]
  ,a.[Size09]
  ,a.[Size10]
 FROM [dbo].[Sizes] a with (nolock)
) a
UNPIVOT
(
 [Name] For [ColumnName] IN (
  [Size01]
  ,[Size02]
  ,[Size03]
  ,[Size04]
  ,[Size05]
  ,[Size06]
  ,[Size07]
  ,[Size08]
  ,[Size09]
  ,[Size10]
 )
) AS unpivoted

轉置後的尺寸資料表

庫存資料表透過 UNPIVOT 進行轉置指令碼

--
--使用 UNPIVOT 語法做庫存轉置
--
SELECT unpivoted.[ProductCode]
 ,unpivoted.[ProductSizeCode]
 ,CONVERT(TINYINT,RIGHT(unpivoted.[ColumnName],2))-1 [Index]
 ,unpivoted.[Storage]
FROM
(
 SELECT b.[Code] [ProductCode]
  ,c.[Code] [ProductSizeCode]
  ,a.[Storage01]
  ,a.[Storage02]
  ,a.[Storage03]
  ,a.[Storage04]
  ,a.[Storage05]
  ,a.[Storage06]
  ,a.[Storage07]
  ,a.[Storage08]
  ,a.[Storage09]
  ,a.[Storage10]
 FROM [dbo].[ProductStorages] a with (nolock)
  INNER JOIN [dbo].[Products] b with (nolock) ON a.[ProductNo] = b.[No]
  INNER JOIN [dbo].[Sizes] c with (nolock) ON a.[SizeNo] = c.[No]
) a
UNPIVOT
(
 [Storage] For [ColumnName] IN (
  [Storage01]
  ,[Storage02]
  ,[Storage03]
  ,[Storage04]
  ,[Storage05]
  ,[Storage06]
  ,[Storage07]
  ,[Storage08]
  ,[Storage09]
  ,[Storage10]
 )
) AS unpivoted

轉置後的庫存資料表

透過 UNPIVOT 將 Size01, Size02 與 Storage01, Storage02 的索引調整成資料表欄位，就可進行 Foreign Key 外部參考設定。

此實際案例中，有依照轉置後的格式建立新的資料表進行儲存。
參考資料

留言

張貼留言