locked
Self join to get desired result RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-1426568392 posted

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

    B‌ut i need in this format as one item no and all years in one line

    Item No Year January February March April May June July August September October November December
    310103X000 2016 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1
    310103X000 2017 NULL NULL NULL NULL 1 NULL NULL NULL 1 NULL NULL NULL
    310104EA10 2016 1 2 1 4 4 1 1 2 NULL 2 2 2
    310104EA10 2017 NULL 1 NULL 2 NULL NULL NULL 3 1 NULL 1 1
    310104EA10 2018 1 NULL NULL 0 1 NULL 1 NULL NULL NULL NULL NULL
    310302J200 2016 NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL
    310302J200 2017 NULL NULL NULL NULL NULL NULL NULL NULL NULL 2 -1 NULL
    310302J200 2018 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    Item No Jan-16 Feb-16 Mar-16 Apr-16 May-16 Jun-16 Jul-16 Aug-16 Sep-16 Oct-16 Nov-16 Dec-16 Jan-17 Feb-17 Mar-17 Apr-17 May-17 Jun-17 Jul-17 Aug-17 Sep-17 Oct-17 Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18
    310103X000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 NULL NULL NULL NULL 1 NULL NULL NULL 1 NULL NULL NULL              
    310104EA10 1 2 1 4 4 1 1 2 NULL 2 2 2 NULL 1 NULL 2 NULL NULL NULL 3 1 NULL 1 1 1 NULL NULL 0 1 NULL 1
    310302J200 NULL NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2 -1 NULL 1 NULL NULL NULL NULL NULL NULL
    Sunday, July 29, 2018 10:01 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    User77042963 posted
    CREATE TABLE mytable(
   Item_No   VARCHAR(10) NOT NULL 
  ,Year      int  NOT NULL
  ,January   int 
  ,February  int 
  ,March     int 
  ,April     int 
  ,May       int 
  ,June      int 
  ,July      int 
  ,August    int 
  ,September int 
  ,October   int 
  ,November  int 
  ,December  int 
 
);
INSERT INTO mytable(Item_No,Year,January,February,March,April,May,June,July,August,September,October,November,December) VALUES
 ('310103X000',2016,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1)
,('310103X000',2017,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,1,NULL,NULL,NULL)
,('310104EA10',2016,1,2,1,4,4,1,1,2,NULL,2,2,2)
,('310104EA10',2017,NULL,1,NULL,2,NULL,NULL,NULL,3,1,NULL,1,1)
,('310104EA10',2018,1,NULL,NULL,0,1,NULL,1,NULL,NULL,NULL,NULL,NULL);

select Item_No
, Max(case when Year=2016 then January else null end) Jan_16 
, Max(case when Year=2016 then February else null end) Feb_16 
, Max(case when Year=2016 then March else null end) Mar_16 
, Max(case when Year=2016 then April else null end) Apr_16 
, Max(case when Year=2016 then May else null end) May_16 
, Max(case when Year=2016 then June else null end) Jun_16 
, Max(case when Year=2016 then July else null end) Jul_16 
, Max(case when Year=2016 then August else null end) Aug_16 
, Max(case when Year=2016 then September else null end) Sep_16 
, Max(case when Year=2016 then October  else null end) Oct_16 
, Max(case when Year=2016 then  November  else null end) Nov_16 
, Max(case when Year=2016 then  December else null end) Dec_16 

, Max(case when Year=2017 then January else null end) Jan_17 
, Max(case when Year=2017 then February else null end) Feb_17 
, Max(case when Year=2017 then March else null end) Mar_17 
, Max(case when Year=2017 then April else null end) Apr_17 
, Max(case when Year=2017 then May else null end) May_17 
, Max(case when Year=2017 then June else null end) Jun_17 
, Max(case when Year=2017 then July else null end) Jul_17 
, Max(case when Year=2017 then August else null end) Aug_17 
, Max(case when Year=2017 then September else null end) Sep_17 
, Max(case when Year=2017 then October  else null end) Oct_17 
, Max(case when Year=2017 then  November  else null end) Nov_17 
, Max(case when Year=2017 then  December else null end) Dec_17 

, Max(case when Year=2018 then January else null end) Jan_18 
, Max(case when Year=2018 then February else null end) Feb_18 
, Max(case when Year=2018 then March else null end) Mar_18 
, Max(case when Year=2018 then April else null end) Apr_18 
, Max(case when Year=2018 then May else null end) May_18 
, Max(case when Year=2018 then June else null end) Jun_18 
, Max(case when Year=2018 then July else null end) Jul_18 
, Max(case when Year=2018 then August else null end) Aug_18 
, Max(case when Year=2018 then September else null end) Sep_18 
, Max(case when Year=2018 then October  else null end) Oct_18 
, Max(case when Year=2018 then  November  else null end) Nov_18 
, Max(case when Year=2018 then  December else null end) Dec_18 


from mytable
Group by Item_No


drop table mytable

    Monday, July 30, 2018 1:47 PM