July 31, 2015 at 12:39 am
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output
I am not sure if subject makes sense. But my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
I am interested to know if there are other ways to simply update this data.
Thanks,
Naveen.
Every thought is a cause and every condition an effect
July 31, 2015 at 8:24 am
Try this...
UPDATE h SET
h.CHANNEL1 = vc.C1,
h.CHANNEL2 = vc.c2
FROM
#Hori h
JOIN (
SELECT
v.FILTER,
MAX(CASE WHEN v.CHANNEL = 1 THEN v.VALUE END) AS C1,
MAX(CASE WHEN v.CHANNEL = 2 THEN v.VALUE END) AS C2
FROM
#Vert v
GROUP BY
v.FILTER
) vc
ON h.FILTER = vc.FILTER
SELECT * FROM #Hori h
July 31, 2015 at 9:47 am
I'm just wondering why would you need to update these columns as you shouldn't store values this way if you want a normalized table. And if this is for a report, the update seems an unnecessary step.
July 31, 2015 at 10:56 am
Luis Cazares (7/31/2015)
I'm just wondering why would you need to update these columns as you shouldn't store values this way if you want a normalized table. And if this is for a report, the update seems an unnecessary step.
This is for an existing report. I am re-writing a stored procedure that is used by multiple teams. So report format cannot be changed.
Thanks,
Naveen.
Every thought is a cause and every condition an effect
July 31, 2015 at 10:58 am
Jason A. Long (7/31/2015)
Try this...
UPDATE h SET
h.CHANNEL1 = vc.C1,
h.CHANNEL2 = vc.c2
FROM
#Hori h
JOIN (
SELECT
v.FILTER,
MAX(CASE WHEN v.CHANNEL = 1 THEN v.VALUE END) AS C1,
MAX(CASE WHEN v.CHANNEL = 2 THEN v.VALUE END) AS C2
FROM
#Vert v
GROUP BY
v.FILTER
) vc
ON h.FILTER = vc.FILTER
SELECT * FROM #Hori h
Great. Thanks Jason, this works good for me.
Thanks,
Naveen.
Every thought is a cause and every condition an effect
July 31, 2015 at 12:08 pm
Naveen PK (7/31/2015)
Luis Cazares (7/31/2015)
I'm just wondering why would you need to update these columns as you shouldn't store values this way if you want a normalized table. And if this is for a report, the update seems an unnecessary step.This is for an existing report. I am re-writing a stored procedure that is used by multiple teams. So report format cannot be changed.
I understand that a report format can't be changed. However, I'm not sure if the update could be removed and the columns can be populated during the insert. I can't be certain about it without more information on the process, but it seems possible.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply