Self join to get desired result

• syed_3177

  SSC Enthusiast

  Points: 137

  More actions

  July 29, 2018 at 4:02 am

  #407256

  I have a table where i have column (ItemNo,Year,Month(Jan,Feb,Mar...Dec)

  But i need in this format as one item no and all years in one line

  attached image to your refrel

• pietlinden

  SSC Guru

  Points: 63517

  More actions

  July 29, 2018 at 9:43 am

  #1999392

  To the OP - posting your data as attached spreadsheets is a less than ideal way of posting your problem. Please read Jeff's article on how to post a question so it will be answered, which is here. 

  For everyone else, the original table looks like this:
  CREATE TABLE srcTable (
            ItemNo CHAR(10),
            Year INT,
            January INT,
            February INT,
  . ...
              December INT);

  What you really want is
  CREATE TABLE goodTable (ItemNo CHAR(10),
                                               EventDate DATE,
                                               Amount INT);

  So you basically have to unpivot the data so that you can actually query your data. Right now you have a crosstab.
  Found this article by Dwain Camps (gonna have to go read all the stuff he wrote... miss him on here!) that tackles this problem 

  Here's an abbreviated version of the query that solves the problem: 
  CREATE TABLE Src (
    ItemNo CHAR(10)
    ,Yr INT
    , January TINYINT
    , February TINYINT
    , March TINYINT
);
GO

  INSERT INTO Src (ItemNo, Yr, January, February, March)
VALUES ('310103X000',2016,0,5,1),
        ('310103X000', 2017,0,0,1),
        ('310104EA10', 2016,1,2,1);

  -- use CROSS APPLY to unpivot
SELECT src.ItemNo, x.SalesMonth, x.qty
FROM src
CROSS APPLY (VALUES ('January',January),('February',February),('March',March)) x (SalesMonth,Qty);