CODEWITHSUNDEEP
sqlsql-serverunpivot
user1902540
user1902540

Reputation: 111

Splitting One Row into Many Based on Month

I have a table formatted like the one below, i want to split it up so theres a column for month and year ie. January 2014 then another column for cost. So effectively each row would be split into 12 different rows, but i cant for the life of me figure out how to approach it. Any help would be greatly appreciated

    CREATE TABLE dbo.Line14(
    ItemID int IDENTITY(1,1) NOT NULL,
    Detail nvarchar(max) NULL,
    Type nvarchar(255) NULL,
    Cat nvarchar(255) NULL,
    Jan_14 money NULL,
    Feb_14 money NULL,
    Mar_14 money NULL,
    Apr_14 money NULL,
    May_14 money NULL,
    Jun_14 money NULL,
    Jul_14 money NULL,
    Aug_14 money NULL,
    Sep_14 money NULL,
    Oct_14 money NULL,
    Nov_14 money NULL,
    Dec_14 money NULL
) ON PRIMARY TEXTIMAGE_ON PRIMARY
GO

Upvotes: 1

Views: 908

Answers (2)

Taryn
Taryn

Reputation: 247720

You should be able to use the UNPIVOT function which converts the data from columns into rows:

select itemid,
  detail,
  type,
  cat,
  Month,
  2014 as Year,
  value
from Line14
unpivot
(
  value
  for Month in (Jan_14, Feb_14, Mar_14, Apr_14,
                May_14, Jun_14, Jul_14, Aug_14,
                Sep_14, Oct_14, Nov_14, Dec_14)
) unpiv

See SQL Fiddle with Demo.

The result would be similar to this:

| ITEMID | DETAIL | TYPE |  CAT |  MONTH | YEAR | VALUE |
---------------------------------------------------------
|      1 |   Test |    C | blah | Jan_14 | 2014 |    10 |
|      1 |   Test |    C | blah | Feb_14 | 2014 |    12 |
|      1 |   Test |    C | blah | Mar_14 | 2014 |    45 |
|      1 |   Test |    C | blah | Apr_14 | 2014 |    56 |
|      1 |   Test |    C | blah | May_14 | 2014 |    89 |
|      1 |   Test |    C | blah | Jun_14 | 2014 |    78 |
|      1 |   Test |    C | blah | Jul_14 | 2014 |    96 |
|      1 |   Test |    C | blah | Aug_14 | 2014 |    35 |
|      1 |   Test |    C | blah | Sep_14 | 2014 |    55 |
|      1 |   Test |    C | blah | Oct_14 | 2014 |    30 |
|      1 |   Test |    C | blah | Nov_14 | 2014 |    99 |
|      1 |   Test |    C | blah | Dec_14 | 2014 |   120 |

Upvotes: 3

Randy
Randy

Reputation: 16677

build the new table as you describe - then write 12 insert as select statements grabbing each month's money value and hardcoding the month value.

Upvotes: 0

Related Questions