问题描述
CREATE PROCEDURE [dbo].[DOSAPiv]
AS
BEGIN
DECLARE @date VARCHAR(MAX),
@Sql VARCHAR(Max)
SELECT @Date = stuff((select distinct ',['+ CONVERT(VARCHAR,Date,106) + ']' from dosa for xml path('')),1,1,'')
SET @Sql = ';WITH T AS(SELECT emp_name,Emp_Code, status, Date FROM dosa)SELECT * FROM T PIVOT(MAX(Status) FOR Date IN(' + @date + ')) As StatusSt'
EXEC(@Sql)
先生这是我的查询
i我正在使用此查询显示月份的关注度报告我的关注日期日期从25到26开始,例如25月4日到26日我可以插入比如25,26,27,28,29,30,1,2
但输出的问题显示为1,2 ,25,26,27,28,29,20
i想要输出25,26,27,28,29,30,1,2 ............ ........ 26这个表格
我尝试了什么:
sir this is my query
i am using this query for displaying attendence report of month my attendence date date start from 25 to 26 for example 25 april to 26 may and i inserting like 25, 26 ,27,28,29,30,1,2
but problem is output showing 1,2,25,26,27,28,29,20
i want output in 25, 26 ,27,28,29,30,1,2....................26 this form
What I have tried:
CREATE PROCEDURE [dbo].[DOSAPiv]
AS
BEGIN
DECLARE @date VARCHAR(MAX),
@Sql VARCHAR(Max)
SELECT @Date = stuff((select distinct ',['+ CONVERT(VARCHAR,Date,106) + ']' from dosa for xml path('')),1,1,'')
SET @Sql = ';WITH T AS(SELECT emp_name,Emp_Code, status, Date FROM dosa)SELECT * FROM T PIVOT(MAX(Status) FOR Date IN(' + @date + ')) As StatusSt'
EXEC(@Sql)
先生这是我的查询
i我正在使用此查询进行显示m。的关注报告我的注意日期日期从25到26开始,例如25月4日到26日,我插入像25,26,27,28,29,30,1,2
但输出问题显示1 ,2,25,26,27,28,29,20
i想要输出25,26,27,28,29,30,1,2 .......... .......... 26这个表格
sir this is my query
i am using this query for displaying attendence report of month my attendence date date start from 25 to 26 for example 25 april to 26 may and i inserting like 25, 26 ,27,28,29,30,1,2
but problem is output showing 1,2,25,26,27,28,29,20
i want output in 25, 26 ,27,28,29,30,1,2....................26 this form
推荐答案
DECLARE @date1 VARCHAR(MAX),
@date2 VARCHAR(MAX),
@Sql VARCHAR(Max)
SELECT @date1 = stuff((select distinct ',['+ CONVERT(VARCHAR,Date,106) + ']' from dosa where datepart(dd, date) >= 25 for xml path('')),1,1,'')
SELECT @date2 = stuff((select distinct ',['+ CONVERT(VARCHAR,Date,106) + ']' from dosa where datepart(dd, date) < 25 for xml path('')),1,1,'')
SET @Sql = ';WITH T AS(SELECT emp_name,Emp_Code, status, Date FROM dosa)SELECT * FROM T PIVOT(MAX(Status) FOR Date IN(' + @date1 + ',' + @date2 + ')) As StatusSt'
换句话说,我分别导出列列表的每个部分,然后在 @sql
这篇关于与SQL查询相关的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!