Link to home
Start Free TrialLog in
Avatar of Dirk Haest
Dirk HaestFlag for Belgium

asked on

Pivot-table over multiple tables

Hello,

We would like to have an pivot-ouput, but it's coming from 3 tables

We have the following tablestructure (by design, we cannot change it)

UP (with 2 columns ID and Description)
1   FirstUp
2   SecondUp
...

SampleType (ID, Descr, ...)
10   SampleA
20   SampleB      
30   SampleC
...

SampleUp (ID_UP, ID_SAMPLE, TYPE)
1   10    W
2   10    W
1   20    W
2   30    W
....

We would like to have the following outcome with above example
                       FirstUp          SecondUp  ....
SampleA          1 (or yes)        1
SampleB          1                      0
SampleC          0                      1
SampleD          0                     0
...
Avatar of ste5an
ste5an
Flag of Germany image

It's a simple PIVOT:

DECLARE @UP TABLE
    (
        ID INT ,
        [Description] VARCHAR(255)
    );
INSERT INTO @UP ( ID ,
                  Description )
VALUES ( 1, 'FirstUp' ) ,
       ( 2, 'SecondUp' );

DECLARE @SampleType TABLE
    (
        ID INT ,
        Descr VARCHAR(255)
    );
INSERT INTO @SampleType ( ID ,
                          Descr )
VALUES ( 10, 'SampleA' ) ,
       ( 20, 'SampleB' ) ,
       ( 30, 'SampleC' );


DECLARE @SampleUp TABLE
    (
        ID_UP INT ,
        ID_SAMPLE INT ,
        [TYPE] CHAR(1)
    );
INSERT INTO @SampleUp ( ID_UP ,
                        ID_SAMPLE ,
                        TYPE )
VALUES ( 1, 10, 'W' ) ,
       ( 2, 10, 'W' ) ,
       ( 1, 20, 'W' ) ,
       ( 2, 30, 'W' );

WITH [Data]
AS ( SELECT U.ID ,
            U.Description ,
            ST.Descr
     FROM   @UP U
            INNER JOIN @SampleUp SU ON U.ID = SU.ID_UP
            INNER JOIN @SampleType ST ON SU.ID_SAMPLE = ST.ID )
SELECT *
FROM   [Data] D
    PIVOT (   COUNT(ID)
              FOR Description IN ( FirstUp, SecondUp )) P;

Open in new window

Avatar of Dirk Haest

ASKER

Hello,

Thanks for that answer.
I changed a little bit to get to my results

WITH [Data]
AS ( SELECT U.UP ,
            U.DESCRIPTION ,
            ST.DESCRIPTION as descr
     FROM   RndSuite.RndtUp U
            INNER JOIN RndSuite.RndtStAc SU ON U.UP = SU.UP
            INNER JOIN RndSuite.RndtSt ST ON SU.ST = ST.ST and SU.VERSION = ST.VERSION
                  WHERE ST.ST = 3 )
SELECT *
FROM   [Data] D
    PIVOT (   COUNT(DESCRIPTION)
              FOR UP IN ( [1], [4] )) P;


Still have one question. Now I probably have to add each column in the FOR UP IN ...

Is there a way to get here a "dynamic" list, namely all the values from another table, namely all the UP-id ' s .. (the @up U table)

So instead of
              FOR UP IN ( [1], [2], [3], [4], [5],... )) P;

FOR UP IN (select id's)
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial