sql - 如何在我的 PIVOT 值中为每个 my 包含一个额外的非聚合列?
问题描述
我有以下代码片段,它给了我下面的当前结果。我正在尝试为每个透视值添加一个附加列,以便包含我的每个 siteuserid / tagname 组合的lastview数据(请参阅预期结果)。由于本专栏不是聚合,我不相信额外的支点会有所帮助。我尝试了多种添加lastview的方法,但它总是导致额外的行而不是所需的输出。
create table #taghits (userid int, email varchar(20), tagname varchar(20), hits int, lastview date)
insert into #taghits select 1, 'email1@here.com', 'tag1', 3, '2020-03-24';
insert into #taghits select 2, 'email2@here.com', 'tag1', 1, '2020-03-17';
insert into #taghits select 2, 'email2@here.com', 'tag2', 1, '2020-03-18';
insert into #taghits select 3, 'email3@here.com', 'tag1', 2, '2020-03-25';
insert into #taghits select 3, 'email3@here.com', 'tag2', 5, '2020-03-28';
select * from #taghits;
DECLARE @Columns3 as NVARCHAR(MAX)
SELECT @Columns3 = ISNULL(@Columns3 + ', ','') + QUOTENAME(TagName)
FROM (
select distinct TagName
from #taghits
) AS TagNames
ORDER BY TagNames.TagName
DECLARE @scolumns as NVARCHAR(MAX)
SELECT @scolumns = ISNULL(@Scolumns + ', ','')+ 'ISNULL(' + QUOTENAME(TagName) + ', 0) AS '+ QUOTENAME(TagName)
FROM (select distinct TagName
from #taghits) AS TagNames
ORDER BY TagNames.TagName
DECLARE @SQL as NVARCHAR(MAX)
SET @SQL = '
select userid, email, ' + @scolumns + '
from
(
select userid, email, tagname, hits
from #taghits
) as TagHits
PIVOT (
SUM(hits)
FOR TagName IN (' + @Columns3 + ')
) AS PivotTable
order by userId
'
exec sp_executesql @SQL;
当前结果
| userid | email | tag1 | tag2 |
|--------|-----------------|------|------|
| 1 | email1@here.com | 3 | 0 |
| 2 | email2@here.com | 1 | 1 |
| 3 | email3@here.com | 2 | 5 |
期望的结果
| userid | email | tag1_hits | tag1_lastview | tag2_hits | tag2_lastview |
|--------|-----------------|-----------|---------------|-----------|---------------|
| 1 | email1@here.com | 3 | 2020-03-24 | 0 | null |
| 2 | email2@here.com | 1 | 2020-03-17 | 1 | 2020-03-18 |
| 3 | email3@here.com | 2 | 2020-03-25 | 5 | 2020-03-28 |
解决方案
尝试以下操作:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((select distinct ',
SUM(CASE WHEN tagname=''' + CAST(tagname as varchar(10)) + ''' THEN [hits] ELSE 0 END) AS [' + CAST(tagname as varchar(10)) + '_hits],
MAX(CASE WHEN tagname=''' + CAST(tagname as varchar(10)) + ''' THEN [lastview] ELSE NULL END) AS [' + CAST(tagname as varchar(10)) + '_lastview]'
/*---------------You can add other columns here similar to above--------------*/
FROM #taghits
FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')
SET @query = 'SELECT userid, email, ' + @Cols + ' FROM #taghits group by userid, email'
print (@query)
exec(@query)
请在此处查看 db<>fiddle 。
推荐阅读
- lua - LuaJIT 的 C 解析器中的字符串文字支持
- apache-kafka - KAFKA - 错误锁定目录时磁盘错误
- html - 是否可以在 CSS 中重新定义命名颜色?
- c - Cello 库如何能够引入运算符和关键字的使用,即使它们不是 C 库的一部分?
- python - Python pandas检查行是否包含字符串
- node.js - Firestore GET Document Promise 没有结果
- c++ - c++ 中除了数组之外的所有东西都默认按值传递吗?
- javascript - 使用(Chrome、Firefox 和 IE)中的浏览器或选项卡关闭事件重定向到新页面
- angular - 角度“导航”到 404 但保留 url
- dart - Dart:Map.update 错误“未捕获的错误:TypeError:类型 '(dynamic) => num' 不是类型 '(String) => String' 的子类型”