我们如何以所需顺序将周数显示为列 [英] How we can show week numbers as columns in the required order

问题描述

Hi Team，



对于年度报告，我试图按以下顺序显示Weeknumbers为列名



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 6 7 8 9





我尝试了以下查询，但订单与上述不匹配

Hi Team,

For Yearly report i am trying to show the Weeknumbers as Column names in the following order

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 6 7 8 9


I tried the following query but the order is not matching as above

Create table #myreport (rpt_dt datetime)

Insert into #myreport
Select getdate()-30
union
Select getdate()-60
union

Select getdate()-73

union

Select getdate()-90

DECLARE @cols AS VARCHAR(8000),
    @query  AS VARCHAR(8000)

SELECT @cols = STUFF((SELECT ',' +   QUOTENAME(YrWeek)
                    FROM (SELECT DISTINCT CAST(DATEPART(week,rpt_dt)AS VARCHAR(2))'YrWeek'
                          FROM #myreport
                          WHERE rpt_dt > DATEADD(YEAR,-1,GETDATE()))sub
                   ORDER BY LEFT(YrWeek,4) DESC,RIGHT(YrWeek,2)DESC
                    FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)')
        ,1,1,'')

SET @query = 'SELECT * FROM
                (
                SELECT CAST(DATEPART(week,rpt_dt)AS VARCHAR(2)) YrWeek, COUNT(*)CT
                FROM #myreport
                GROUP BY CAST(DATEPART(week,rpt_dt)AS VARCHAR(2))
                ) AS T1
                PIVOT (SUM(CT) FOR YrWeek IN ('+@cols+')) AS T2

'
EXEC(@query)

推荐答案

声明@StartDate smalldatetime ='1-Jan-14'



DECLARE @cols AS VARCHAR（max），

@query AS VARCHAR（最大值）



创建表#temp（WeekDays varchar（2），WeekDates smalldatetime）



;有cte（WeekDays，WeekDates）

as

（

select 1，@ StartDate

union all

选择WeekDays + 1，DATE ADD（周，周日+ 1，@ StartDate）

来自cte

其中WeekDays< 53

）

插入#temp

选择WeekDays，来自cte的WeekDates



选择@cols = stuff（（select']，['+ WeekDays

来自#temp

按周末排序

for xml path（''）），1,2，''）+']'



设置@query ='SELECT'+ @cols + N'来自

（

选择WeekDays，WeekDates

来自#temp

）x

pivot

（

max（WeekDates） ）

for WeekDays in（'+ @cols + N'）

）p'



exec（ @query）



drop table #temp

declare @StartDate smalldatetime ='1-Jan-14'

DECLARE @cols AS VARCHAR(max),
@query AS VARCHAR(max)

create table #temp (WeekDays varchar(2),WeekDates smalldatetime)

;with cte (WeekDays,WeekDates)
as
(
select 1,@StartDate
union all
select WeekDays+1,DATEADD(week,WeekDays+1,@StartDate)
from cte
where WeekDays<53
)
insert into #temp
select WeekDays,WeekDates from cte

select @cols =stuff((select '],['+WeekDays
from #temp
order by WeekDays
for xml path('')),1,2,'')+']'

set @query = 'SELECT ' + @cols + N' from
(
select WeekDays, WeekDates
from #temp
) x
pivot
(
max(WeekDates)
for WeekDays in (' + @cols + N')
) p '

exec (@query)

drop table #temp

这篇关于我们如何以所需顺序将周数显示为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！