如何使数据行成为分组列？

I want to group by ID but have each column group show up as a new horizontal set of columns.

我想按ID分组，但每个列组都显示为一个新的水平列集。

I have a table with data like this

我有一个包含这样数据的表

╔════╦═══════╦════════╗
║ ID ║ Phone ║  Type  ║
╠════╬═══════╬════════╣
║  A ║   111 ║ home   ║
║  A ║   222 ║ work   ║
║  B ║   333 ║ cell   ║
║  B ║   444 ║ school ║
╚════╩═══════╩════════╝

I want it to look like

我希望它看起来像

╔════╦════════╦═══════╦════════╦════════╗
║ ID ║ Phone1 ║ Type1 ║ Phone2 ║ Type2  ║
╠════╬════════╬═══════╬════════╬════════╣
║  A ║    111 ║ home  ║    222 ║ work   ║
║  B ║    333 ║ cell  ║    444 ║ school ║
╚════╩════════╩═══════╩════════╩════════╝

Ideally I would find a solution that would handle an arbitrary number of repeating groups but I could hard code the number if I had to.

理想情况下，我会找到一个解决方案，可以处理任意数量的重复组，但如果必须，我可以硬编码。

I could have up to 100,000 unique IDs with 20 repeating sets of column groups each with up to 5 individual columns.

我可以拥有多达100,000个唯一ID，其中包含20个重复的列组，每组最多包含5个列。

It seems like PIVOT would help with this but I can't figure out how.

似乎PIVOT会对此有所帮助，但我无法弄清楚如何。

EDIT: To be very clear here I do not want the row data to become column headers. I want the current column headers to repeat and have the row data span out into horizontal groups. There is no summation or aggregation here. This is why PIVOT doesn't seem to work (unless I am missing something which I hope I am!)

编辑：在这里要非常清楚我不希望行数据成为列标题。我希望重复当前列标题并使行数据跨越到水平组。这里没有总结或汇总。这就是为什么PIVOT似乎不起作用的原因（除非我错过了一些我希望是的东西！）

SQL Server 2012

1 个解决方案

#1

Here is an example using a dynamic crosstab as suggested by the article by Jeff Moden at sql server central.

下面是使用动态交叉表的示例，如Jeff Moden在sql server central中的文章所建议的那样。

His article can be found here. http://www.sqlservercentral.com/articles/Crosstab/65048/

他的文章可以在这里找到。 http://www.sqlservercentral.com/articles/Crosstab/65048/

if OBJECT_ID('tempdb..#Something') is not null
    drop table #Something

create table #Something
(
    ID char(1)
    , Phone int
    , MyType varchar(10)
)

insert #Something
select 'A', 111, 'home' union all
select 'A', 222, 'work' union all
select 'B', 333, 'cell' union all
select 'B', 444, 'school'

select *
from #Something

declare @StaticPortion nvarchar(2000) = 
'with OrderedResults as
(
    select *
        , ROW_NUMBER() over (partition by ID order by phone) as RowNum
    from #Something
)
select ID';

declare @DynamicPortion nvarchar(max) = '';
declare @FinalStaticPortion nvarchar(2000) = ' from OrderedResults Group by ID order by ID';


with E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS 
(
    SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)

select @DynamicPortion = @DynamicPortion + 
    ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then Phone end) as Phone' + CAST(N as varchar(6)) +
    ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then MyType end) as Type' + CAST(N as varchar(6))

from cteTally t
where t.N <= 
(
    select top 1 Count(*)
    from #Something
    group by ID
    order by COUNT(*) desc
)

select @StaticPortion + @DynamicPortion + @FinalStaticPortion

declare @SqlToExecute nvarchar(max) = @StaticPortion + @DynamicPortion + @FinalStaticPortion;
exec sp_executesql @SqlToExecute

#1