使用動態列選擇

我有一個 InvoiceHeader、InvoiceItem 表

發票頭內容：

InvoiceId   InvoiceNumber   BusinessId  BusinessName    BranchId
       1               1           10       BSN INC    1
       2               2           10       BSN INC    2
       3               3           23    SAMPLE INC    1
       4               4           45   ANOTHER INC    1
       5               5           45   ANOTHER INC    3

發票項目內容：

InvoiceId   ItemId  ItemCode    Name            Quantity
       1        1    SNACK1    GOOD SNACKS     150
       1        2    SNACK2    SNACKERS        120
       2        1    SNACK1    GOOD SNACKS     100
       2        2    SNACK2    SNACKERS        150
       3        1    SNACK1    GOOD SNACKS     150
       3        2    SNACK2    SNACKERS        150
       3        3    SNACK3    BIG SNACKS      110
       4        1    SNACK1    GOOD SNACKS     90
       4        2    SNACK2    SNACKERS        80
       4        3    SNACK3    BIG SNACKS      120
       5        1    SNACK1    GOOD SNACKS     50
       5        2    SNACK2    SNACKERS        70
       5        3    SNACK3    BIG SNACKS      90
       5        4    SNACK4    TASTY SNACKS    70

這是 sqlfiddle：http ://sqlfiddle.com/#!18/aa5fa/6

現在我想要的結果是這樣的：

InvoiceNumber   BusinessName    BusinnessBranch SNACK1  SNACK2  SNACK3  SNACK4  TotalItems
   1           BSN INC         Branch1         150     120     0       0       270
   2           BSN INC         Branch2         100     150     0       0       250
   3           SAMPLE INC      Branch1         150     150     110     0       410
   4           ANOTHER INC     Branch1         90      80      120     0       290
   5           ANOTHER INC     Branch3         50      70      90      70      280
                                                                               1500 

如您所見，發票中的項目程式碼被添加為列來計算項目，最後一列是該發票的項目總數，最後一行顯示所有選定發票的項目總數。

轉置列的數量可以更多，甚至可以達到10多個。

這個查詢會給你你需要的東西，或者至少讓你接近。

;WITH CTE_Items AS
 (
   SELECT InvoiceId
       , COALESCE(SNACK1, 0) AS SNACK1
       , COALESCE(SNACK2, 0) AS SNACK2
       , COALESCE(SNACK3, 0) AS SNACK3
       , COALESCE(SNACK4, 0) AS SNACK4
   FROM (SELECT InvoiceId, ItemCode, Quantity FROM dbo.InvoiceItem AS II) AS I
     PIVOT (SUM(Quantity) FOR ItemCode IN ([SNACK1], [SNACK2], [SNACK3], [SNACK4])) AS P
   )
SELECT IH.InvoiceId
   , IH.BusinessName
   , 'Branch' + CONVERT(VARCHAR(10), IH.BranchID) AS BusinessBranch
   , I.SNACK1
   , I.SNACK2
   , I.SNACK3
   , I.SNACK4
   , TotalSnacks = I.SNACK1 + I.SNACK2 + I.SNACK3 + I.SNACK4
FROM dbo.InvoiceHeader AS IH
   LEFT OUTER JOIN CTE_Items AS I ON I.InvoiceID = IH.InvoiceID

WITH abc AS (
SELECT
  i.InvoiceId
  ,h.BusinessName
  ,h.BranchId
  ,ItemCode
  ,Quantity
FROM InvoiceItem i
INNER JOIN InvoiceHeader h ON i.InvoiceId = h.InvoiceId
)
SELECT PT.InvoiceId, PT.BusinessName, PT.BranchId,
   ISNULL(PT.SNACK1,0) as Snack1,
   ISNULL(PT.SNACK2,0) as Snack2,
   ISNULL(PT.SNACK3,0) as Snack3,
   ISNULL(PT.SNACK4,0) as Snack4
FROM abc
PIVOT( SUM(Quantity)
      FOR ItemCode in ("SNACK1","SNACK2","SNACK3","SNACK4")
      ) as PT

輸出：

SQLfiddle

| InvoiceId | BusinessName | BranchId | Snack1 | Snack2 | Snack3 | Snack4 |
|-----------|--------------|----------|--------|--------|--------|--------|
|         1 |      BSN INC |        1 |    150 |    120 |      0 |      0 |
|         2 |      BSN INC |        2 |    100 |    150 |      0 |      0 |
|         3 |   SAMPLE INC |        1 |    150 |    150 |    110 |      0 |
|         4 |  ANOTHER INC |        1 |     90 |     80 |    120 |      0 |
|         5 |  ANOTHER INC |        3 |     50 |     70 |     90 |     70 |

引用自：https://dba.stackexchange.com/questions/241692