CODEWITHSUNDEEP
sqlsql-server
user1913919
user1913919

Reputation: 53

sql server get the attributes from the rollup

I have data table like below

Prod-Code Name              Rollup1 Rollup2 EndProd-Code
--------- ----------------- ------- ------- ------------
P100      Potatoes          P2         P3   P100
P200      Potatoes GS       P4         P5   P200
P300      Potatoes GSS      P1         P6   P100

What I want to do is create a query that would fetch the row as is if the Prod-Code = EndProd-Code. But if they are different I want to override the values of Rollup1 and Rollup2 with the values coming from the EndProd-Code row

An example run of the query with the above data would result below. See how the rollups of Prod-Code P300 is overriden with the rollup values from P100 since it has that EndProd-Code

Prod-Code Name              Rollup1 Rollup2 EndProd-Code
--------- ----------------- ------- ------- ------------
P100      Potatoes          P2      P3      P100
P200      Potatoes GS       P4      P5      P200
P300      Potatoes GSS      P2      P3      P100

Upvotes: 1

Views: 61

Answers (2)

Nicholas Carey
Nicholas Carey

Reputation: 74345

Assuming your Prod-Code column is the primary key (or at least is unique), the following should do nicely:

select ProdCode    = t1.ProdCode ,
       Name        = t1.Name     ,
       Rollup1     = coalesce( t2.Rollup1 , t1.Rollup1 ) ,
       Rollup2     = coalesce9 t2.Rollup2 , t2.Rollup2 ) ,
       EndProdCode =  t1.EndProdCode
from      myTable t1
left join myTable t2 on t2.ProdCode  = t1.EndProdCode
                    and t2.ProdCode != t1.ProdCode

Upvotes: 0

PM 77-1
PM 77-1

Reputation: 13344

SELECT p.ProdCode, p.Name, CASE WHEN p.ProdCode=p.EndProdCode THEN p.Rollup1 ELSE pm.Rollup1 END [Rollup1],
       CASE WHEN p.ProdCode=p.EndProdCode THEN p.Rollup2 ELSE pm.Rollup2 END [Rollup2]
FROM ProdTable p
JOIN ProdTable pm ON (pm.ProdCode=pm.EndProdCode and p.EndProdCode=pm.EndProdCode)
ORDER BY p.ProdCode;

SQL Fiddle: http://sqlfiddle.com/#!3/fb6b5/3

Upvotes: 2

Related Questions