扩展当前查询、计算列 [英] extend current query, calculated columns
问题描述
我的表看起来像这样:
Name date result
A 2012-01-01 1
A 2012-02-01 2
B 2013-01-01 1
...
完整示例:http://sqlfiddle.com/#!3/0226b/1
目前我有一个按人和年份计算行数的工作查询:http://sqlfiddle.com/#!3/0226b/3
At the moment I have a working query that counts the rows by person and year: http://sqlfiddle.com/#!3/0226b/3
这很完美，但我想要的是 2014 年的一些额外信息.我需要计算每个结果有多少行.像这样:
This is perfect, but what I want is some extra information for 2014. i need to count how many rows I have for every result. something like this:
NAME 1 2 3 2014 2013 2012 TOTAL
Person B 4 0 2 6 2 2 10
Person A 2 1 1 4 3 4 11
Person C 1 1 1 3 1 0 4
更好的是我给结果列起一个好名字(1 = 输了，2 = 平局，3 = 赢了):
NAME lost draw won 2014 2013 2012 TOTAL
Person B 4 0 2 6 2 2 10
Person A 2 1 1 4 3 4 11
Person C 1 1 1 3 1 0 4
我尝试添加一些额外的代码，例如:
I tried to add some extra code, like:
select @colsResult
= STUFF((SELECT ',' + QUOTENAME(result)
from list
group by result
order by result
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
结果是:
,[1]
,[2]
,[3]
但是如果我运行整个代码，我会得到一个错误，无效的列名...
But if I run the whole code I get an error, invallid column name...
推荐答案
由于您现在要对两列进行 PIVOT，因此您首先必须对这些列进行逆透视，然后将这些值转换为新列.
Since you have two columns that you now want to PIVOT, you'll first have to unpivot those columns and then convert those values into the new columns.
>
从 SQL Server 2005 开始，您可以使用 CROSS APPLY 对列进行逆透视.基本语法类似于:
Starting in SQL Server 2005, you could use CROSS APPLY to unpivot the columns. The basic syntax will be similar to:
select
name,
new_col,
total
from
(
select name,
dt = year(date),
result,
total = count(*) over(partition by name)
from list
) d
cross apply
(
select 'dt', dt union all
select 'result', result
) c (old_col_name, new_col)
请参阅SQL Fiddle with Demo.此查询为您提供一个名称列表，其中包含新列"，然后是每个名称的总条目.
See SQL Fiddle with Demo. This query gets you a list of names, with the "new columns" and then the Total entries for each name.
| NAME | NEW_COL | TOTAL |
|----------|---------|-------|
| Person A | 2012 | 11 |
| Person A | 1 | 11 |
| Person A | 2012 | 11 |
| Person A | 2 | 11 |
您会看到日期和结果现在都存储在new_col"中.这些值现在将用作新的列名.如果您的列数有限，那么您只需对查询进行硬编码:
You'll see that the dates and the results are now both stored in "new_col". These values will now be used as the new column names. If you have a limited number of columns, then you would simply hard-code the query:
select name, lost = [1],
draw=[2], won = [3],
[2014], [2013], [2012], Total
from
(
select
name,
new_col,
total
from
(
select name,
dt = year(date),
result,
total = count(*) over(partition by name)
from list
) d
cross apply
(
select 'dt', dt union all
select 'result', result
) c (old_col_name, new_col)
) src
pivot
(
count(new_col)
for new_col in([1], [2], [3], [2014], [2013], [2012])
) piv
order by [2014];
现在既然你的年份是动态的，那么你就需要使用动态 sql.但看起来您有 3 个结果并且可能有多年 - 所以我会使用静态/动态 sql 的组合来使这更容易:
Now since your years are dynamic, then you'll need to use dynamic sql. But it appears that you have 3 results and potentially multiple years - so I'd use a combination of static/dynamic sql to make this easier:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@orderby nvarchar(max)
select @cols
= STUFF((SELECT ',' + QUOTENAME(year(date))
from list
group by year(date)
order by year(date) desc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @orderby = 'ORDER BY ['+cast(year(getdate()) as varchar(4)) + '] desc'
set @query = 'SELECT name, lost = [1],
draw=[2], won = [3],' + @cols + ', Total
from
(
select
name,
new_col,
total
from
(
select name,
dt = year(date),
result,
total = count(*) over(partition by name)
from list
) d
cross apply
(
select ''dt'', dt union all
select ''result'', result
) c (old_col_name, new_col)
) x
pivot
(
count(new_col)
for new_col in ([1], [2], [3],' + @cols + ')
) p '+ @orderby
exec sp_executesql @query;
参见SQL Fiddle with Demo.这给出了一个结果:
See SQL Fiddle with Demo. This gives a result:
| NAME | LOST | DRAW | WON | 2014 | 2013 | 2012 | TOTAL |
|----------|------|------|-----|------|------|------|-------|
| Person B | 7 | 1 | 2 | 6 | 2 | 2 | 10 |
| Person A | 5 | 3 | 3 | 4 | 3 | 4 | 11 |
| Person C | 2 | 1 | 1 | 3 | 1 | 0 | 4 |
如果您只想过滤当前年份的结果列，那么您可以通过多种方式执行此过滤，但最简单的方法是在逆透视中包含过滤器.硬编码版本将是:
If you want to only filter the result columns for the current year, then you can perform this filtering a variety of ways but the easiest you be to include a filter in the unpivot. The hard-coded version would be:
select name, lost = [1],
draw=[2], won = [3],
[2014], [2013], [2012], Total
from
(
select
name,
new_col,
total
from
(
select name,
dt = year(date),
result,
total = count(*) over(partition by name)
from list
) d
cross apply
(
select 'dt', dt union all
select 'result', case when dt = 2014 then result end
) c (old_col_name, new_col)
) src
pivot
(
count(new_col)
for new_col in([1], [2], [3], [2014], [2013], [2012])
) piv
order by [2014] desc;
请参阅SQL Fiddle with Demo.那么动态sql版本将是:
See SQL Fiddle with Demo. Then the dynamic sql version would be:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@orderby nvarchar(max),
@currentYear varchar(4)
select @currentYear = cast(year(getdate()) as varchar(4))
select @cols
= STUFF((SELECT ',' + QUOTENAME(year(date))
from list
group by year(date)
order by year(date) desc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @orderby = 'ORDER BY ['+ @currentYear + '] desc'
set @query = 'SELECT name, lost = [1],
draw=[2], won = [3],' + @cols + ', Total
from
(
select
name,
new_col,
total
from
(
select name,
dt = year(date),
result,
total = count(*) over(partition by name)
from list
) d
cross apply
(
select ''dt'', dt union all
select ''result'', case when dt = '+@currentYear+' then result end
) c (old_col_name, new_col)
) x
pivot
(
count(new_col)
for new_col in ([1], [2], [3],' + @cols + ')
) p '+ @orderby
exec sp_executesql @query;
请参阅SQL Fiddle with Demo.这个版本会给出结果:
See SQL Fiddle with Demo. This version will give a result:
| NAME | LOST | DRAW | WON | 2014 | 2013 | 2012 | TOTAL |
|----------|------|------|-----|------|------|------|-------|
| Person B | 4 | 0 | 2 | 6 | 2 | 2 | 10 |
| Person A | 2 | 1 | 1 | 4 | 3 | 4 | 11 |
| Person C | 1 | 1 | 1 | 3 | 1 | 0 | 4 |
这篇关于扩展当前查询、计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
