Asked by:
Self join to get desired result

Question
-
User-1426568392 posted
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
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
-
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