SQL枢轴
我有一个表SQL枢轴
CREATE TABLE [dbo].[newTable](
[EBELN] [nvarchar](20) NOT NULL,
[EBELP] [nvarchar](10) NOT NULL,
[VGABE] [nvarchar](2) NOT NULL,
[MENGE] [numeric](15, 3) NULL,
[DMBTR] [numeric](15, 2) NULL
)
它有这些记录
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000004', '0001', '1', 1 , 27.95)
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000004', '0001', '2', 1 , 27.95)
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000004', '0002', '1', 1 , 10.95)
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000004', '0002', '2', 1 , 10.95)
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000010', '0001', '1', 1 , 22.95)
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000010', '0001', '2', 1 , 22.95)
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000010', '0002', '1', 1 , 32.95)
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000010', '0002', '2', 1 , 32.95)
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('4151516119', '0001', '1', 1 , 400.00)
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('4151516119', '0001', '1', 1 , 400.00)
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('4151516119', '0001', '2', 1 , 400.00)
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('4151516119', '0002', '1', 1 , 200.00)
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('4151516119', '0002', '2', 1 , 200.00)
Go
多数民众赞成在SELECT *
EBELN EBELP VGABE MENGE DMBTR
-------------------- ---------- ----- -------------- ---------------------------------------
3000000004 0001 1 1.000 27.95
3000000004 0001 2 1.000 27.95
3000000004 0002 1 1.000 10.95
3000000004 0002 2 1.000 10.95
3000000010 0001 1 1.000 22.95
3000000010 0001 2 1.000 22.95
3000000010 0002 1 1.000 32.95
3000000010 0002 2 1.000 32.95
4151516119 0001 1 1.000 400.00
4151516119 0001 1 1.000 400.00
4151516119 0001 2 1.000 400.00
4151516119 0002 1 1.000 200.00
4151516119 0002 2 1.000 200.00
3000000004 0001 2 1.000 27.95
3000000004 0002 1 1.000 10.95
3000000004 0002 2 1.000 10.95
3000000010 0001 1 1.000 22.95
3000000010 0001 2 1.000 22.95
3000000010 0002 1 1.000 32.95
3000000010 0002 2 1.000 32.95
4151516119 0001 1 1.000 400.00
4151516119 0001 1 1.000 400.00
4151516119 0001 2 1.000 400.00
4151516119 0002 1 1.000 200.00
4151516119 0002 2 1.000 200.00
4151516177 0002 6 1.000 111.00
4151516177 0002 8 1.000 111.00
我需要什么而想要的是产生这个结果的动态支点
+------------+-------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| EBELN | EBELP | c_DMBTR_1 | c_MENGE_1 | c_DMBTR_2 | c_MENGE_2 | c_DMBTR_6 | c_MENGE_6 | c_DMBTR_8 | c_MENGE_8 |
+------------+-------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 3000000004 | 0001 | 27.95 | 1 | 27.95 | 1 | NULL | NULL | NULL | NULL |
| 3000000004 | 0002 | 10.95 | 1 | 10.95 | 1 | NULL | NULL | NULL | NULL |
| [...] | | | | | | | | | |
| 4151516119 | 0001 | 800.00 | 1 | 400.00 | 1 | NULL | NULL | NULL | NULL |
| 4151516177 | 0002 | NULL | NULL | NULL | NULL | 111.00 | 1 | 111.00 | 1 |
+------------+-------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
我尝试了几个解决方案，但没有得到我想要的结果。
我需要的是VGABE的值，它们是(1,2,6,7,8,9,P,R)，要连接到字符串名称c_DMBTR和c_MENGE。但是有可能例如P或者7没有被使用，所以我不希望有这个列。我认为使其动态是唯一可行的方法。
从VGABE的价值应该被添加到列名状'c_MENGE_'+VGABE并且它必须是为了，这意味着它开始从VGABE所有栏的最低值，然后下一个值，直到从vgabe每次使用的值。
如果有一个位置（EBELP）从VGABE不止一个值样两次1 POS 0001，你可以通过EBELN 4151516119我来总结（DMBTR）这个职位和VGABE价值看。我也和MENGE一样。
是否可以在一个查询或存储过程中执行操作。我不知道如何得到这个结果，我现在被卡住了。还是有另一种我不知道的方式？
为了得到您想要的最终结果，您首先必须unpivotDMBTR和MENGE列，然后应用PIVOT函数将行转换为列。
你没有提到你正在使用哪个版本的SQL Server go我会猜测SQL Server 2005+。从SQL Server 2005开始，PIVOT函数已经可用，但对于UNPIVOT，您也可以使用CROSS APPLY--我认为这会更容易一些。
在深入研究动态SQL版本之前，我总是希望编写一个静态版本来获取正确的逻辑，然后将其转换为动态SQL。第一步是将DMBTR和MENGE列转换为行：
select
t.ebeln,
t.ebelp,
new_col = c.orig_col + '_' + vgabe,
c.value
from dbo.newTable t
cross apply
(
select 'c_MENGE', menge union all
select 'c_DMBTR', dmbtr
) c (orig_col, value);
见SQL Fiddle with Demo。这是你的数据转换成：
| EBELN | EBELP | NEW_COL | VALUE |
|------------|-------|-----------|-------|
| 3000000004 | 0001 | c_MENGE_1 | 1 |
| 3000000004 | 0001 | c_DMBTR_1 | 27.95 |
| 3000000004 | 0001 | c_MENGE_2 | 1 |
| 3000000004 | 0001 | c_DMBTR_2 | 27.95 |
| 3000000004 | 0002 | c_MENGE_1 | 1 |
| 3000000004 | 0002 | c_DMBTR_1 | 10.95 |
正如你可以看到你现在有多行，用的c_MENGE_1连接值等，这将是你最后列的new_col一起。
一旦你有了这个结果，您可以将旋转功能：
select ebeln,
ebelp,
c_DMBTR_1, c_MENGE_1, c_DMBTR_2, c_MENGE_2,
c_DMBTR_6, c_MENGE_6, c_DMBTR_8, c_MENGE_8
from
(
select
t.ebeln,
t.ebelp,
new_col = c.orig_col + '_' + vgabe,
c.value
from dbo.newTable t
cross apply
(
select 'c_MENGE', menge union all
select 'c_DMBTR', dmbtr
) c (orig_col, value)
) d
pivot
(
sum(value)
for new_col in (c_DMBTR_1, c_MENGE_1, c_DMBTR_2, c_MENGE_2,
c_DMBTR_6, c_MENGE_6, c_DMBTR_8, c_MENGE_8)
) piv
order by ebeln, ebelp;
现在您已经掌握了正确的逻辑，您需要将其转换为动态SQL。这将从创建新列名称的sql字符串开始。对于这一点，您将使用FOR XML PATH：
select @cols = STUFF((SELECT ',' + QUOTENAME(col + '_' + vgabe)
from dbo.NewTable t
cross apply
(
select 'c_DMBTR', 1 union all
select 'c_MENGE', 2
) c (col, so)
group by col, so, vgabe
order by vgabe, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
这类似于我在静态版本中使用的代码。它获取vgabe值的列表，并将其连接到您想要PIVOT的两列的名称（DMBTR,MENGE）。我还为这些栏目提供了排序顺序，因此您可以根据需要订购它们。完整的动态SQL代码将为：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col + '_' + vgabe)
from dbo.NewTable t
cross apply
(
select 'c_DMBTR', 1 union all
select 'c_MENGE', 2
) c (col, so)
group by col, so, vgabe
order by vgabe, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'SELECT ebeln, ebelp,' + @cols + N'
from
(
select
t.ebeln,
t.ebelp,
new_col = c.orig_col + ''_'' + vgabe,
c.value
from dbo.newTable t
cross apply
(
select ''c_MENGE'', menge union all
select ''c_DMBTR'', dmbtr
) c (orig_col, value)
) x
pivot
(
sum(value)
for new_col in (' + @cols + N')
) p
order by ebeln, ebelp'
exec sp_executesql @query;
请参阅SQL Fiddle with Demo。这给出了一个结果：
| EBELN | EBELP | C_DMBTR_1 | C_MENGE_1 | C_DMBTR_2 | C_MENGE_2 | C_DMBTR_6 | C_MENGE_6 | C_DMBTR_8 | C_MENGE_8 |
|------------|-------|-----------|-----------|-----------|-----------|-----------|-----------|-----------|-----------|
| 3000000004 | 0001 | 27.95 | 1 | 27.95 | 1 | (null) | (null) | (null) | (null) |
| 3000000004 | 0002 | 10.95 | 1 | 10.95 | 1 | (null) | (null) | (null) | (null) |
| 3000000010 | 0001 | 22.95 | 1 | 22.95 | 1 | (null) | (null) | (null) | (null) |
| 3000000010 | 0002 | 32.95 | 1 | 32.95 | 1 | (null) | (null) | (null) | (null) |
| 4151516119 | 0001 | 800 | 2 | 400 | 1 | (null) | (null) | (null) | (null) |
| 4151516119 | 0002 | 200 | 1 | 200 | 1 | 111 | 1 | 111 | 1 |
嘿！非常感谢您的快速和详细的回复！特别是为了以静态和动态的方式显示逻辑！很神奇。我使用SQL Server 2014和Management Studio 2014.我可以问你另一个问题吗？ – JollyPopper 2014-10-01 12:08:39
当然，问题是什么？ – Taryn 2014-10-01 12:24:40
1）我需要在dbo.newTableHeader中插入一个LEFT JOIN作为我的EBELN，EBELP主表以及dbo.newTable中的PK。 2）是否可以扩展查询。我想添加一个名为c_COUNT_X的列，其中X代表每个VGABE值。它计算每个EBELP的VGABE。 也许我可以做一个CTE来为我的需要准备桌子并将其用于加入 – JollyPopper 2014-10-01 13:06:40
即时通讯使用SQL Server 2014和MS SQL管理工作室2014年 – JollyPopper 2014-10-01 12:09:40