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

--行列转换
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".

 

 

 

 

 

 

 

 

 

-- --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

　　

 

 

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

　　

 

 

 

 

--
 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

　　

 

;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