使用数据透视表选择其他表行 [英] Select other table rows with pivot table

问题描述

我有桌子销售详情



salesid invno金额

1 345 5000

2 678 500

3 111 1000

4 222 2000

3 333 3000



和TaxDetails表



tdid salesid taxid taxname amount

2 1 1 CST 100

3 1 2 VAT 200

4 1 3 ST 300

5 2 1增值税400

6 2 2 ST 500

7 3 1 CST 600

8 3 2增值税700

9 3 3 ST 800



使用此查询

  DECLARE   @cols   AS   NVARCHAR （MAX），
  @查询  AS   NVARCHAR （MAX）
 
  select   @cols  = STUFF（（ SELECT  ' ，' + QUOTENAME（taxname）
 来自 TaxDetails 其中 salesid = 1 
  group  按 taxname，taxid 
 订单 按 taxid 
  FOR  XML PATH（' '）， TYPE  
）.value（' 。'，'  NVARCHAR（MAX ）'）
， 1 ， 1 ，' '）
 
  set   @ query  = N '  SELECT' +  @cols  + N ' 来自
（
选择金额，税号
来自TaxDetails，其中salesid = 1 
）x 
 pivot 
（
 max（金额）
 for taxname in（' +  @cols  + N ' ）
）p' 
 
  exec  sp_executesql  @ query ; 

我得到了结果喜欢这个



CST增值税ST

100 200 300





但我想要这种格式的数据



salesid invno金额CST增值税ST

1 345 5000 100 200 300

2 678 500 400 500

3 111 1000 600 700 800

4 222 2000 0 0 0

3 333 3000 0 0 0





请帮帮我





提前感谢...



我的尝试：



DECLARE @cols AS NVARCHAR（MAX），

@query AS NVARCHAR（MAX）



select @cols = STUFF（（SELECT'，'+ QUOTENAME（taxname）

来自TaxDetails，其中salesid = 1

按税名，出租车分组

按出租车顺序

FOR XML PATH（''），TYPE

）.value（'。'，'NVARCHAR（MAX）'）

，1,1，''）



set @query = N'SELECT'+ @cols + N'来自

（

选择金额，税名
来自TaxDetails的
，其中salesid = 1
）x

pivot

（

max（金额）

for taxname in（' + @cols + N'）

）p'



exec sp_executesql @query;

解决方案

您需要加入数据透视表的源部分中的两个表 - 上面的子查询 x 。



您还需要删除限制 salesid = 1 WHERE 子句>



因此你还需要在列列表的生成中使用 DISTINCT ，这意味着你还需要改变该部分的 ORDER BY - 我使用了快捷方式 ORDER BY 1 而不是 ORDER BY'，'+ QUOTENAME（taxname）



此查询非常接近您的预期结果，因为给出的样本数据不准确与您的预期结果不符：

  DECLARE   @ cols   AS   NVARCHAR （MAX），
  @ query   AS   NVARCHAR （MAX）
 
 选择  @cols  = STUFF（（ SELECT   DISTINCT  ' ，' + QUOTENAME（taxname） ）
 来自 T. axDetails 
  group  按 taxname，taxid 
 订单 按  1  
  FOR  XML PATH（' '）， TYPE  
）.value（' 。'，'  NVARCHAR（MAX）'）
， 1 ， 1 ，' '）
 
  set   @ query  = N  '  SELECT salesid，invno，' +  @cols  + N ' 来自
（
选择T.salesid，invno，T.amount，来自SalesDetails的taxname 
 S 
 LEFT OUTER JOIN TaxDetails T ON T.salesid = S.salesid 
）x 
 pivot 
（
 max（amount）
中的taxname（' +  @ cols  + N ' ）
）p ORDER BY Salesid' 
 
  exec  sp_executesql  @查询; 

I Have Table SalesDetails like

salesid invno amount
1 345 5000
2 678 500
3 111 1000
4 222 2000
3 333 3000

and TaxDetails Table

tdid salesid taxid taxname amount
2 1 1 CST 100
3 1 2 VAT 200
4 1 3 ST 300
5 2 1 VAT 400
6 2 2 ST 500
7 3 1 CST 600
8 3 2 VAT 700
9 3 3 ST 800

by using this query

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(taxname) 
                    from TaxDetails where salesid=1
                    group by taxname, taxid
                    order by taxid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select amount, taxname
                from TaxDetails where salesid=1
            ) x
            pivot 
            (
                max(amount)
                for taxname in (' + @cols + N')
            ) p '

exec sp_executesql @query;

I got result like this

CST VAT ST
100 200 300


But i want data like this format

salesid invno amount CST VAT ST
1 345 5000 100 200 300
2 678 500 400 500
3 111 1000 600 700 800
4 222 2000 0 0 0
3 333 3000 0 0 0


Please help me


thanks in advance...

What I have tried:

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(taxname)
from TaxDetails where salesid=1
group by taxname, taxid
order by taxid
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = N'SELECT ' + @cols + N' from
(
select amount, taxname
from TaxDetails where salesid=1
) x
pivot
(
max(amount)
for taxname in (' + @cols + N')
) p '

exec sp_executesql @query;

解决方案

You need to join the two tables in the source section of your pivot - your sub-query x above.

You also need to remove all of the WHERE clauses where you are limiting the salesid = 1

Because of that you also need to use DISTINCT on the generation of the column list, which means you also need to change the ORDER BY on that section - I've used the shortcut ORDER BY 1 instead of ORDER BY ',' + QUOTENAME(taxname)

This query gets pretty close to your expected results, it's not exact because the sample data given doesn't match your expected results:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
 
select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(taxname) 
                    from TaxDetails 
                    group by taxname, taxid
                    order by 1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
 
set @query = N'SELECT salesid, invno, ' + @cols + N' from 
             (
                select T.salesid, invno, T.amount, taxname
				from  SalesDetails S
				LEFT OUTER JOIN  TaxDetails T ON T.salesid = S.salesid
            ) x
            pivot 
            (
                max(amount)
                for taxname in (' + @cols + N')
            ) p ORDER BY Salesid'
 
exec sp_executesql @query;

这篇关于使用数据透视表选择其他表行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！