0

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]
1

You cannot get it on this way.

First, have a look at next query:

SELECT d.*, ti2.*
FROM #temp_import_2 ti2
INNER JOIN (select temp_import_id, product_id, 
                   ROW_NUMBER() OVER (PARTITION BY temp_import_id ORDER BY contact_product_id) AS thisRowNum
            from #contactProducts) d 
ON    d.temp_import_id = ti2.temp_import_id;
GO
temp_import_id | product_id | thisRowNum | temp_import_2_id | temp_import_id | PRODUCT_IDS                                       | PRODUCT_ID_1 | PRODUCT_ID_2 | PRODUCT_ID_3 | PRODUCT_ID_4 | PRODUCT_ID_5 | PRODUCT_ID_6 | PRODUCT_ID_7 | PRODUCT_ID_8 | PRODUCT_ID_9 | PRODUCT_ID_10
-------------: | :--------- | :--------- | ---------------: | -------------: | :------------------------------------------------ | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :------------
             3 | B6281-81   | 1          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B100839-1  | 2          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B101203-5  | 3          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B101204-7  | 4          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B101204-7  | 5          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         

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

SELECT d.*, ti2.*
FROM #temp_import_2 ti2
INNER JOIN (select temp_import_id, product_id, 
                   ROW_NUMBER() OVER (PARTITION BY temp_import_id ORDER BY contact_product_id) AS thisRowNum
            from #contactProducts) d 
ON    d.temp_import_id = ti2.temp_import_id;
GO
temp_import_id | product_id | thisRowNum | temp_import_2_id | temp_import_id | PRODUCT_IDS                                       | PRODUCT_ID_1 | PRODUCT_ID_2 | PRODUCT_ID_3 | PRODUCT_ID_4 | PRODUCT_ID_5 | PRODUCT_ID_6 | PRODUCT_ID_7 | PRODUCT_ID_8 | PRODUCT_ID_9 | PRODUCT_ID_10
-------------: | :--------- | :--------- | ---------------: | -------------: | :------------------------------------------------ | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :------------
             3 | B6281-81   | 1          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B100839-1  | 2          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B101203-5  | 3          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B101204-7  | 4          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
             3 | B101204-7  | 5          |                1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | null         | null         | null         | null         | null         | null         | null         | null         | null         | null         
with rnum as 
(
    select temp_import_id, product_id, 
           ROW_NUMBER() OVER (PARTITION BY temp_import_id ORDER BY contact_product_id) AS thisRowNum
    from #contactProducts
)
UPDATE #temp_import_2
SET    PRODUCT_ID_1 = (SELECT COALESCE(product_id, '1') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 1),
       PRODUCT_ID_2 = (SELECT COALESCE(product_id, '2') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 2),
       PRODUCT_ID_3 = (SELECT COALESCE(product_id, '3') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 3),
       PRODUCT_ID_4 = (SELECT COALESCE(product_id, '4') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 4),
       PRODUCT_ID_5 = (SELECT COALESCE(product_id, '5') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 5),
       PRODUCT_ID_6 = (SELECT COALESCE(product_id, '6') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 6),
       PRODUCT_ID_7 = (SELECT COALESCE(product_id, '7') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 7),
       PRODUCT_ID_8 = (SELECT COALESCE(product_id, '8') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 8),
       PRODUCT_ID_9 = (SELECT COALESCE(product_id, '9') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 9),
       PRODUCT_ID_10 = (SELECT COALESCE(product_id, '10') from rnum where temp_import_id = ti2.temp_import_id and thisRowNum = 10)
FROM   #temp_import_2 ti2
;

select * from #temp_import_2;
GO
temp_import_2_id | temp_import_id | PRODUCT_IDS                                       | PRODUCT_ID_1 | PRODUCT_ID_2 | PRODUCT_ID_3 | PRODUCT_ID_4 | PRODUCT_ID_5 | PRODUCT_ID_6 | PRODUCT_ID_7 | PRODUCT_ID_8 | PRODUCT_ID_9 | PRODUCT_ID_10
---------------: | -------------: | :------------------------------------------------ | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :----------- | :------------
               1 |              3 | B6281-81,B100839-1,B101203-5,B101204-7,B102472-14 | B6281-81     | B100839-1    | B101203-5    | B101204-7    | B101204-7    | null         | null         | null         | null         | null         

dbfiddle here

0

You need to perform additional logic on the #temp_import_2 table to make that a single row, eliminating the multiple rows, which will not update as you expect them to. Effectively you are pivoting the data here. A pivot command, or multiple temp tables may be more efficient at getting you the results that you desire, but the following is a functional start where I've used command table expressions to do the work of pivoting the data.

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]

;
WITH    cteAllProducts
          AS ( SELECT   temp_import_id ,
                        product_id ,
                        ROW_NUMBER() OVER ( PARTITION BY temp_import_id ORDER BY contact_product_id ) AS thisRowNum
               FROM     #contactProducts
             ),
        cteConvergedProducts
          AS ( SELECT   d.temp_import_id ,
                        PRODUCT_ID_1 = CASE WHEN d.thisRowNum = 1
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_2 = CASE WHEN d.thisRowNum = 2
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_3 = CASE WHEN d.thisRowNum = 3
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_4 = CASE WHEN d.thisRowNum = 4
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_5 = CASE WHEN d.thisRowNum = 5
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_6 = CASE WHEN d.thisRowNum = 6
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_7 = CASE WHEN d.thisRowNum = 7
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_8 = CASE WHEN d.thisRowNum = 8
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_9 = CASE WHEN d.thisRowNum = 9
                                            THEN d.product_id
                                       END ,
                        PRODUCT_ID_10 = CASE WHEN d.thisRowNum = 10
                                             THEN d.product_id
                                        END
               FROM     cteAllProducts d
                        INNER JOIN #temp_import_2 t ON d.temp_import_id = t.temp_import_id
             ),
        cteGroupedResults
          AS ( SELECT   c.temp_import_id ,
                        PRODUCT_ID_1 = COALESCE(MAX(c.PRODUCT_ID_1), '1') ,
                        PRODUCT_ID_2 = COALESCE(MAX(c.PRODUCT_ID_2), '2') ,
                        PRODUCT_ID_3 = COALESCE(MAX(c.PRODUCT_ID_3), '3') ,
                        PRODUCT_ID_4 = COALESCE(MAX(c.PRODUCT_ID_4), '4') ,
                        PRODUCT_ID_5 = COALESCE(MAX(c.PRODUCT_ID_5), '5') ,
                        PRODUCT_ID_6 = COALESCE(MAX(c.PRODUCT_ID_6), '6') ,
                        PRODUCT_ID_7 = COALESCE(MAX(c.PRODUCT_ID_7), '7') ,
                        PRODUCT_ID_8 = COALESCE(MAX(c.PRODUCT_ID_8), '8') ,
                        PRODUCT_ID_9 = COALESCE(MAX(c.PRODUCT_ID_9), '9') ,
                        PRODUCT_ID_10 = COALESCE(MAX(c.PRODUCT_ID_10), '10')
               FROM     cteConvergedProducts c
               GROUP BY c.temp_import_id
             )
    UPDATE  #temp_import_2
    SET     PRODUCT_ID_1 = c.PRODUCT_ID_1 ,
            PRODUCT_ID_2 = c.PRODUCT_ID_2 ,
            PRODUCT_ID_3 = c.PRODUCT_ID_3 ,
            PRODUCT_ID_4 = c.PRODUCT_ID_4 ,
            PRODUCT_ID_5 = c.PRODUCT_ID_5 ,
            PRODUCT_ID_6 = c.PRODUCT_ID_6 ,
            PRODUCT_ID_7 = c.PRODUCT_ID_7 ,
            PRODUCT_ID_8 = c.PRODUCT_ID_8 ,
            PRODUCT_ID_9 = c.PRODUCT_ID_9 ,
            PRODUCT_ID_10 = c.PRODUCT_ID_10
    FROM    #temp_import_2 t
            JOIN cteGroupedResults c ON c.temp_import_id = t.temp_import_id;

SELECT  *
FROM    [#temp_import_2];

DROP TABLE #contactProducts;

DROP TABLE [#temp_import_2];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.