Joseph A-Sa-BLue

同一個Row 有3組 column 要轉成 Header & Detail 如下 

1 . UNPIVOT 轉

   select ZA001,ZA002,ZA002S,meal_a
from 
(
select ZA001,ZA002,ZA003,ZA004,ZA005,ZA006,ZA007,ZA008,ZA009,ZA010,ZA011,ZA012,ZA013,ZA014,ZA015,ZA016,ZA017,ZA018,ZA019,ZA020,ZA021,ZA022,ZA023
,ZA024,ZA025,ZA026,ZA027,ZA028,ZA029,ZA030,ZA031,ZA032,ZA033  
from PALZA where ZA002=substring(convert(nvarchar(8),getdate(),112),1,6))P
UNPIVOT
 (meal_a for ZA002S 
 in
 (ZA003,ZA004,ZA005,ZA006,ZA007,ZA008,ZA009,ZA010,ZA011,ZA012,ZA013,ZA014,ZA015,ZA016,ZA017,ZA018,ZA019,ZA020,ZA021,ZA022,ZA023
 ,ZA024,ZA025,ZA026,ZA027,ZA028,ZA029,ZA030,ZA031,ZA032,ZA033)) as unpvt

執行結果

再調整一下把ZA002S 轉換成日期  select ZA001,ZA002,cast(substring(ZA002S,3,3) as int)-2 as 'day_no',meal_a

這樣第一組就轉出來了

2.重複 ZA065-ZA095  ZA096-ZA126

  在join 起來  就可以

3 .再利用 distinct 將轉好的資料取出Header

就可以開始來塞資料了