SQL Pivot列的同一列具有多个值 [英] SQL Pivot Column that has multiple values for same column

问题描述

尝试透视表结果可能包含多个具有相同值的行 到目前为止，我的数据看起来像这样.

Trying to pivot table results that may have multiple rows with the same value I have data that looks like this so far.

Nbr      Person     Test

33      Barry.      Prim
33      Brian       Sup
33      Burke RT    1st
33      Ray         Add
33      Jake        Add
33      Smith       Add

我正在尝试对其进行旋转，以使其看起来像这样:

I'm trying to pivot it so that it looks like this:

Nbr   Prim    Sup     1st       Add   Add2       Add3

33   Barry   Brian   Burke      Ray   Jake       Smith

到目前为止，这是我能正常使用的枢轴，但是无法在测试列"中获取所有具有相同值的对象

This is what I have so far with a normal pivot but it doesn't work to grab all the ones with the same value in the Test Column

CREATE TABLE #testTbl(nbr int,name varchar(20),test VARCHAR(10))
INSERT INTO #testTbl
SELECT '33','Barry','Prim'
UNION
SELECT '33','Brian','Sup'
UNION
SELECT '33','Burke','1st'
UNION
SELECT '33','Ray','Add'
UNION
SELECT '33','jake','Add'
UNION
SELECT '33','Smith','Add'


select * from (
Select * 
from #testTbl
) as x
 pivot(
 max(name) for test in ([prim],[sup],[1st],[add])
)
as pivot1

任何帮助将不胜感激.如果不可能将列输出为Add Add2和Add3，那就没问题了.无论如何.

Any help is greatly appreciated. If its not possible to have the columns output as Add Add2 and Add3 thats fine. Whatever works.

推荐答案

您可以通过使用窗口函数修改test值来做到这一点:

You can do so by modifying the test value using window functions:

select *
from (Select tt.name,
             (test + (case when count(*) over (partition by test) = 1
                           then ''
                           else cast(row_number() over (partition by test order by (select null)) as varchar(255))
                      end)) as test
      from testTbl tt
     ) as x
pivot(
 max(name) for test in ([prim], [sup], [1st], [Add1], [Add2], [Add3])
) as pivot1

SQL小提琴位于此处.

这篇关于SQL Pivot列的同一列具有多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！