CODEWITHSUNDEEP
sql-server
Glycerol
Glycerol

Reputation: 53

How do I make rows of data become grouped columns?

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

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.

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

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!)

SQL Server 2012

Upvotes: 4

Views: 93

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

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

His article can be found here. 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

Upvotes: 1

Related Questions