CODEWITHSUNDEEP
sql
selva k s
selva k s

Reputation: 1

How to combine tables with combining of cte with join query

How can I combine those tables with combining of cte with join query?

enter image description here

WITH CTE AS(
    SELECT *,
        Rn = ROW_NUMBER() OVER(PARTITION BY productCode ORDER BY productId)
    FROM sellingprice
) 
sELECT
    *,
    productcode =  CASE
                WHEN Rn = 0 THEN productCode
                ELSE productCode+ CHAR(65 + Rn -1)
            END
from CTE

Upvotes: 0

Views: 91

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

If you are looking for combining the two queries, the CTE and the other query. Then you can do this:

WITH CTE 
AS
(
    SELECT *,
        Rn = ROW_NUMBER() OVER(PARTITION BY productCode ORDER BY productId)
    FROM sellingprice
), CTE2
AS
( -- you can put here the second query you need to combine with the first cte
    SELECT
      sp.productid, sp.productname, ...
    from sellingPrice sp
    inner join cost ...
)
-- here the two queries are accessible from cte, cte2 
SELECT *,
  productcode =  CASE
               WHEN Rn = 0 THEN productCode
               ELSE productCode+ CHAR(65 + Rn -1)
                END, .... -- you can select other columns
from CTE c1
INNER JOIN CTE2 c2 ON c1.productid = c2.productid

Upvotes: 1

Related Questions