Using PIVOT with 2 related tables

Question

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

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

Code:

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)

Then i try to use:

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')
      )

but an error appears "Incorrect syntax near the keyword "PIVOT"."

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).

Solutions for dynamic pivoting are very different than pivoting over a fixed list of values. Which solution are you looking for? — GMB, Jan 09 '20 at 16:22
hmm .. dynamically, but I would also like to understand what is wrong in my code — heso, Jan 09 '20 at 16:28

score 0 · Accepted Answer · answered Jan 09 '20 at 16:40

It's easy to handle with 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

Using PIVOT with 2 related tables

1 Answers1

Linked