我有一个表,其中包含重复结果集的数据,比如导致重复的列名和值。
ColName | ColValue | UniqueIDpercombination[/tr>|
---|---|---|
KitiD | K89901 | 1 |
套件 | 00900 | <1>|
KitiD | L7865 | 2 |
套件 | 00400 | 2 |
UPC | 345234 | 3 |
UPN | AVF | 3 |
使用STUFF
和XML PATH
的组合,然后使用GROUP BY
作为UniqueIDpercombination
列。
您已经完成了查询,但是,您需要首先从表中SELECT
,然后在子查询中使用WHERE
子句,以使外部查询JOIN
与内部子查询GROUP BY
基本一致。这样就不会在一行中返回整个表。
同样值得注意的是,我使用了2
作为STUFF函数的长度,以说明' - '
分隔符中的空格。您可以通过更改传递到STUFF
函数中的参数来处理所需的长度和分隔符。
STUFF ( character_expression , start , length , replaceWith_expression )
查询:
SELECT
STUFF((SELECT ' - ' + ColName
FROM sample_table b
WHERE a.UniqueIDpercombination = b.UniqueIDpercombination
FOR XML PATH('')), 1, 2, '') AS ColName,
STUFF((SELECT ' - ' + ColValue
FROM sample_table b
WHERE a.UniqueIDpercombination = b.UniqueIDpercombination
FOR XML PATH('')), 1, 2, '') AS ColValue,
a.UniqueIDpercombination
FROM sample_table a
GROUP BY UniqueIDpercombination
ORDER BY UniqueIDpercombination
db<gt;小提琴在这里。
create table #temp (
ColName varchar (10),
ColValue varchar (10),
UniqueIDpercombination int
)
insert into #temp values ('KitiD', 'K89901', 1)
insert into #temp values ('Kit', '00900', 1 )
insert into #temp values ('KitiD', 'L7865', 2 )
insert into #temp values ('Kit', '00400', 2 )
insert into #temp values ('UPC', '345234', 3 )
insert into #temp values ('UPN', 'AVF', 3 )
select *,ROW_NUMBER() over(partition by uniqueIDpercombination order by uniqueIDpercombination) as rownum into #joinTable from #temp
步骤1:如果你想使用联接表/一次性
select distinct UniqueIDpercombination into #masterTable from #temp
select concat(b1.ColName,' - ',b2.ColName) ColName,CONCAT(b1.ColValue,' - ',b2.ColValue) ColValue,a.UniqueIDpercombination from #masterTable a
left join #joinTable b1 on a.UniqueIDpercombination = b1.UniqueIDpercombination and b1.rownum = 1
left join #joinTable b2 on a.UniqueIDpercombination = b2.UniqueIDpercombination and b2.rownum = 2
步骤2:如果你想使用循环/如果重复的数量不是只有2行
select cast(NULL as varchar) ColName, cast(NULL as varchar) ColValue, UniqueIDpercombination into #masterTable1 from #temp
group by UniqueIDpercombination
declare @i int
set @i = 1
while @i <= (select max(rownum) from #joinTable)
begin
update a
set
a.ColName = case when a.ColName is null then b.ColName else (case when b.ColName is null then a.ColName else concat(a.ColName,' - ',b.ColName) end) end,
a.ColValue = case when a.ColValue is null then b.ColValue else (case when b.ColValue is null then a.ColValue else concat(a.ColValue,' - ',b.ColValue) end) end
from #masterTable1 a
left join #joinTable b on a.UniqueIDpercombination = b.UniqueIDpercombination and b.rownum = @i
set @i = @i +1
end
db<gt;不停摆弄https://dbfiddle.uk/CQ3RSYCQ