将PIVOT与2个相关表一起使用 [英] Using PIVOT with 2 related tables

问题描述

我正在使用两个表在SQL Server(SSMS)中工作:

I am working in SQL Server (SSMS) with two tables like:

prop_ppl:

id_numb  id_pers       cdate       val
1         4            NULL      NULL
2         2        2018-12-12    250
3         1        2018-12-01    250
4         3        2018-12-11    500
5         6        2018-01-01    500
6         5        2018-12-12    480

ppl:

id_perc   name
1         John
2         Derek
3          Mia
4         Chris
5          Ann
6         Dave

然后我需要像这样获取表:

Then i need to get the table like this:

           name
id_numb   value

对于这些表，应在必要时查找日期为2018/12/12的ppl的所有值:

for these tables it should be, when its nececcary to find all values for ppl with date 2018/12/12:

     Derek    Ann
2     250      0
6      0     NULL

代码:

CREATE TABLE ppl(
    id_perc smallint PRIMARY KEY,
    name varchar(50) NOT NULL
)

CREATE TABLE prop_ppl(
    id_numb int IDENTITY(1,1) PRIMARY KEY,
    id_perc smallint NOT NULL,
    cdate smalldatetime,
    val int
    )

INSERT INTO dbo.ppl (id_perc, name)
VALUES (1, 'John'), (2, 'Derek'), (3, 'Mia'), (4, 'Chris'), (5, 'Ann'), 
        (6, 'Dave')

INSERT INTO dbo.prop_ppl (id_perc, cdate, val)
VALUES (4, NULL,NULL), (2,'20181212', 250), (1, '20181201', 250),
    (3, '20181211',500), (6,'20180101', 500), (5, '20181212', 480)

然后我尝试使用:

SELECT *
FROM (  
    SELECT name,id_numb,val     
    FROM prop_ppl 
    JOIN ppl ON prop_ppl.id_perc = ppl.id_perc
    WHERE cdate='20181212'
    ) 

PIVOT(
    SUM(val) 
    FOR [name] in ('Derek','Ann')
      )

但出现错误 "关键字"PIVOT"附近的语法不正确."

也许没有PIVOT也可以做. 另外，我想了解如何将此脚本应用于任意参数(而不仅限于Derek& Ann).

maybe it's possible to do without PIVOT.. Also, I would like to understand how this script could be applied to arbitrary parameters (and not just to Derek & Ann).

推荐答案

使用CTE易于处理:

With CTE as (
    SELECT
        name,
        id_numb,
        val   
    FROM prop_ppl 
    JOIN ppl ON prop_ppl.id_perc = ppl.id_perc
    WHERE cdate='20181212')
Select
    *
from CTE
PIVOT(SUM(val) FOR [name] in ([Derek],[Ann])) as  Col

这篇关于将PIVOT与2个相关表一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！