sql server:Pivot and UNPivot data with date in the columns

?
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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
--行列转换
drop table geovindu
create table geovindu
(ID int,ChangeName nvarchar(25),Number1 int,Number2 int)
 
insert into geovindu
 
select 1,'购物换领',10,1 union all
 
select 1,'积分换领',5,3 union all
 
select 1,'购物换领',3,2 union all
 
select 2,'购物换领',5,3 union all
 
select 2,'积分换领',1,2 union all
select 2,'积分换领',2,2
 
---
select ID,[购物换领(Number1)],[购物换领(Number2)],[积分换领(Number1)],[积分换领(Number2)]
from
(select ID,ChangeName+'('+c+')' 'c',v
from
(select ID,ChangeName,sum(Number1) 'Number1',sum(Number2) 'Number2'
from geovindu
group by ID,ChangeName) a
unpivot(v for c in([Number1],[Number2])) u) b
pivot(max(v) for c in([购物换领(Number1)],[购物换领(Number2)],[积分换领(Number1)],[积分换领(Number2)])) p
go
 
 
--APPLY 有两种形式：CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL。
 
--Create Employees table and insert values.
drop table Employees
go
 
CREATE TABLE Employees
(
    empid   int         NOT NULL
    ,mgrid   int         NULL
    ,empname varchar(25) NOT NULL
    ,salary  money       NOT NULL
    CONSTRAINT PK_Employees PRIMARY KEY(empid)
);
GO
INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00);
INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00);
INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00);
INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00);
INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00);
INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00);
INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00);
INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00);
INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00);
INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00);
INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00);
INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00);
INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00);
INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00);
GO
--Create Departments table and insert values.
drop table Departments
go
 
 
CREATE TABLE Departments
(
    deptid    INT NOT NULL PRIMARY KEY
    ,deptname  VARCHAR(25) NOT NULL
    ,deptmgrid INT NULL REFERENCES Employees
);
GO
INSERT INTO Departments VALUES(1, 'HR',           2);
INSERT INTO Departments VALUES(2, 'Marketing',    7);
INSERT INTO Departments VALUES(3, 'Finance',      8);
INSERT INTO Departments VALUES(4, 'R&D',          9);
INSERT INTO Departments VALUES(5, 'Training',     4);
INSERT INTO Departments VALUES(6, 'Gardening', NULL);
go
 
drop FUNCTION dbo.fn_getsubtree
go
 
 
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT)
    RETURNS @TREE TABLE
(
    empid   INT NOT NULL
    ,empname VARCHAR(25) NOT NULL
    ,mgrid   INT NULL
    ,lvl     INT NOT NULL
)
AS
BEGIN
  WITH Employees_Subtree(empid, empname, mgrid, lvl)
  AS
  (
    -- Anchor Member (AM)
    SELECT empid, empname, mgrid, 0
    FROM Employees
    WHERE empid = @empid
 
    UNION all
     
    -- Recursive Member (RM)
    SELECT e.empid, e.empname, e.mgrid, es.lvl+1
    FROM Employees AS e
      JOIN Employees_Subtree AS es
        ON e.mgrid = es.empid
  )
  INSERT INTO @TREE
    SELECT * FROM Employees_Subtree;
 
  RETURN
END
GO
 
--https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms175156(v=sql.105)
--若要返回每个部门经理的各级下属，请使用以下查询。
SELECT D.deptid, D.deptname, D.deptmgrid
    ,ST.empid, ST.empname, ST.mgrid,ST.lvl
FROM Departments AS D
    CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;
go

　　

 

 SQL Server 2109安装中遇到脱机安装Microsoft机器学习服务器组件无法下一步的解决方法

https://docs.microsoft.com/zh-cn/sql/machine-learning/install/sql-ml-component-install-without-internet-access?view=sql-server-ver15

 

 

把文件名其中的"1033"改為"2052".

 

 

 

 

 

 

 

 

 

?
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
-- --SQL - Pivot data with date in the columns
 --Dynamic sql 动态 geovindu
 DECLARE @sql nvarchar(max)
 DECLARE @s nvarchar(max)   
 SELECT @s=STUFF(( SELECT DISTINCT ',['+CAST(CAST(RecordAddDate as DATE) as nvarchar)+']'  FROM dbo.DuView_RedemptionRecordShopName FOR XML PATH('') ), 1, 1, '')   
 SET @sql=N';WITH cte
 as (SELECT ShopsName,CAST([RecordAddDate] as DATE) [RecordAddDate],SUM([RecordNumber] ) Val
 FROM dbo.DuView_RedemptionRecordShopName where  ProjectId=1
 GROUP BY ShopsName,CAST([RecordAddDate] as DATE))
 SELECT * FROM (SELECT [ShopsName],[RecordAddDate],Val FROM cte) as t 
 pivot (MAX(val) FOR [RecordAddDate] in ('+@s+')) as p'
 print(@sql)
 EXECUTE sp_executesql  @sql
 
 --https://docs.microsoft.com/en-us/answers/questions/411019/sql-pivot-data-with-date-in-the-columns-azure-sql.html
 ;WITH cte
 as (SELECT ShopsName,CAST([RecordAddDate] as DATE) [RecordAddDate],isnull(SUM([RecordNumber]),0) Val
 FROM dbo.DuView_RedemptionRecordShopName  where  ProjectId=1
 GROUP BY ShopsName,CAST([RecordAddDate] as DATE))
 SELECT * FROM (SELECT [ShopsName],[RecordAddDate],isnull(Val,0) Val FROM cte) as t 
 pivot (MAX(val) FOR [RecordAddDate] in ([2021-08-03],[2021-08-04],[2021-08-05],[2021-08-06],[2021-08-07],[2021-08-10],[2021-08-11],[2021-08-12],[2021-08-13],[2021-08-14],[2021-08-21],[2021-08-23],[2021-08-26],[2021-08-29],[2021-09-03],[2021-09-04],[2021-09-06],[2021-10-02],[2021-10-06],[2021-10-07],[2021-10-10],[2021-10-14],[2021-10-17],[2021-10-23],[2021-10-29],[2021-10-30])) as p
 go
 
 --
  ;WITH cte
 as (SELECT ShopsName,RecordChangeCode,CAST([RecordAddDate] as DATE) [RecordAddDate],isnull(SUM([RecordNumber]),0) Val
 FROM dbo.DuView_RedemptionRecordShopName  where  ProjectId=1
 GROUP BY ShopsName,RecordChangeCode,CAST([RecordAddDate] as DATE))
 SELECT * FROM (SELECT [ShopsName],RecordChangeCode,[RecordAddDate],isnull(Val,0) Val FROM cte) as t 
 pivot (MAX(val) FOR [RecordAddDate] in ([2021-08-03],[2021-08-04],[2021-08-05],[2021-08-06],[2021-08-07],[2021-08-10],[2021-08-11],[2021-08-12],[2021-08-13],[2021-08-14],[2021-08-21],[2021-08-23],[2021-08-26],[2021-08-29],[2021-09-03],[2021-09-04],[2021-09-06],[2021-10-02],[2021-10-06],[2021-10-07],[2021-10-10],[2021-10-14],[2021-10-17],[2021-10-23],[2021-10-29],[2021-10-30])) as p
 go

　　

 

 

?
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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
CREATE TABLE geovindu_sales (
  product_name VARCHAR(100),
  store_location VARCHAR(50),
  num_sales INT
);
 
INSERT INTO geovindu_sales (product_name, store_location, num_sales) VALUES
('Chair', 'North', 55),
('Desk', 'Central', 120),
('Couch', 'Central', 78),
('Chair', 'South', 23),
('Chair', 'South', 10),
('Chair', 'North', 98),
('Desk', 'West', 61),
('Couch', 'North', 180),
('Chair', 'South', 14),
('Desk', 'North', 45),
('Chair', 'North', 87),
('Chair', 'Central', 34),
('Desk', 'South', 42),
('Couch', 'West', 58),
('Couch', 'Central', 27),
('Chair', 'South', 91),
('Chair', 'West', 82),
('Chair', 'North', 37),
('Desk', 'North', 68),
('Couch', 'Central', 54),
('Chair', 'South', 81),
('Desk', 'North', 25),
('Chair', 'North', 46),
('Chair', 'Central', 121),
('Desk', 'South', 85),
('Couch', 'North', 43),
('Desk', 'West', 10),
('Chair', 'North', 5),
('Chair', 'Central', 16),
('Desk', 'South', 9),
('Couch', 'West', 22),
('Couch', 'Central', 59),
('Chair', 'South', 76),
('Chair', 'West', 48),
('Chair', 'North', 19),
('Desk', 'North', 3),
('Couch', 'West', 63),
('Chair', 'South', 81),
('Desk', 'North', 85),
('Chair', 'North', 90),
('Chair', 'Central', 47),
('Desk', 'West', 63),
('Couch', 'North', 28);
 
SELECT * FROM geovindu_sales;
 
---
 
SELECT
product_name,
ISNULL(North,0) North, ISNULL(Central,0) Central, ISNULL(South,0) South, ISNULL(West,0) West
FROM (
  SELECT product_name, store_location, ISNULL(num_sales,0) AS num_sales
  FROM geovindu_sales
) AS alias_for_select
PIVOT
(
  SUM(num_sales)
  FOR store_location IN (North, Central, South, West)
) AS pivot_table;
GO
 
--动态SQL
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
  
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(store_location)
            FROM geovindu_sales
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'');
 PRINT(@cols)
SET @query = 'SELECT product_name, ' + @cols + ' FROM
            (
                SELECT product_name,
                     store_location,
                     num_sales
                FROM geovindu_sales
           ) AS alias_for_select
            PIVOT
            (
                 SUM(num_sales)
                FOR store_location IN (' + @cols + ')
            ) AS pivot_table ';
 
PRINT(@query)
EXECUTE(@query);
GO
 
 drop table geovindu_sales
 go
 
 
 
--SQL Server UNPIVOT
CREATE TABLE geovindu_sales_pivoted (
    product_name VARCHAR(100),
    north INT,
    central INT,
    south INT,
    west INT
);
  
INSERT INTO geovindu_sales_pivoted (product_name, north, central, south, west) VALUES
('Chair', 437, 218, 376, 130),
('Couch', 251, 218, NULL, 143),
('Desk', 226, 120, 136, 134);
 
SELECT product_name, store_location, num_sales
FROM (
  SELECT product_name, north, central, south, west
  FROM geovindu_sales_pivoted
) AS pivoted_table
UNPIVOT
(
    num_sales FOR store_location IN (north, central, south, west)
) AS unpivot_table;
 
 
go
 
 
 drop table geovindu_sales_pivoted
 go

　　

 

 

 

 

?
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
--
 SELECT   RecordAddDate,DATEPART(WW,RecordAddDate)  as  '一年内的第几周'   FROM  dbo.DuView_RedemptionRecordShopName
 go
 
 --第周计算
 DECLARE @sql nvarchar(max)
 DECLARE @s nvarchar(max)   
 Declare @ProjectId int
 declare @GiftsId int
 set @ProjectId=1
 set @GiftsId=2
 SELECT @s=STUFF(( SELECT DISTINCT ',['+CAST(DATEPART(WW,RecordAddDate) as nvarchar)+']'  FROM dbo.DuView_RedemptionRecordShopName FOR XML PATH('') ), 1, 1, '')   
 SET @sql=N';WITH cte
 as (SELECT GiftsName,RecordChangeCode,DATEPART(WW,RecordAddDate) [RecordAddDate],SUM([RecordNumber] ) Val
 FROM dbo.DuView_RedemptionRecordShopName where  ProjectId='+cast(@ProjectId as nvarchar)+' and GiftsId='+cast(@GiftsId as nvarchar)+'
 GROUP BY GiftsName,RecordChangeCode,DATEPART(WW,RecordAddDate))
 SELECT * FROM (SELECT [GiftsName],[RecordChangeCode],[RecordAddDate],Val FROM cte) as t 
 pivot (MAX(val) FOR [RecordAddDate] in ('+@s+')) as p'
 print(@sql)
 EXECUTE sp_executesql  @sql
 go
 
 --geovindu
  DECLARE @sql nvarchar(max)
 DECLARE @s nvarchar(max)   
 Declare @ProjectId int
 declare @GiftsId int
 set @ProjectId=1
 set @GiftsId=1
 SELECT @s=STUFF(( SELECT DISTINCT ',['+CAST(DATEPART(WW,RecordAddDate) as nvarchar)+']'  FROM dbo.DuView_RedemptionRecordShopName FOR XML PATH('') ), 1, 1, '')   
 SET @sql=N';WITH cte
 as (SELECT GiftsName,RecordChangeCode,DATEPART(WW,RecordAddDate) [RecordAddDate],SUM([RecordNumber] ) Val
 FROM dbo.DuView_RedemptionRecordShopName where  ProjectId='+cast(@ProjectId as nvarchar)+' and GiftsId='+cast(@GiftsId as nvarchar)+'
 GROUP BY GiftsName,RecordChangeCode,DATEPART(WW,RecordAddDate))
 SELECT * FROM (SELECT [GiftsName],[RecordChangeCode],[RecordAddDate],Val FROM cte) as t 
 pivot (MAX(val) FOR [RecordAddDate] in ('+@s+')) as p'
 print(@sql)
 EXECUTE sp_executesql  @sql
 go

　　

 

?
1
2
3
4
5
6
;WITH cte
 as (SELECT GiftsName,RecordChangeCode,DATEPART(WW,RecordAddDate) [RecordAddDate],SUM([RecordNumber] ) Val
 FROM dbo.DuView_RedemptionRecordShopName where  ProjectId=1 and GiftsId=1
 GROUP BY GiftsName,RecordChangeCode,DATEPART(WW,RecordAddDate))
 SELECT * FROM (SELECT [GiftsName],[RecordChangeCode],[RecordAddDate],Val FROM cte) as t 
 pivot (MAX(val) FOR [RecordAddDate] in ([32],[33],[34],[35],[36],[37],[40],[41],[42],[43],[44])) as p

　　

Sql Server DATEPART（）函数：

DATEPART（）函数用于返回日期 / 时间的单独部分，比如年、月、日、小时、分钟等等

语法：

      DATEPART(datepart,date)  

      date参数是合法的日期表达式。datepart 参数可以是下列的值：

datepart缩写
年 yy, yyyy
季度 qq, q
月 mm, m
年中的日 dy, y
日 dd, d
周 wk, ww
星期 dw, w
小时 hh
分钟 mi, n
秒 ss, s
毫秒 ms
微妙 mcs
纳秒 ns
posted @   ®Geovin Du Dream Park™  阅读(21)  评论(0)  编辑  收藏  举报
相关博文：
·  sql: SQLite,MySQL,SQL Server
·  python: excel假期时间提取统计
·  sql时间函数
·  MySQL DATE
·  Pandas-2-2-中文文档-八-
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
历史上的今天：
2015-07-07 csharp: DataTable Rename ColumnName and remove Column
2009-07-07 gb2312简繁转换js兼容各种浏览器
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 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 1 2 3 4 5
点击右上角即可分享
微信分享提示