How to muptiply rows in existing table on SQL database?

Question

I have table on my SQL database

Code | Ordered | Cut01 | Cut02 | Cut03 | Confirmed
--------------------------------------------------
8832     10        1        1      3        5
8821     12        0        1      2        9
8122     20       10        0      0       10
8901     11        0        8      1        2

How can I separate rows for each Code, which have several Cuts* that is <> 0 and multiply themselves like:

Code | Ordered | Cut01 | Cut02 | Cut03 | Confirmed
--------------------------------------------------
8832      1        1        0      0        0
8832      1        0        1      0        0
8832      8        0        0      3        5
--------------------------------------------------
8821      1        0        1      0        0
8821     11        0        0      2        9
--------------------------------------------------
8122     20       10        0      0       10
--------------------------------------------------
8901      8        0        8      0        0
8901      3        0        0      1        2

Confirmed = Ordered - Cut01 - Cut02 - Cut03

As you can see in result table Sum of Ordered for each code = Ordered for this code from first table, also it works for Sum of Confirmed. But in each of row I have only single Cut that is not equal to 0. How can I do it by using T-SQL?

Not sure if it can be, but what would you like as output if all of Cut1, Cut2 and Cut3 are 0? No row at all or one with all of them 0 and confirmed set to same value as Ordered? — Joachim Isaksson, Aug 09 '12 at 10:37
Yes you are right. If Cut1 = Cut2 = Cut3 = 0 then Confirmed = Ordered. — mbigun, Aug 09 '12 at 10:40

score 3 · Answer 1 · answered Aug 09 '12 at 11:01

SELECT
  Code,
  CASE WHEN sub_row = 1 THEN cut01
       WHEN sub_row = 2 THEN cut02
       WHEN sub_row = 3 THEN ordered - cut01 - cut02 END     Ordered,
  CASE WHEN sub_row = 1 THEN cut01     ELSE 0 END            cut01,
  CASE WHEN sub_row = 2 THEN cut02     ELSE 0 END            cut02,
  CASE WHEN sub_row = 3 THEN cut03     ELSE 0 END            cut03,
  CASE WHEN sub_row = 3 THEN confirmed ELSE 0 END            confirmed
FROM
  yourTable
CROSS JOIN
  (          SELECT 1 AS sub_row
   UNION ALL SELECT 2 AS sub_row
   UNION ALL SELECT 3 AS sub_row ) AS multiplier_table
WHERE
     (sub_row = 1 AND cut01 > 0)
  OR (sub_row = 2 AND cut02 > 0)
  OR (sub_row = 3 AND cut03 > 0)
  OR (sub_row = 3 AND cut01 = 0 AND cut02 = 0 AND cut03 = 0)

I like this one, it's fairly simple and straight forward :) – Joachim Isaksson Aug 09 '12 at 15:18 — Joachim Isaksson, Aug 09 '12 at 15:18

score 2 · Answer 2 · answered Aug 09 '12 at 15:20

I see you already have your solution, however I just thought I'd add another version since I wrote it anyway :-)

SELECT Code,
       conf + Cut01 + Cut02 + Cut03 AS Ordered, 
       Cut01, Cut02, Cut03, conf AS Confirmed
FROM
 (
         SELECT Code, Cut01, 0 AS Cut02, 0 AS Cut03, 
           CASE WHEN Cut01<>0 AND Cut02=0 AND Cut03=0 
             THEN Confirmed ELSE 0 END conf FROM MyTable

   UNION SELECT Code, 0 AS Cut01, Cut02, 0 AS Cut03, 
           CASE WHEN Cut02<>0 AND Cut03=0 
             THEN Confirmed ELSE 0 END conf FROM MyTable

   UNION SELECT Code, 0 AS Cut01, 0 AS Cut02, Cut03, 
           CASE WHEN Cut03<>0 OR Cut01=0 AND Cut02=0 AND Cut03=0
             THEN Confirmed ELSE 0 END conf FROM MyTable
  ) a
WHERE conf + Cut01 + Cut02 + Cut03<>0 ORDER BY Code

+1 This method seems to be the simplest and the best, and it returns expected output for all rows. I would advise OP to use this query. — Ivan Golović, Aug 10 '12 at 07:01

score 1 · Answer 3 · answered Aug 09 '12 at 11:06

Start here:

declare @Table as Table ( Number Int )
insert into @Table ( Number ) values ( 2 ), ( 5 )

; with Numbers as (
  select 1 as Number
  union all
  select Number + 1
    from Numbers
    where Number < 100
  )
select *
  from @Table as T inner join
    Numbers as N on N.Number <= T.Number

The hardcoded 100 can be replaced with the maximum number of rows that are needed.

Ivan Golović · Accepted Answer · 2012-08-09T14:17:41.650

1

Here is another way of doing it:

DECLARE @t TABLE (Code INT, Ordered INT, Cut01 INT, 
Cut02 INT, Cut03 INT, Confirmed INT)
INSERT @t 
VALUES
(8832,10,   1,   1, 3,   5)
,(8821,12,   0,   1, 2,   9)
,(8122,20,  10,   0, 0,  10)
,(8901,11,   0,   8, 1,   2)
,(1000,2,   0,   0, 0,   2)

;WITH x AS (
    SELECT  a.Code,
            CASE WHEN ColName = 'Cut01' THEN u.Value ELSE 0 END Cut01,
            CASE WHEN ColName = 'Cut02' THEN u.Value ELSE 0 END Cut02,
            CASE WHEN ColName = 'Cut03' THEN u.Value ELSE 0 END Cut03
    FROM    @t a
    JOIN    (
                SELECT  Value,
                        ColName,
                        Code
                FROM    @t
                UNPIVOT 
                (Value FOR ColName IN (Cut01, Cut02, Cut03)) unpvt
            ) u ON u.Code = a.Code
), y AS
(
    SELECT  *,
            ROW_NUMBER() OVER 
                (PARTITION BY Code ORDER BY Cut01 , Cut02 , Cut03) 
                AS LastRowForCode
    FROM    x
    WHERE   Cut01 <> 0 OR Cut02 <> 0 OR Cut03 <> 0
), z AS
(
    SELECT  COALESCE(y.Code, b.Code) Code,
            COALESCE(y.Cut01, b.Cut01) Cut01,
            COALESCE(y.Cut02, b.Cut02) Cut02,
            COALESCE(y.Cut03, b.Cut03) Cut03,
            CASE WHEN Confirmed IS NULL THEN 0 ELSE Confirmed END 
                AS Confirmed
    FROM    y
    FULL    JOIN    
            @t b ON 
            b.Code = y.Code
            AND y.LastRowForCode = 1
)

SELECT  Code,
        Confirmed + Cut01 + Cut02 + Cut03 Ordered,
        Cut01,
        Cut02,
        Cut03,
        Confirmed
FROM    z
ORDER BY Code DESC, Cut01 DESC , Cut02 DESC, Cut03 DESC

edited Aug 09 '12 at 14:17

answered Aug 09 '12 at 11:43

Ivan Golović

8,732
3
25
31

@mbigun Great! I'm glad to help, i further simplified the code. – Ivan Golović Aug 09 '12 at 12:15
If all cuts for a particular `code` are `0`, wouldn't this discard the entire code? – MatBailie Aug 09 '12 at 12:31
@mbigun I changed the query, I tried this query as well as other answers posted but none of them returned results as you needed. I changed it now and it returns results just as you need them. I also added a row to sample data which has all Cuts set to 0, it is also returned as expected. – Ivan Golović Aug 09 '12 at 14:29
Ivan, I really appreciate you. You saved a couple days for me! Also, as you said, I have rows with zero Cuts and now it works PERFECT! Thanks for the time you spent for this solution. – mbigun Aug 09 '12 at 15:01
@IvanG - In what way does my CROSS JOIN method fail? It should be quite simple to make work, there may just be a simple mistake? – MatBailie Aug 09 '12 at 20:40
@Dems I didn't say your method failed, I just said that at a certain time none of the methods returned exactly the same output as described by OP. BTW, I was the first one to upvote your method. For Code 8122 none of the methods returned expected output so I adjusted my query and OP said that now he is getting result exactly as expected. – Ivan Golović Aug 10 '12 at 06:51
@IvanG How can I put result of this query to the table? I'm trying to delete old data, and insert in the same table new data after code execution like: `DELETE FROM MyTable` after deletion `INSERT INTO MyTable (SELECT ... FROM z)`. How can I do it with `WITH AS` block in my code? – mbigun Aug 10 '12 at 11:55
@mbigun For an `INSERT` to table from Common Table Expression (`WITH AS` block) take a look at this: http://stackoverflow.com/questions/3306096/combining-insert-into-and-with-cte – Ivan Golović Aug 10 '12 at 17:04

How to muptiply rows in existing table on SQL database?

4 Answers4