Need help with SQL table structure transformation

I need to perform update/insert simultaneously changing structure of incoming data.

Think about Shops that have defined work time for each day of the week.

Hopefully, this might explain better what I'm trying to achieve:

worktimeOrigin table:

columns:

• shop_id
• day
• val

data:

shop_id  |  day        |  val
------------------------------
123      | "monday"    | "9:00 AM - 18:00"
123      | "tuesday"   | "9:00 AM - 18:00"  
123      | "wednesday" | "9:00 AM - 18:00"

shop table:

columns:

• id
• worktimeDestination.id

worktimeDestination table:

columns:

• id
• monday
• tuesday
• wednesday

My aim:

I would like to insert data from worktimeOrigin table into worktimeDestination and specify appropriate worktimeDestination for shop.

shop table data:

• 123
• 1 (updated)

worktimeDestination table data:

id  |  monday           |  tuesday          |  wednesday 
-----开发者_开发问答----------------------------------------------------------------------
1   | "9:00 AM - 18:00" | "9:00 AM - 18:00" | "9:00 AM - 18:00" (inserted)

---

Any ideas how to do that?

---

You could use PIVOT, e.g.

;WITH x AS
(
    SELECT 
        ShopID,
        [Monday] = [2],
        [Tuesday] = [3],
        [Wednesday] = [4]
    FROM
    (
        SELECT
            ShopID,
            DW = CASE [Day]
                WHEN 'Monday' THEN 2
                WHEN 'Tuesday' THEN 3
                WHEN 'Wednesday' THEN 4
            END,
            val
        FROM
            dbo.WorkTimeOrigin
        -- WHERE ShopID = 123 or @param
    ) AS d
    PIVOT
    (
        MAX(val)
        FOR DW IN ([2],[3],[4])
    ) AS p
)
/*
INSERT dbo.WorkTimeDestination
(
    id,
    Monday,
    Tuesday,
    Wednesday
)
*/
SELECT 
    s.[WorkTimeDestination.ID],
    [Monday],
    [Tuesday],
    [Wednesday]
FROM x
INNER JOIN dbo.Shop AS s
ON x.ShopID = s.id;

Uncomment the INSERT portion when you are satisfied with the results. Note that not all three rows will necessarily exist, so you may end up with NULL for one or more values.