与SQL查询相关的问题 [英] Question related to SQL query

问题描述

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

推荐答案

起初我以为只是介绍一个ORDER BY但不幸的是我无法让它工作即使我更改 @Date 的派生以使用CTE，CSV仍按自然顺序出现。



所以我使用了如下的暴力技术

At first I thought "just introduce an ORDER BY" but unfortunately I could not get that to work, even if I changed the derivation of @Date to use a CTE the CSV still came out in natural order.

So I used a brute-force technique as follows

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查询相关的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！