SQL Pivot 行转列

最新推荐文章于 2024-01-18 14:21:41 发布

KingCruel

最新推荐文章于 2024-01-18 14:21:41 发布

阅读量685

点赞数

分类专栏： SQL Server

本文链接：https://blog.csdn.net/KingCruel/article/details/51038399

版权

SQL Server 专栏收录该内容

35 篇文章 1 订阅

订阅专栏

pivot 行转列
unpivot 列转行

--CREATE TABLE [dbo].[Pivot_Test](
--	[UserName] [nvarchar](50) NOT NULL,
--	[Gains] [decimal](18, 2) NOT NULL,
--	[GainsType] [nvarchar](50) NOT NULL
--) ON [PRIMARY]

GO

--INSERT INTO Pivot_Test(UserName,Gains,GainsType) values('a','1','i1')

--INSERT INTO Pivot_Test(UserName,Gains,GainsType) values('a','1','i2')

--INSERT INTO Pivot_Test(UserName,Gains,GainsType) values('a','1','i3')

--INSERT INTO Pivot_Test(UserName,Gains,GainsType) values('b','1','i1')

--INSERT INTO Pivot_Test(UserName,Gains,GainsType) values('b','1','i2')

--INSERT INTO Pivot_Test(UserName,Gains,GainsType) values('c','1','i1')

--INSERT INTO Pivot_Test(UserName,Gains,GainsType) values('c','1','i2')

--INSERT INTO Pivot_Test(UserName,Gains,GainsType) values('d','1','i1')

--INSERT INTO Pivot_Test(UserName,Gains,GainsType) values('d','1','i2')

select * from Pivot_Test

DECLARE @sql NVARCHAR(4000)
SELECT  @sql = ISNULL(@sql + ',', '') + GainsType
FROM Pivot_Test
GROUP BY GainsType
PRINT @sql
IF ( CHARINDEX(@sql, ',') = 0 )
    BEGIN
        EXEC ('select * from Pivot_Test a pivot (sum(Gains) for GainsType in (' + @sql + ')) b')--可以使用
    END

--------------------------------------------------------------------------------------------------------------
--CREATE TABLE [dbo].[Pivot_TestNew](
--	[UserName] [nvarchar](50) NOT NULL,
--	[Gains] [decimal](18, 2) NOT NULL,
--	[GainsType] [nvarchar](50) NOT NULL
--) ON [PRIMARY]

--INSERT INTO Pivot_TestNew(UserName,Gains,GainsType) values('a','1','i1 1')

--INSERT INTO Pivot_TestNew(UserName,Gains,GainsType) values('a','1','i2 1')

--INSERT INTO Pivot_TestNew(UserName,Gains,GainsType) values('a','1','i3 1')

--INSERT INTO Pivot_TestNew(UserName,Gains,GainsType) values('b','1','i1 1')

--INSERT INTO Pivot_TestNew(UserName,Gains,GainsType) values('b','1','i2 1')

--INSERT INTO Pivot_TestNew(UserName,Gains,GainsType) values('c','1','i1 1')

--INSERT INTO Pivot_TestNew(UserName,Gains,GainsType) values('c','1','i2 1')

--INSERT INTO Pivot_TestNew(UserName,Gains,GainsType) values('d','1','i1 1')

--INSERT INTO Pivot_TestNew(UserName,Gains,GainsType) values('d','1','i2 1')

select * from Pivot_TestNew

DECLARE @sql2 NVARCHAR(4000)
SELECT  @sql2 = ISNULL(@sql2 + ',', '') + REPLACE(GainsType,' ','_') FROM (select * FROM Pivot_TestNew
group by GainsType,Gains,UserName
) tt
GROUP BY GainsType
PRINT @sql2
IF ( CHARINDEX(@sql2, ',') = 0 )
    BEGIN
        EXEC ('select * from(
		                     select UserName,ISNULL(Gains, ''0'') AS Gains,REPLACE(GainsType,'' '',''_'') AS GainsType 
							 FROM Pivot_TestNew 
							 group by GainsType,Gains,UserName
							 ) tt pivot (sum(Gains) for GainsType in (' + @sql2 + ')) b')--可以使用
    END

--REPLACE(GainsType,'' '',''_'')
--ISNULL(REPLACE(GainsType,'' '',''_''),0)
--REPLACE(ISNULL(GainsType,0),'' '',''_'')
----------------------------如果有空格替换(REPLACE)空格,有NULL值(ISNULL)替换NULL值----------------------------
EXEC('SELECT REPLACE(UserName,'' '',''_'') AS UserName,ISNULL(Gains, ''0'') AS Gains,GainsType FROM Pivot_Test')

日期排班

CREATE TABLE [dbo].[base_nurse_on_duty](
	[nurse_on_duty_code] [int] IDENTITY(1,1) NOT NULL,
	[department_code] [int] NULL,
	[nurse_code] [nvarchar](50) NOT NULL,
	[nurse_title] [varchar](50) NULL,
	[real_name] [nvarchar](50) NOT NULL,
	[bed_code] [int] NULL,
	[work_in_shifts] [nvarchar](50) NULL,
	[duty_date] [datetime] NULL,
	[week] [nvarchar](50) NULL,
	[modify_time] [datetime] NOT NULL,
 CONSTRAINT [PK_base_nurse_on_duty] PRIMARY KEY CLUSTERED 
(
	[nurse_on_duty_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[base_nurse_on_duty] ON 

GO
INSERT [dbo].[base_nurse_on_duty] ([nurse_on_duty_code], [department_code], [nurse_code], [nurse_title], [real_name], [bed_code], [work_in_shifts], [duty_date], [week], [modify_time]) VALUES (1, 1, N'7', N'护士', N'张琪', 1, N'白班', CAST(0x0000AB5800000000 AS DateTime), NULL, CAST(0x0000AAC300000000 AS DateTime))
GO
INSERT [dbo].[base_nurse_on_duty] ([nurse_on_duty_code], [department_code], [nurse_code], [nurse_title], [real_name], [bed_code], [work_in_shifts], [duty_date], [week], [modify_time]) VALUES (2, 1, N'2', N'护师', N'王素珍', 2, N'大夜班', CAST(0x0000AB5800000000 AS DateTime), NULL, CAST(0x0000AAC300000000 AS DateTime))
GO
INSERT [dbo].[base_nurse_on_duty] ([nurse_on_duty_code], [department_code], [nurse_code], [nurse_title], [real_name], [bed_code], [work_in_shifts], [duty_date], [week], [modify_time]) VALUES (3, 1, N'3', N'主管护师', N'陈玉林', 3, N'小夜班', CAST(0x0000AB5800000000 AS DateTime), NULL, CAST(0x0000AAC300000000 AS DateTime))
GO
INSERT [dbo].[base_nurse_on_duty] ([nurse_on_duty_code], [department_code], [nurse_code], [nurse_title], [real_name], [bed_code], [work_in_shifts], [duty_date], [week], [modify_time]) VALUES (4, 1, N'4', N'副主任护师', N'宋佳', 1, N'白班', CAST(0x0000AB5800000000 AS DateTime), NULL, CAST(0x0000AAC300000000 AS DateTime))
GO
INSERT [dbo].[base_nurse_on_duty] ([nurse_on_duty_code], [department_code], [nurse_code], [nurse_title], [real_name], [bed_code], [work_in_shifts], [duty_date], [week], [modify_time]) VALUES (5, 1, N'5', N'主任护师', N'刘敏君', 2, N'大夜班', CAST(0x0000AB5800000000 AS DateTime), NULL, CAST(0x0000AAC300000000 AS DateTime))
GO
INSERT [dbo].[base_nurse_on_duty] ([nurse_on_duty_code], [department_code], [nurse_code], [nurse_title], [real_name], [bed_code], [work_in_shifts], [duty_date], [week], [modify_time]) VALUES (6, 1, N'6', N'护士', N'杨羽', 3, N'小夜班', CAST(0x0000AB5800000000 AS DateTime), NULL, CAST(0x0000AAC300000000 AS DateTime))
GO
INSERT [dbo].[base_nurse_on_duty] ([nurse_on_duty_code], [department_code], [nurse_code], [nurse_title], [real_name], [bed_code], [work_in_shifts], [duty_date], [week], [modify_time]) VALUES (7, 1, N'7', N'护士', N'张琪', 1, N'白班', CAST(0x0000AB5900000000 AS DateTime), NULL, CAST(0x0000AAC300000000 AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[base_nurse_on_duty] OFF
GO


-------动态创建列名-----
DECLARE @sql2 NVARCHAR(4000)
SELECT  @sql2 = ISNULL(@sql2 + ',', '') + '['+convert(varchar(100), duty_date, 112)+']' FROM (select duty_date FROM base_nurse_on_duty
group by duty_date,work_in_shifts,real_name
) tt
GROUP BY duty_date
PRINT @sql2
IF ( CHARINDEX(@sql2, ',') = 0 )
    BEGIN
        EXEC ('select * from(
		                     select real_name,ISNULL(work_in_shifts,''0'') AS Gains,convert(varchar(100),duty_date, 112) AS GainsType 
							 FROM base_nurse_on_duty 
							 group by duty_date,work_in_shifts,real_name
							 ) tt pivot (max(Gains) for GainsType in (' + @sql2 + ')) b')--可以使用
    END


-----------------直接执行语句------------------------
select * from(
 select real_name,ISNULL(work_in_shifts,'0') AS Gains,convert(varchar(100),duty_date, 112) AS GainsType 
 FROM base_nurse_on_duty 
 group by duty_date,work_in_shifts,real_name
 ) tt pivot (max(Gains) for GainsType in ([20200205],[20200206],[20202027])) b

时间范围排班表

DECLARE @sql2 NVARCHAR(4000)
DECLARE @days INT, 
@date_start DATETIME = '2020-02-10', 
@date_end DATETIME = '2020-02-20'  
SET @days = DATEDIFF(DAY, @DATE_START, @DATE_END); 

SELECT @sql2 = ISNULL(@sql2 + ',', '') + '['+convert(varchar(100), duty_date, 112)+']' FROM (
SELECT convert(varchar(10), DATEADD(dd, number, @DATE_START),112) AS duty_date 
FROM master.dbo.spt_values  as spt
WHERE type = 'p' AND number <= @days
) tt
GROUP BY duty_date
PRINT @sql2
IF ( CHARINDEX(@sql2, ',') = 0 )
    BEGIN
        EXEC ('select * from(
		                     select real_name,ISNULL(work_in_shifts,''0'') AS Gains,convert(varchar(100),duty_date, 112) AS GainsType 
							 FROM base_nurse_on_duty 
							 group by duty_date,work_in_shifts,real_name
							 ) tt pivot (max(Gains) for GainsType in (' + @sql2 + ')) b')--可以使用
    END

时间范围查询【日期+星期】

DECLARE @sql2 NVARCHAR(4000)
DECLARE @days INT, 
@date_start DATETIME = '2020-02-10', 
@date_end DATETIME = '2020-02-16'  
SET @days = DATEDIFF(DAY, @DATE_START, @DATE_END); 

SELECT @sql2 = ISNULL(@sql2 + ',', '') + '['+convert(varchar(100), duty_date, 112)+' '+datename(weekday,duty_date)+']' FROM (
SELECT convert(varchar(10), DATEADD(dd, number, @DATE_START),112) AS duty_date 
FROM master.dbo.spt_values  as spt
WHERE type = 'p' AND number <= @days
) tt
GROUP BY duty_date
PRINT @sql2
IF ( CHARINDEX(@sql2, ',') = 0 )
    BEGIN
        EXEC ('select * from(
		                     select real_name,ISNULL(work_in_shifts,''0'') AS Gains,convert(varchar(100),duty_date, 112)+'' ''+datename(weekday,duty_date) AS GainsType 
							 FROM base_nurse_on_duty 
							 group by duty_date,work_in_shifts,real_name
							 ) tt pivot (max(Gains) for GainsType in ('+@sql2+')) b')--可以使用
    END
	go

*
case

select department_code,department_name,queue_state from trans_queue

select department_code
,department_name
,count( case when queue_state = 1 then 1 else null end ) as '在队'
,count( case when queue_state = 0 then 0 else null end ) as '离队' 
FROM trans_queue 
where department_code is not null
group by department_code,department_name

*

*
*
*
*
*

KingCruel

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL Pivot 行转列

--CREATE TABLE [dbo].[Pivot_Test](-- [UserName] [nvarchar](50) NOT NULL,-- [Gains] [decimal](18, 2) NOT NULL,-- [GainsType] [nvarchar](50) NOT NULL--) ON [PRIMARY]GO--INSERT INTO Pivot_Tes...
复制链接

扫一扫

专栏目录