SQL Pivot插入 [英] SQL Pivot insertion
本文介绍了SQL Pivot插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一张桌子，叫它tblINVOICE.发票可以容纳一个或多个项目，并且发票上的每个项目都会创建一行.
I have a table, lets call it tblINVOICE. The invoice can hold one or more item and for each item on the invoice, a line is created.
+-----------------------------------------+
| InvNo | ItemNo | ItemPrice | VatAmount |
+-----------------------------------------+
| 001 | A001 | 100.00 | 10.00 |
| 001 | B020 | 233.33 | 23.00 |
| 001 | D111 | 20.99 | 2.00 |
| 002 | B020 | 233.33 | 23.00 |
| 002 | X901 | 108.00 | 10.80 |
+-----------------------------------------+
现在，我想将这些数据插入到另一个表中，其中每行代表一张发票，如下所示；
Now I want to insert these data into another table, whereby each line represent an invoice, as below;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InvNo | Item1 | Item2 | Item3 | Item4 | Item5 | ItemPrice1 | ItemPrice2 | ItemPrice3 | ItemPrice4 | ItemPrice5 | VatAmount1 | VatAmount2 | VatAmount3 | VatAmount4 | VatAmount5 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 001 | A001 | B020 | D111 | Null | Null | 100.00 | 233.00 | 20.99 | Null | Null | 10.00 | 23.00 | 2.00 | 23.00 | 10.80 |
| 002 | B020 | X901 | Null | Null | Null | 233.33 | 23.00 | Null | Null | Null | 23.00 | 10.80 | Null | Null | Null |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
我希望所有行都被透视成列. 我该如何进行?有没有简单的方法可以做到这一点? 还是我应该遍历第一个表并插入第二个表?
I want all the rows to be pivoted into columns. How do I proceed? Is there a simple way to do this? Or should I loop through the 1st table and insert into the second?
谢谢
推荐答案
- 通常，当您总是需要五列时:
CREATE TABLE #inv(id INT IDENTITY(1,1) PRIMARY KEY,InvNo VARCHAR(16),ItemNo VARCHAR(16), ItemPrice DECIMAL(28,2), VatAmount DECIMAL(28,2));
INSERT INTO #inv(InvNo,ItemNo,ItemPrice,VatAmount)VALUES
('001','A001',100.00,10.00),
('001','B020',233.33,23.00),
('001','D111',20.99,2.00),
('002','B020',233.33,23.00),
('002','X901',108.00,10.80);
SELECT
InvNo,
Item1=MAX(CASE WHEN ItemId=1 THEN ItemNo END),
Item2=MAX(CASE WHEN ItemId=2 THEN ItemNo END),
Item3=MAX(CASE WHEN ItemId=3 THEN ItemNo END),
Item4=MAX(CASE WHEN ItemId=4 THEN ItemNo END),
Item5=MAX(CASE WHEN ItemId=5 THEN ItemNo END),
ItemPrice1=MAX(CASE WHEN ItemId=1 THEN ItemPrice END),
ItemPrice2=MAX(CASE WHEN ItemId=2 THEN ItemPrice END),
ItemPrice3=MAX(CASE WHEN ItemId=3 THEN ItemPrice END),
ItemPrice4=MAX(CASE WHEN ItemId=4 THEN ItemPrice END),
ItemPrice5=MAX(CASE WHEN ItemId=5 THEN ItemPrice END),
VatAmount1=MAX(CASE WHEN ItemId=1 THEN VatAmount END),
VatAmount2=MAX(CASE WHEN ItemId=2 THEN VatAmount END),
VatAmount3=MAX(CASE WHEN ItemId=3 THEN VatAmount END),
VatAmount4=MAX(CASE WHEN ItemId=4 THEN VatAmount END),
VatAmount5=MAX(CASE WHEN ItemId=5 THEN VatAmount END)
FROM
(
SELECT
*,
ItemId=ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id)
FROM
#inv
) AS inv_nr
GROUP BY
InvNo;
DROP TABLE #inv;
结果:
+-------+-------+-------+-------+-------+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| InvNo | Item1 | Item2 | Item3 | Item4 | Item5 | ItemPrice1 | ItemPrice2 | ItemPrice3 | ItemPrice4 | ItemPrice5 | VatAmount1 | VatAmount2 | VatAmount3 | VatAmount4 | VatAmount5 |
+-------+-------+-------+-------+-------+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| 001 | A001 | B020 | D111 | NULL | NULL | 100.00 | 233.33 | 20.99 | NULL | NULL | 10.00 | 23.00 | 2.00 | NULL | NULL |
| 002 | B020 | X901 | NULL | NULL | NULL | 233.33 | 108.00 | NULL | NULL | NULL | 23.00 | 10.80 | NULL | NULL | NULL |
+-------+-------+-------+-------+-------+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
- 动态地，当您想要等于任何
InvNo的最大行数的列数时
- Dynamically, when you want an amount of columns equal to the maximum number of rows for any
InvNo
CREATE TABLE #inv(id INT IDENTITY(1,1) PRIMARY KEY,InvNo VARCHAR(16),ItemNo VARCHAR(16), ItemPrice DECIMAL(28,2), VatAmount DECIMAL(28,2));
INSERT INTO #inv(InvNo,ItemNo,ItemPrice,VatAmount)VALUES
('001','A001',100.00,10.00),
('001','B020',233.33,23.00),
('001','D111',20.99,2.00),
('002','B020',233.33,23.00),
('002','X901',108.00,10.80);
DECLARE @item_cols NVARCHAR(MAX)=STUFF((
SELECT DISTINCT
',Item'+CAST(ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id) AS VARCHAR(16))+'=MAX(CASE WHEN row_id='+CAST(ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id) AS VARCHAR(16))+' THEN ItemNo END)'
FROM
#inv
FOR
XML PATH('')
),1,1,''
);
DECLARE @price_cols NVARCHAR(MAX)=STUFF((
SELECT DISTINCT
',ItemPrice'+CAST(ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id) AS VARCHAR(16))+'=MAX(CASE WHEN row_id='+CAST(ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id) AS VARCHAR(16))+' THEN ItemPrice END)'
FROM
#inv
FOR
XML PATH('')
),1,1,''
);
DECLARE @vat_cols NVARCHAR(MAX)=STUFF((
SELECT DISTINCT
',VatAmount'+CAST(ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id) AS VARCHAR(16))+'=MAX(CASE WHEN row_id='+CAST(ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id) AS VARCHAR(16))+' THEN VatAmount END)'
FROM
#inv
FOR
XML PATH('')
),1,1,''
);
DECLARE @stmt NVARCHAR(MAX)=N'
SELECT
InvNo,' + @item_cols + ',' + @price_cols + ',' + @vat_cols + '
FROM
(
SELECT
*,
row_id=ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id)
FROM
#inv
) AS inv_nr
GROUP BY
InvNo;
';
EXECUTE sp_executesql @stmt;
DROP TABLE #inv;
结果:
+-------+-------+-------+-------+------------+------------+------------+------------+------------+------------+
| InvNo | Item1 | Item2 | Item3 | ItemPrice1 | ItemPrice2 | ItemPrice3 | VatAmount1 | VatAmount2 | VatAmount3 |
+-------+-------+-------+-------+------------+------------+------------+------------+------------+------------+
| 001 | A001 | B020 | D111 | 100.00 | 233.33 | 20.99 | 10.00 | 23.00 | 2.00 |
| 002 | B020 | X901 | NULL | 233.33 | 108.00 | NULL | 23.00 | 10.80 | NULL |
+-------+-------+-------+-------+------------+------------+------------+------------+------------+------------+
这篇关于SQL Pivot插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
