我的输入为
123 1 Y Active
123 1 Y Idle
109 1 Y Active
109 1 Y Away
165 1 Y Active
145 1 Y Idle
我需要输出为:
123 1 Y Active and Idle
109 1 Y Active and Away
165 1 Y Active Only
145 1 Y Idle Only
SELECT distinct COl1,Col2,Col3 ,
case when Col4 = 'Active' and Col4 = 'Idle' then 'Active and Idle'
when Col4 = 'Active' and Col4 = 'Away' then 'Active and Away'
when Col4 = 'Active' then 'Active Only'
when Col4 = 'Idle' then 'Idle Only'
end as category
from Person
我尝试了上述查询,但它不起作用,并且因为我是尝试将多行转换为1列,这是不可能的案例。
我必须尝试类似枢轴的东西,但无法实现。有帮助吗?
您需要FOR XML PATH()
方法:
SELECT DISTINCT p.col1, STUFF(pp.Col4, 1, 4, '') AS category
FRPM Person p CROSS APPLY
( SELECT ' AND ' + p1.Col4
FROM Person p1
WHERE P1.col1 = p.col1
FOR XML PATH('')
) pp(Col4);
通过这种方式,FOR XML PATH('')
将返回每个col1
&外部查询STUFF()
将在字符串开始时用''
替换AND
。
尝试以下内容:
declare @table table(
col1 int,col2 int,col3 nvarchar(10),col4 nvarchar(50)
)
insert into @table values(123, 1, 'Y' ,'Active')
insert into @table values(123, 1, 'Y' ,'Idle')
insert into @table values(109, 1, 'Y' ,'Active')
insert into @table values(109, 1, 'Y' ,'Away')
insert into @table values(165, 1, 'Y' ,'Active')
insert into @table values(145, 1, 'Y' ,'Idle')
select col1,col2,col3, stuff((SELECT ' AND ' + t2.col4 + CASE WHEN count(t1.col1) = 1 THEN ' ONLY' ELSE '' END
FROM @table t2
WHERE t2.col1 = t1.col1
ORDER BY t2.col1
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,5,'') AS col4
from @table t1
group by col1,col2,col3
我正在使用stuff
到不包括组中不包括的CONCAT值。
当count = 1
时,这意味着该组中只有一个项目,因此您需要添加Only
后缀。
这并不是一个明显的问题,即使在提供STRING_AGG
的SQL Server 2017中也是如此。如果每组只有一排,则需要检查Only
。
给定此表:
declare @table table (id int, flag1 int, flag2 char(1), status varchar(40))
insert into @table
values
(123,1,'Y','Active'),
(123,1,'Y','Idle'),
(109,1,'Y','Active'),
(109,1,'Y','Away'),
(165,1,'Y','Active'),
(145,1,'Y','Idle')
以下查询将返回汇总值。如果只有一个值,它将附加Only
:
select id,flag1,flag2,
string_agg(status,' and ') + IIF(count(*)>1,'',' Only')
from @table
group by id,flag1,flag2
这会产生:
109 1 y活跃和离开123 1 y活跃和空闲145 1 y只闲置仅165 1 y活跃