Replacement for Nested SELECT statements for every row in SELECT clause

Question

I've created main SELECT statement like below. For every row in Base table (base on IDKey I've added columns with values from result of INNER JOIN of few tables.

SELECT
  IDKey, 
  Name, 
  (select Amount from Table1 INNER JOIN Table2 ... where ID=IDKey) AS AmountTypeX, 
  (select Amount from Table1 INNER JOIN Table2 ... where ID=IDKey) AS AmountTypeY
FROM BaseTable

BaseTable:

IDKey Name 
----------
1     Ian
2     John
3     Ann
...

INNER JOIN of few tables Table1, Table2... :

ID Amount Type
--------------
1  100    X
1  200    Y
2  300    X
2  400    Y
3  500    X
3  600    Y
...

Result looks like:

IDKey  Name  AmountTypeX  AmountTypeY
-------------------------------------
1      Ian   100          200
2      John  300          400
3      Ann   500          600
...

Do you have any suggestion how to avoid this nested SELECT statements in the main query at the begining and made this query simpler and more effective ?

I wast thinking about PIVOTING of the INNER JOIN result to look like below (all data for particular ID in one row) and then JOIN with main query but Have no idea how to do this and is it possible.

ID  X   Y
-----------
1   100 200
2   300 400
3   500 600
...

Answer 1

It's the classic EAV pattern, which is a strong indicator of a design issue. I hope you understand that this design is against the core rules of relational databases. I have a blog post on this subject if you want to dig deeper.

However, if the design can't be changed, you can use a PIVOT query to extract your data:

DECLARE @BaseTable TABLE (
    IDKey int PRIMARY KEY,
    Name varchar(50)
)

INSERT INTO @BaseTable
VALUES
(1, 'Ian'),
(2, 'John'),
(3, 'Ann');


DECLARE @Table1 TABLE (
    ID int,
    Amount int,
    Type char(1),
    PRIMARY KEY (ID, Type)
)

INSERT INTO @Table1
VALUES 
(1, 100, 'X'),
(1, 200, 'Y'),
(2, 300, 'X'),
(2, 400, 'Y'),
(3, 500, 'X'),
(3, 600, 'Y');


SELECT *
FROM @BaseTable AS B
INNER JOIN (
    SELECT ID, X AS AmountX, Y AS AmountY
    FROM @Table1 AS T
    PIVOT (MIN(Amount) FOR Type IN ([X],[Y])) AS P
) AS T1
    ON B.IDKey = T1.ID;