Dirk Haest
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
...
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
...
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window