CODEWITHSUNDEEP
sqlsql-server
user1773949
user1773949

Reputation: 119

Recursive CTE or While Loop

I'm trying to pivot into rows the columns VNLAMNT, MXBAMNT, HZLAMNT, DARKAMNT, STWBRYAMNT in the Orders table and append the value Description in ChocolateType table to each row in the most efficient manner possible.

The table schemas are like so:

ChocolateTypes 

Id | Description

Orders 

CUSTOMERCLASS | OFFICE | LOCATION | VNLAMNT | MXBAMNT | HZLAMNT | DARKAMNT | STWBRYAMNT

I want the result set to look like this with 15 records (3 descriptions * 5 columns), as per values in tables in Fiddle:

Result

CUSTOMERCLASS | OFFICE | LOCATION | VALUE | TYPE 
Proprietary Head    Chicago 2 Vanilla
Proprietary Head    Chicago 14 MixedBerry
Proprietary Head    Chicago 14 Hazelnut
Proprietary Head    Chicago 14 Dark
Proprietary Head    Chicago 14 Strawberry

I have the tables in Fiddle: http://sqlfiddle.com/#!3/10f89/1/0

VALUE is populated with the value in each of the columns VNLAMNT, MXBAMNT, HZLAMNT, DARKAMNT, STWBRYAMNT. The sequence is always as above. TYPE is populated with Description from the ChocolateTypes table.

I have looked at recursive CTE's and it appears these are not the most efficient way of looping through the records but I am unsure of how to do it either with CTE's or with a While Loop. Any help much appreciated.

Upvotes: 0

Views: 852

Answers (1)

M.Ali
M.Ali

Reputation: 69554

No need for recursive CTE nor while loop simply an UPIVOT query will do the trick, see below.....

;WITH CTE
AS (
    SELECT  ORDERID
           ,CUSTOMERCLASS
           ,OFFICE
           ,LOCATION
           ,VALUE
           ,CASE [TYPE]
             WHEN 'MXBAMNT'    THEN 1
             WHEN 'VNLAMNT'    THEN 2
             WHEN 'HZLAMNT'    THEN 3
             WHEN 'STWBRYAMNT' THEN 4
             WHEN 'DARKAMNT'   THEN 5
            END AS [TYPE]
    FROM dbo.Orders
    UNPIVOT ( VALUE FOR [TYPE]
              IN (VNLAMNT, MXBAMNT, HZLAMNT, DARKAMNT, STWBRYAMNT)
            )up
   )
SELECT  C.ORDERID
       ,C.CUSTOMERCLASS
       ,C.OFFICE
       ,C.LOCATION
       ,C.VALUE
       ,CT.[DESCRIPTION]
FROM CTE C INNER JOIN dbo.ChocolateTypes CT
ON C.[TYPE] = CT.ID

SQL FIDDLE

Upvotes: 2

Related Questions