Converting rows to Columns in SQL
- by Ram
I have a table (actually a view, but simplified my example to a table) which gives me some data like this
id CompanyName website
1 Google google.com
2 Google google.net
3 Google google.org
4 Google google.in
5 Google google.de
6 Microsoft Microsoft.com
7 Microsoft live.com
8 Microsoft bing.com
9 Microsoft hotmail.com
I am looking to convert it to get a result like this
CompanyName website1 website2 website3 website 4 website5 website6
----------- ------------- ---------- ---------- ----------- --------- --------
Google google.com google.net google.org google.in google.de NULL
Microsoft Microsoft.com live.com bing.com hotmail.com NULL NULL
I have looked into pivot but looks like the record(row values) cannot be dynamic (i.e can only be certain predefined values).
Also, if there are more than 6 websites, I want to limit it to the first 6
Dynamic pivot makes sense, but I would have to incorporate it into my view ?? Is there a simpler solution for this ?
Here are the SQL scripts
CREATE TABLE [dbo].[Company](
[id] [int] NULL,
[CompanyName] [varchar](50) NULL,
[website] [varchar](50) NULL
) ON [PRIMARY]
GO
insert into company values (1,'Google','google.com')
insert into company values (2,'Google','google.net')
insert into company values (3,'Google','google.org')
insert into company values (4,'Google','google.in')
insert into company values (5,'Google','google.de')
insert into company values (6,'Microsoft','Microsoft.com')
insert into company values (7,'Microsoft','live.com')
insert into company values (8,'Microsoft','bing.com')
insert into company values (9,'Microsoft','hotmail.com')
