sql server语言 - 我如何从过去一年的每个月的数据在t-sql和每个月应该在不同的列

Question

我想要过去1年的数据，例如今天是02-05-2014，我需要2013年5月，2013年6月的数据。直到2014年4月作为单独的专栏。如果有人能用t-sql帮助我，那将对我很有帮助。由于

这里我需要数据。下面是我的专栏

created date
------------
02-05-2013
16-05-2013
05-06-2013
22-07-2013
01-08-2013
09-08-2013
02-09-2013
03-10-2013
19-11-2013
11-12-2013
03-01-2014
29-02-2014
15-03-2014
19-04-2014

我希望结果为

May 2013    June 2013    July 2013    August 2013 till   April 2014
--------    ---------    ---------    -----------        ----------
02-05-2013 05-06-2013   22-07-2013   01-08-2013          19-04-2014
16-05-2013                           09-08-2013

我还想动态地设置Columns 这对我的查询非常重要

Answer 1

虽然每个人都很快建议使用PIVOT，但这在这里确实行不通，因为PIVOT需要将每个列聚合为该月份和年份的一个(max, min, whatever)日期。

我给这个问题一些关注，因为它实际上是一个有趣的挑战。现实情况是，这最好由一些报告工具来完成，比如SSRS，因为您的输出实际上是一个时间轴报告。

由于对动态列等的需求，我真的不知道如果没有变量和一些动态sql如何做到这一点，所以假设这是可以接受的，测试下面的代码并将输出您所描述的内容。它实际上创建了12个CTE表，每个表包含一个月和一年的日期(从运行sql的月份和年份向后计数)。然后，只需使用所有表的FULL OUTER JOIN就可以创建报告。但是，如果只对12个表进行全联接，则每列的值将随机分布在几行中，中间有许多空。为了将每列中的日期排列在顶部，必须添加一个带有序号的基表，以便每个月/年表可以连接。序列号生成到任何给定月/年的最大日期数。(注意:对于底数表，LEFT OUTER JOIN也足够了…)

假设表名是dbo.MyTable，日期列是CreatedDate:

DECLARE @cteSql nvarchar(MAX) = '';
DECLARE @tblSql nvarchar(MAX) = '';
DECLARE @frmSql nvarchar(MAX) = '';
DECLARE @colNm  varchar(10);
DECLARE @tblNm  varchar(3);
DECLARE @i int = 0;
/* today's date */
DECLARE @td date = GETDATE();
/* max number of dates per yr/mo */
DECLARE @maxItems int = (SELECT MAX(CNT) FROM (SELECT COUNT(*) AS CNT FROM dbo.MyTable GROUP BY YEAR(CreatedDate), MONTH(CreatedDate)) T)
/* a table of sequential numbers up to the max per yr/mo; this is so the full outer join is laid out neatly */
SET @cteSql = 'WITH T(id) AS( SELECT id = 1 UNION ALL SELECT id + 1 FROM T WHERE id + 1 <= ' + CAST(@maxItems AS varchar(16)) + ')';
/* count down from current date to past 12 months */
WHILE @i > -12
BEGIN
  /* a simple name for each CTE: T0, T1, T2 etc */
  SET @tblNm = 'T' + CAST((@i*-1) AS varchar(2));
  /* rpt column names; [Jan 2014], [Feb 2014] etc */
  SET @colNm = '[' + RIGHT(CONVERT(varchar(11), DATEADD(m, @i, @td), 106),8) + ']';
  /* each CTE contains a sequential id and the dates belonging to that month and yr */
  SET @cteSql += ', ' + @tblNm + '(id, ' + @colNm + ')'
               + ' AS (SELECT ROW_NUMBER() OVER(ORDER BY CreatedDate) AS id, CreatedDate FROM dbo.MyTable WHERE YEAR(CreatedDate) = ' + CAST(YEAR(DATEADD(m, @i, @td)) AS varchar(4))
               + ' AND MONTH(CreatedDate) = ' + CAST(MONTH(DATEADD(m, @i, @td)) AS varchar(2)) + ')';
  /* this will eventually be the SELECT statement for the report...just the month columns, not the id */
  SET @tblSql = ', ' + @colNm + @tblSql;
  /* concatenate all the columns using FULL OUTER JOIN with the first table of simple sequential numbers as the driver */
  SET @frmSql += ' FULL OUTER JOIN ' + @tblNm + ' ON T.id = ' + @tblNm + '.id ';
  SET @i -= 1;
END
/* put all the sql together */
SET @tblSql = @cteSql + ' SELECT' + STUFF(@tblSql, 1, 1, '') + ' FROM T ' + @frmSql
/*  view the generated sql */
-- SELECT @tblSql AS X
/*  this should generate the report you described above, showing the last 12 months from whatever date you run it */
EXECUTE (@tblSql)

输出:

Jun 2013   Jul 2013   Aug 2013   Sep 2013   Oct 2013   Nov 2013   Dec 2013   Jan 2014   Feb 2014   Mar 2014   Apr 2014   May 2014
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2013-06-05 2013-07-22 2013-08-01 2013-09-02 2013-10-03 2013-11-19 2013-12-11 2014-01-03 2014-02-28 2014-03-15 2014-04-19 NULL
2013-06-07 NULL       2013-08-09 NULL       NULL       NULL       NULL       NULL       NULL       NULL       NULL       NULL
NULL       NULL       2013-08-10 NULL       NULL       NULL       NULL       NULL       NULL       NULL       NULL       NULL

结果，生成的sql在概念上与@Hogan建议的相似，尽管我一开始没有意识到这一点。它只是添加了动态命名加上按年/月而不是按月的分隔

Answer 2

这里有一种不需要动态枢轴的方法。我只在2013年这样做了，您可以看到需要添加更多列:

(working fiddle: http://sqlfiddle.com/#!6/d9797/1)

with nums as
(
  select [create date],
         MONTH([create date]) as M,
         ROW_NUMBER() OVER (ORDER BY [create date] ASC) as RN
  from table1       
  where  MONTH([create date]) =1
  union all      
  select [create date],
         MONTH([create date]) as M,
         ROW_NUMBER() OVER (ORDER BY [create date] ASC) as RN
  from table1       
  where  MONTH([create date]) =2
  union all      
  select [create date],
         MONTH([create date]) as M,
         ROW_NUMBER() OVER (ORDER BY [create date] ASC) as RN
  from table1       
  where  MONTH([create date]) =3
  union all      
  select [create date],
         MONTH([create date]) as M,
         ROW_NUMBER() OVER (ORDER BY [create date] ASC) as RN
  from table1       
  where  MONTH([create date]) =4
  union all      
  select [create date],
         MONTH([create date]) as M,
         ROW_NUMBER() OVER (ORDER BY [create date] ASC) as RN
  from table1       
  where  MONTH([create date]) =5
  union all      
  select [create date],
         MONTH([create date]) as M,
         ROW_NUMBER() OVER (ORDER BY [create date] ASC) as RN
  from table1       
  where  MONTH([create date]) =6
  union all      
  select [create date],
         MONTH([create date]) as M,
         ROW_NUMBER() OVER (ORDER BY [create date] ASC) as RN
  from table1       
  where  MONTH([create date]) =7
  union all      
  select [create date],
         MONTH([create date]) as M,
         ROW_NUMBER() OVER (ORDER BY [create date] ASC) as RN
  from table1       
  where  MONTH([create date]) =8
  union all      
  select [create date],
         MONTH([create date]) as M,
         ROW_NUMBER() OVER (ORDER BY [create date] ASC) as RN
  from table1       
  where  MONTH([create date]) =9
  union all      
    select [create date],
         MONTH([create date]) as M,
         ROW_NUMBER() OVER (ORDER BY [create date] ASC) as RN
  from table1       
  where  MONTH([create date]) =10
  union all      
  select [create date],
         MONTH([create date]) as M,
         ROW_NUMBER() OVER (ORDER BY [create date] ASC) as RN
  from table1       
  where  MONTH([create date]) =11
  union all      
  select [create date],
         MONTH([create date]) as M,
         ROW_NUMBER() OVER (ORDER BY [create date] ASC) as RN
  from table1       
  where  MONTH([create date]) =12
),maxrn as
(
  select MAX(RN) as maxnum from nums
), rowNumbers as
(
   select 1 as RN
   union all
   select RN+1 AS RN
   from rowNumbers
   where RN < (select maxnum from maxrn)
)
SELECT 
   nJan.[create date] as [Jan 2013],
   nFeb.[create date] as [Feb 2013],
   nMar.[create date] as [Mar 2013],
   nApr.[create date] as [Apr 2013],
   nMay.[create date] as [May 2013],
   nJun.[create date] as [Jun 2013],
   nJul.[create date] as [Jul 2013],
   nAug.[create date] as [Aug 2013],
   nSep.[create date] as [Sep 2013],
   nOct.[create date] as [Oct 2013],
   nNov.[create date] as [Nov 2013],
   nDec.[create date] as [Dec 2013]
FROM rowNumbers n
LEFT JOIN nums nJan ON n.RN = nJan.RN and nJan.M = 1
LEFT JOIN nums nFeb ON n.RN = nFeb.RN and nFeb.M = 2
LEFT JOIN nums nMar ON n.RN = nMar.RN and nMar.M = 3
LEFT JOIN nums nApr ON n.RN = nApr.RN and nApr.M = 4
LEFT JOIN nums nMay ON n.RN = nMay.RN and nMay.M = 5
LEFT JOIN nums nJun ON n.RN = nJun.RN and nJun.M = 6
LEFT JOIN nums nJul ON n.RN = nJul.RN and nJul.M = 7
LEFT JOIN nums nAug ON n.RN = nAug.RN and nAug.M = 8
LEFT JOIN nums nSep ON n.RN = nSep.RN and nSep.M = 9
LEFT JOIN nums nOct ON n.RN = nOct.RN and nOct.M = 10
LEFT JOIN nums nNov ON n.RN = nNov.RN and nNov.M = 11
LEFT JOIN nums nDec ON n.RN = nDec.RN and nDec.M = 12
ORDER BY n.RN ASC

Answer 3

哎呀，这已经有答案了T_T，
但如果你有空闲时间，可以试试这个。

在这种情况下使用很多东西:

1. CTE
4. 子串
   
   

声明@xCol nvarchar(max);

--Begin create column
with cte as
(
    select 1 as id,
        aa.month as mm, aa.Year
        --,aa.xmonth as mmm
        , aa.ord
    from
    (
    select 
        xdate
        ,Year(xdate) as Year
        --,month(xdate) as xmonth
        , substring(convert(nvarchar(max),xdate,106),3,len(convert(nvarchar(max),xdate,106))) as month
        ,convert(nvarchar(6), xdate,112) as ord
    from tempData vv
    ) aa
    group by aa.ord, aa.month,aa.Year
    --order by aa.Year
)
select 
    distinct 
    --c.id,
    @xCol = stuff(
        (
            select ',' + c2.mm
            from cte c2
            where c.id = c2.id
            for xml path ('')
        ),1,0,''
    )
from cte c
;
set @xCol= SUBSTRING(@xCol,2, len(@xCol))
select @xCol = '[' + replace(@xCol,',','],[') + ']'
--select @xCol as '@columns', len(@xCol)
--END CREATE COLUMNS
--CREATE INPUT STRING
Declare @tbl_inputstr table
(
  id int,
  xstr nvarchar(max)
)
;
    with cte as
    (
        select 
            a.xdate, a.month
            ,row_number() over(partition by a.month order by a.xdate) as xrow
        from
        (
        select 
                xdate
                ,Year(xdate) as Year
                ,month(xdate) as xmonth
                ,convert(nvarchar(6),xdate,112) as month2
                , substring(convert(nvarchar(max),xdate,106),3,len(convert(nvarchar(max),xdate,106))) as month
        from tempData 
        ) a
    )
    insert into @tbl_inputstr(id,xstr)
    select  distinct c.xrow as id,
        ' Insert into @tempData (' + substring(stuff(
            (
                select ',[' + cast(c2.month as nvarchar(max)) + ']'
                from cte c2
                where c.xrow = c2.xrow
                for xml path ('')
            ),1,0,''
        ),2,len(stuff(
            (
                select ',[' + cast(c2.month as nvarchar(max)) + ']'
                from cte c2
                where c.xrow = c2.xrow
                for xml path ('')
            ),1,0,''
        ))) + ')'
        +' Values(' + Substring(stuff(
            (
                select ',''' + cast(c2.xdate as nvarchar(max)) + ''''
                from cte c2
                where c.xrow = c2.xrow
                for xml path ('')
            ),1,0,''
        ),2,len(stuff(
            (
                select ',''' + cast(c2.xdate as nvarchar(max)) + ''''
                from cte c2
                where c.xrow = c2.xrow
                for xml path ('')
            ),1,0,''
        ))) + ')'
    from cte c
    order by c.xrow;
select * from @tbl_inputstr
Declare @inputStr nvarchar(max)
select @inputStr = 
    substring(stuff
    (
        (
            select ';' + xstr
            from @tbl_inputstr
            for xml path('')
        ),1,0,''
    ),2, len(stuff
    (
        (
            select ';' + xstr
            from @tbl_inputstr
            for xml path('')
        ),1,0,''
    ))
    ) 

select @inputStr= 'Declare @tempData Table (' +replace(@xCol,']', '] nvarchar(max)') + ');' + @inputStr 
    + '; select ' + @xCol 
    + ' from @tempData'

exec(@inputStr)
--END INPUT STRING