1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
Create table final1
(
product_id int,
shop_code nvarchar(20),
doc_date datetime,
diff2 int,
diff1 int,
BASEITLOG int,
base int,
TotalSum int
)
insert into final1 values
(11628,'00664НСК','2015-01-01 00:00:00.000',0,208,318,318,526),
(11628,'00664НСК','2015-02-01 00:00:00.000',24,290,345,369,659),
(11628,'00664НСК','2015-03-01 00:00:00.000',12,0,503,515,515),
(11628,'00664НСК','2015-04-01 00:00:00.000',0,273,264,264,537),
(11628,'00664НСК','2015-05-01 00:00:00.000',0,195,266,266,461),
(11628,'00664НСК','2015-06-01 00:00:00.000',0,0,265,265,265),
(11628,'00664НСК','2015-07-01 00:00:00.000',482,0,231,713,713),
(11628,'00664НСК','2015-08-01 00:00:00.000',182,0,333,515,515),
(11628,'00664НСК','2015-09-01 00:00:00.000',287,0,376,663,663),
(11628,'00664НСК','2015-10-01 00:00:00.000',180,0,232,412,412),
(11628,'00664НСК','2015-11-01 00:00:00.000',0,0,197,197,197),
(11628,'00664НСК','2015-12-01 00:00:00.000',596,0,952,1548,1548),
(11628,'00664НСК','2016-01-01 00:00:00.000',0,0,375,375,375),
(11628,'00664НСК','2016-02-01 00:00:00.000',256,164,252,508,672),
(11628,'00664НСК','2016-03-01 00:00:00.000',0,0,267,267,267),
(11628,'00664НСК','2016-04-01 00:00:00.000',0,0,176,176,176),
(11628,'00664НСК','2016-05-01 00:00:00.000',0,0,181,181,181),
(11628,'00664НСК','2016-06-01 00:00:00.000',0,0,236,236,236),
(11628,'00664НСК','2016-07-01 00:00:00.000',16,455,208,224,679),
(11628,'00664НСК','2016-08-01 00:00:00.000',0,0,185,185,185),
(11628,'00664НСК','2016-09-01 00:00:00.000',0,0,196,196,196),
(11628,'00664НСК','2016-10-01 00:00:00.000',0,0,201,201,201),
(11628,'00664НСК','2016-11-01 00:00:00.000',0,454,207,207,661),
(11628,'00664НСК','2016-12-01 00:00:00.000',0,0,275,275,275),
(11628,'00664НСК','2017-01-01 00:00:00.000',0,212,116,116,328),
(11628,'00664НСК','2017-02-01 00:00:00.000',0,170,125,125,295),
(11628,'00664НСК','2017-03-01 00:00:00.000',0,0,242,242,242),
(11628,'00664НСК','2017-04-01 00:00:00.000',0,0,136,136,136),
(11628,'00664НСК','2017-05-01 00:00:00.000',0,0,193,193,193),
(11628,'00664НСК','2017-06-01 00:00:00.000',251,0,219,470,470),
(11628,'00664НСК','2017-07-01 00:00:00.000',0,0,183,183,183),
(11628,'00664НСК','2017-08-01 00:00:00.000',0,11,219,219,230),
(11628,'00664НСК','2017-09-01 00:00:00.000',0,204,194,194,398),
(11628,'00664НСК','2017-10-01 00:00:00.000',0,81,204,204,285),
(11628,'00664НСК','2017-11-01 00:00:00.000',0,60,191,191,251),
(11628,'00664НСК','2017-12-01 00:00:00.000',16,207,251,267,474),
(11628,'00664НСК','2018-01-01 00:00:00.000',0,0,148,148,148),
(11628,'00664НСК','2018-02-01 00:00:00.000',0,0,164,164,164),
(11628,'00664НСК','2018-03-01 00:00:00.000',0,0,277,277,277),
(11628,'00664НСК','2018-04-01 00:00:00.000',0,0,137,137,137),
(11628,'00664НСК','2018-05-01 00:00:00.000',0,274,194,194,468),
(11628,'00664НСК','2018-06-01 00:00:00.000',12,30,184,196,226),
(11628,'00664НСК','2018-07-01 00:00:00.000',0,0,134,134,134),
(11628,'00664НСК','2018-08-01 00:00:00.000',0,32,116,116,148),
(11628,'00664НСК','2018-09-01 00:00:00.000',0,115,117,117,232)
;With CTE
AS
(
SELECT *,YEAR(doc_date) AS DocYr,DATENAME(mm,doc_date) AS DocMnth
FROM final1
)
SELECT variable,product_id,shop_code,DocYr AS [Year],
MAX(CASE WHEN DocMnth = 'January' THEN val END) AS Jan,
MAX(CASE WHEN DocMnth = 'February' THEN val END) AS Feb,
MAX(CASE WHEN DocMnth = 'March' THEN val END) AS Mar,
MAX(CASE WHEN DocMnth = 'April' THEN val END) AS Apr,
MAX(CASE WHEN DocMnth = 'May' THEN val END) AS May,
MAX(CASE WHEN DocMnth = 'June' THEN val END) AS Jun,
MAX(CASE WHEN DocMnth = 'July' THEN val END) AS Jul,
MAX(CASE WHEN DocMnth = 'August' THEN val END) AS Aug,
MAX(CASE WHEN DocMnth = 'September' THEN val END) AS Sep,
MAX(CASE WHEN DocMnth = 'October' THEN val END) AS Oct,
MAX(CASE WHEN DocMnth = 'November' THEN val END) AS Nov,
MAX(CASE WHEN DocMnth = 'December' THEN val END) AS [Dec]
FROM
(
SELECT product_id ,
shop_code ,
diff2 ,
diff1 ,
BASEITLOG ,
base ,
TotalSum ,DocYr,DocMnth
FROM CTE
)c
UNPIVOT(val FOR variable IN ([diff2],[diff1],[BASEITLOG],[base],[TotalSum]))u
GROUP BY variable,product_id,shop_code,DocYr
