[Reference and several price-lists][1] [1]: https://i.stack.imgur.com/iy5LE.jpg

Good day! There is a reference book of goods and several tables with prices for them. How can you achieve a compact output of the final result as shown in the picture? Simply using LEFT JOIN results in unnecessary records through cross-links. (Desirable for version MSSQL 2005). Thanks.

CodePudding user response：

A solution would be the following query.

SELECT a.ID,
CASE WHEN ProdName IS NULL THEN (SELECT ProdName FROM tblRef WHERE id=a.id) ELSE ProdName END ProdName,
CASE WHEN Price1 IS NULL THEN 0 ELSE Price1 END Price1,
CASE WHEN Price2 IS NULL THEN 0 ELSE Price2 END Price2,
CASE WHEN Price3 IS NULL THEN 0 ELSE Price3 END Price3
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) rn,ID,Price3 FROM tblPrc3) a
FULL OUTER JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) rn,ID,Price1 FROM tblPrc1) b ON (a.id=b.id AND a.rn=b.rn)
FULL OUTER JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) rn,ID,Price2 FROM tblPrc2) c ON (a.id=c.id AND a.rn=c.rn)
FULL OUTER JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) rn,ID,ProdName FROM tblRef) d ON (a.id=d.id AND a.rn=d.rn)

However, there are two flaws with this query

• In the FROM clause I kept tblPrc3 table because it had maximum number of records. I think this is going to be undesirable for a dynamic query.
• I am firing a sub query inside the SELECT statement that will hugely impact the performance in case the dataset increases.

Possible solutions

• The first flaw can be corrected by generating a dynamic query and keeping the table with maximum rows in FROM clause.
• The second flaw can be corrected by create a temporary resultset of selected ID and productName and using it as a sub query.

Data creation queries

CREATE TABLE tblRef(ID INT,ProdName VARCHAR(MAX));

INSERT INTO tblRef(ID,ProdName)
VALUES(1,'NAME1');

CREATE TABLE tblPrc1(ID INT,Price1 INT);

INSERT INTO tblPrc1(ID,Price1)
VALUES(1,100000),(1,110000);

CREATE TABLE tblPrc2(ID INT,Price2 INT);

INSERT INTO tblPrc2(ID,Price2)
VALUES(1,200000);

CREATE TABLE tblPrc3(ID INT,Price3 INT);

INSERT INTO tblPrc3(ID,Price3)
VALUES(1,300000),(1,310000),(1,320000);

CodePudding user response：

I assume you can not rely on tblPrc3 being the table with most prices. I tried to find the maximum number of prices dynamically for each id and build the query from there. I hope you get some additional help from this.

With ref (id,prodname) as
(select 1, 'Name1')
,prc1(id,price) as
(select 1,10000 union all
select 1,11000)
,prc2(id,price) as
(select 1,20000)
,prc3(id,price) as
(select 1,30000 union all
select 1,31000 union all
select 1,32000)
,p1(rn,id,price) as
(select  ROW_NUMBER() OVER(PARTITION BY ID ORDER BY price) rn,id,price
from prc1
)
,p2(rn,id,price) as
(select  ROW_NUMBER() OVER(PARTITION BY ID ORDER BY price) rn,id,price
from prc2
)
,p3(rn,id,price) as
(select  ROW_NUMBER() OVER(PARTITION BY ID ORDER BY price) rn,id,price
from prc3
)
,maxrowperid(id,cnt) as(
select a.id, max(a.cnt) from (
  select ref.id, count(*) cnt
  from ref
  join prc1 on prc1.id = ref.id
  group by ref.id
  union all
  select ref.id, count(*) 
  from ref
  join prc2 on prc2.id = ref.id
  group by ref.id
  union all
  select ref.id, count(*) 
  from ref
  join prc3 on prc3.id = ref.id
  group by ref.id
  ) a
group by a.id)
, cte AS
  (
    SELECT ref.ID,ref.prodname,t.cnt ,1 rn FROM ref
      INNER JOIN maxrowperid t
      ON ref.ID = t.ID
    UNION ALL

    SELECT cte.ID, cte.prodname, (cte.cnt - 1) cnt,(cte.rn  1) rn
    FROM cte INNER JOIN maxrowperid t
      ON cte.ID = t.ID
    WHERE cte.cnt > 1
)
SELECT cte.ID, cte.prodname,ISNULL(p1.price,0) price1,ISNULL(p2.price,0) price2,ISNULL(p3.price,0) price3
FROM cte
left join p1 on p1.id = cte.id and p1.rn= cte.rn
left join p2 on p2.id = cte.id and p2.rn= cte.rn
left join p3 on p3.id = cte.id and p3.rn= cte.rn

ORDER BY 1