Running MS SQL 2008 R2. I am provided a CSV list of product-IDs which I parse into individual rows and normalize. I then need to take those product-IDs (anywhere from 1-10) and write them to their corresponding columns in a table. I'm using a combination of CASE statements, a derived table, along with ROW_NUMBER in order to assign the correct product-ID value to each corresponding column. For some reason, it only seems to update the product_id_1 column, which is not right since there are other values stored in the #contactProducts table Below please find the T-SQL to recreate my issue:
CREATE TABLE #contactProducts(
[contact_product_id] [int] IDENTITY(1,1) NOT NULL,
[temp_import_id] [int] NOT NULL,
[product_id] [varchar](50) NOT NULL,
[createDt] [datetime] NOT NULL,
CONSTRAINT [PK_#contactProducts] PRIMARY KEY CLUSTERED
(
[contact_product_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO #contactProducts VALUES(3,'B6281-81',getDate())
INSERT INTO #contactProducts VALUES(3,'B100839-1',getDate())
INSERT INTO #contactProducts VALUES(3,'B101203-5',getDate())
INSERT INTO #contactProducts VALUES(3,'B101204-7',getDate())
INSERT INTO #contactProducts VALUES(3,'B101204-7',getDate())
SELECT * FROM #contactProducts
-- Temp_Import_2
CREATE TABLE #temp_import_2(
[temp_import_2_id] [int] IDENTITY(1,1) NOT NULL,
[temp_import_id] [int] NOT NULL,
[PRODUCT_IDS] [varchar](2000) NULL,
[PRODUCT_ID_1] [varchar](50) NULL,
[PRODUCT_ID_2] [varchar](50) NULL,
[PRODUCT_ID_3] [varchar](50) NULL,
[PRODUCT_ID_4] [varchar](50) NULL,
[PRODUCT_ID_5] [varchar](50) NULL,
[PRODUCT_ID_6] [varchar](50) NULL,
[PRODUCT_ID_7] [varchar](50) NULL,
[PRODUCT_ID_8] [varchar](50) NULL,
[PRODUCT_ID_9] [varchar](50) NULL,
[PRODUCT_ID_10] [varchar](50) NULL,
CONSTRAINT [temp_import_2] PRIMARY KEY CLUSTERED
(
[temp_import_2_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO #temp_import_2 (temp_import_id, product_IDs)
VALUES(3, 'B6281-81,B100839-1,B101203-5,B101204-7,B102472-14')
SELECT * FROM [#temp_import_2]
-- update with derived table
-- even tho there are values for thisRowNum = 2,3,4, etc. they don't seem to update. only works for thisRowNum = 1
UPDATE #temp_import_2 SET
#temp_import_2.PRODUCT_ID_1 = CASE WHEN derivedTbl.thisRowNum = 1 THEN derivedTbl.product_id END
,#temp_import_2.PRODUCT_ID_2 = CASE WHEN derivedTbl.thisRowNum = 2 THEN derivedTbl.product_id ELSE '2' END
,#temp_import_2.PRODUCT_ID_3 = CASE WHEN derivedTbl.thisRowNum = 3 THEN derivedTbl.product_id ELSE '3' END
,#temp_import_2.PRODUCT_ID_4 = CASE WHEN derivedTbl.thisRowNum = 4 THEN derivedTbl.product_id ELSE '4' END
,#temp_import_2.PRODUCT_ID_5 = CASE WHEN derivedTbl.thisRowNum = 5 THEN derivedTbl.product_id ELSE '5' END
,#temp_import_2.PRODUCT_ID_6 = CASE WHEN derivedTbl.thisRowNum = 6 THEN derivedTbl.product_id ELSE '6' END
,#temp_import_2.PRODUCT_ID_7 = CASE WHEN derivedTbl.thisRowNum = 7 THEN derivedTbl.product_id ELSE '7' END
,#temp_import_2.PRODUCT_ID_8 = CASE WHEN derivedTbl.thisRowNum = 8 THEN derivedTbl.product_id ELSE '8' END
,#temp_import_2.PRODUCT_ID_9 = CASE WHEN derivedTbl.thisRowNum = 9 THEN derivedTbl.product_id ELSE '9' END
,#temp_import_2.PRODUCT_ID_10 = CASE WHEN derivedTbl.thisRowNum = 10 THEN derivedTbl.product_id ELSE '10' END
FROM (
select temp_import_id, product_id, ROW_NUMBER() OVER (PARTITION BY temp_import_id ORDER BY contact_product_id) AS thisRowNum
from #contactProducts
) derivedTbl INNER JOIN #temp_import_2 ON derivedTbl.temp_import_id = #temp_import_2.temp_import_id
SELECT * FROM [#temp_import_2]
DROP TABLE #contactProducts
DROP TABLE [#temp_import_2]
Best Answer
You cannot get it on this way.
First, have a look at next query:
As you can see, it returns 5 rows. And you cannot update one record from 5 rows using a CASE statement.
May be there is a easiest solution, but you must add a single SELECT for each PRODUCT_X
dbfiddle here