CODEWITHSUNDEEP
sqlsql-server
Dreamcatcher_AR
Dreamcatcher_AR

Reputation: 61

Split a row into multiple rows based on a column value into table in SQL SERVER

I have a Table TABLE1 what is need to do is to duplicate the row value based on a coulmn value based on some condition. Shown Below is the table:

INPUT

COMPANY| YEAR| MONTH|ACT_MONTH|AMT
ABC    | 2021|   12 | 0       |100
XYZ    | 2021|   12 | 6       |200

The condition is that if ACT_MON = 0 , then the values has to be replicated for 12 months and if the ACT_MON = 6 then the rows has to be replicated for last 6 months of the year i.e from 06 to 12

REQUIRED OUTPUT

COMPANY| YEAR| MONTH|AMT
------------------------
ABC    | 2021|  01  |100
ABC    | 2021|  02  |100
ABC    | 2021|  03  |100
ABC    | 2021|  04  |100
ABC    | 2021|  05  |100
ABC    | 2021|  06  |100
ABC    | 2021|  07  |100
ABC    | 2021|  07  |100
ABC    | 2021|  09  |100
ABC    | 2021|  10  |100
ABC    | 2021|  11  |100
ABC    | 2021|  12  |100
XYZ    | 2021|  07  |200
XYZ    | 2021|  08  |200
XYZ    | 2021|  19  |200
XYZ    | 2021|  10  |200
XYZ    | 2021|  11  |200
XYZ    | 2021|  12  |200

Upvotes: 0

Views: 1363

Answers (1)

YuTing
YuTing

Reputation: 6629

CREATE TABLE test
(
  [COMPANY] varchar(13), 
  [YEAR] decimal(4,0), 
  [MONTH] decimal(2,0), 
  [ACT_MONTH] decimal(2,0), 
  [AMT] decimal(10,0)
);

INSERT INTO test 
VALUES
  ('ABC', 2021, 12, 0, 100),
  ('XYZ', 2021, 12, 6, 200);

WITH m AS
(
  SELECT *
  FROM (VALUES ('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09'), ('10'), ('11'), ('12')) n(n)
)

SELECT test.COMPANY, test.YEAR , m.n, test.AMT
FROM test
INNER JOIN m
ON m.n > 12 - (case test.ACT_MONTH when 0 then 12 else test.ACT_MONTH end)

sqlfiddle

Upvotes: 2

Related Questions