SQL Server 2008 Management Studio : convert row data into columns

Question

I have tried viewing other posts on the subject but all the examples I've seen are based on knowing a specific value.

Example of what I have:

Address       Name     Number
-------      -------   -------
1234 Main     Bob      555-555-5555
1234 Main     Karen    444-444-4444
1990 Maple    Susie    333-333-3333
1010 12th     Joe      222-222-2222
1010 12th     Beth     111-111-1111
1010 12th     Steve    444-433-3221

Example of what I want:

Address     Contact1               Contact2              Contact3
-------      -------               --------              --------
1234 Main    Bob:555-555-5555     Karen:444-444-4444     NULL
1990 Maple   Susie:333-333-3333      NULL                NULL
1010 12th    Joe: 222-222-2222    Beth 111-111-1111    Steve 444-433-3221

There are thousands of rows so I can't CASE and.. I'm a more than a little lost here.

Any suggestions?

yes, perform a search for [dynamic pivot](http://stackoverflow.com/search?q=%5Bsql-server%5D+dynamic+pivot) — Lamak, Jul 17 '14 at 18:57
I saw so many users asking question like this (Convert rows to columns)... They are writing question instead of searching... — Jesuraja, Jul 18 '14 at 07:27

score 1 · Answer 1 · answered Jul 17 '14 at 19:01

1

A dynamic pivot will work but so will a dynamic cross tab. Generally speaking a cross tab will beat the pivot for performance. Here is an example of one I posted just a few days ago.

if OBJECT_ID('Something') is not null
    drop table Something

create table Something
(
    ID int,
    Subject1 varchar(50)
)

insert Something
select 10868952, 'NUR/3110/D507' union all
select 10868952, 'NUR/3110/D512' union all
select 10868952, 'NUR/4010/D523' union all
select 10868952, 'NUR/4010/HD20' union all
select 12345, 'asdfasdf'
declare @MaxCols int

declare @StaticPortion nvarchar(2000) = 
    'with OrderedResults as
    (
        select *, ROW_NUMBER() over(partition by ID order by Subject1) 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 Subject1 end) as Subject' + CAST(N as varchar(6)) + CHAR(10)
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

answered Jul 17 '14 at 19:01

Sean Lange

33,028
3
25
40

*Generally speaking a cross tab will beat the pivot for performance*, do you have any links to a resource to back that statement up?. And if you think that just posting your exact same answer here is a good answer, then that means that the question is a duplicate – Lamak Jul 17 '14 at 19:04
You mean you won't just take my word for it? LOL. Here is an article by Jeff Moden about cross tabs. He compares static and dynamic cross tabs vs Pivots. Another big advantage of cross tabs is the ability to have multiple aggregations. http://www.sqlservercentral.com/articles/T-SQL/63681/ – Sean Lange Jul 17 '14 at 19:09
Ok, I read the post. I can see that there is no "tally" table there nor anything related to performing dynamic SQL, you are adding quite some things in your answer that what is used in those tests – Lamak Jul 17 '14 at 19:24
@Lamak did you look at the second article in the series? That is where he goes into the dynamic version. Ooohh...there isn't a link to article #2 in there. http://www.sqlservercentral.com/articles/Crosstab/65048/ – Sean Lange Jul 17 '14 at 19:31
No, first time actually logging in sqlservercentral (had to ask for a coworker's user), I don't even know how to search for another article there and don't clearly see a link to a "series", so, I read what you linked – Lamak Jul 17 '14 at 19:34
I edited my post above with the link. Sorry I forgot it. :) – Sean Lange Jul 17 '14 at 19:35
Well, don't know if there is a third article, but again, I don't see any benchmarks – Lamak Jul 17 '14 at 19:37
You are absolutely right. He did post performance benchmarks for static pivots vs static cross tabs but not in the dynamic version. The static versions a cross tab beats out pivot for either pre-aggregate or not. I will dig around and see if I can some benchmarks for the dynamic version of each. FWIW, I prefer the syntax for crosstabs as I find them easier to read. I know other people who feel the opposite. If I can find some performance measures I will post back. – Sean Lange Jul 17 '14 at 19:43

score 1 · Accepted Answer · answered Jul 17 '14 at 19:07

I think you can use the following query. This assumes you have maximum three contacts

;WITH orderedAddress(Address,Name,Number,Sort)
AS
(
SELECT Address,Name,Number,ROW_NUMBER() OVER(PARTITION BY Address ORDER BY Name) AS num
FROM Addresses
)
SELECT main.Address,Contact1.Name + ':' + Contact1.Number AS Contact1 ,
Contact2.Name + ':' + ISNULL(Contact2.Number,'') AS Contact2 ,
Contact3.Name + ':' + ISNULL(Contact3.Number,'') AS Contact3 
FROM
(SELECT DISTINCT Address FROM Addresses) AS main
LEFT JOIN OrderedAddress Contact1 ON main.Address = Contact1.Address AND Contact1.Sort=1
LEFT JOIN OrderedAddress Contact2 ON main.Address = Contact2.Address AND Contact2.Sort=2
LEFT JOIN OrderedAddress Contact3 ON main.Address = Contact3.Address AND Contact3.Sort=3

This way you already decided that there will be 3 contacts at the most. Op hints at that s/he wants a dynamic solution — Lamak, Jul 17 '14 at 19:12

SQL Server 2008 Management Studio : convert row data into columns

2 Answers2