CODEWITHSUNDEEP
sql-serverpivot
ram
ram

Reputation: 11626

Converting rows to Columns in SQL

I have a table (actually a view, but simplified my example to a table) which gives me some data like this

 CompanyName website
 Google         google.com
 Google         google.net
 Google         google.org
 Google         google.in
 Google         google.de
 Microsoft       Microsoft.com
 Microsoft       live.com
 Microsoft       bing.com
 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](

 [CompanyName] [varchar](50) NULL,
 [website] [varchar](50) NULL
) ON [PRIMARY]
GO

insert into company values ('Google','google.com')
insert into company values ('Google','google.net')
insert into company values ('Google','google.org')
insert into company values ('Google','google.in')
insert into company values ('Google','google.de')
insert into company values ('Microsoft','Microsoft.com')
insert into company values ('Microsoft','live.com')
insert into company values ('Microsoft','bing.com')
insert into company values ('Microsoft','hotmail.com')

Edit: I am removing the ID as it was created for simplification. I think I should not have it.Sorry about it

Upvotes: 1

Views: 3342

Answers (3)

tgolisch
tgolisch

Reputation: 6734

I had to tackle a similar report for my work but I came at it from a different direction. I wrote (aped) a SQL CLR custom aggregate to roll-up the website list as a comma-separated string.

For example:

--load the temp table named Company from your example
--then run this query
SELECT CompanyName, StringUtil.Concat(Website) AS WebSites FROM Company

--outputs:

CompanyName Websites
----------- ---------------------------------------------------
Google      google.com, google.net, google.org, google.in, google.de
Microsoft   microsoft.com, live.com, bing.com, hotmail.com

(2 row(s) affected)

The code and the instructions for build/install/using a CLR custom Aggregate come with SQL Server (2005 and up), but aren't usually installed. The docs are on MSDN http://msdn.microsoft.com/en-US/library/ms161551(v=SQL.90).aspx

Upvotes: 0

KM.
KM.

Reputation: 103697

try this:

DECLARE @Company table (
    [id] [int] NULL,
    [CompanyName] [varchar](15) NULL,
    [website] [varchar](15) NULL
) 

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

;WITH CompanyGrouped AS
(   SELECT
        *,ROW_NUMBER() OVER(PARTITION BY CompanyName ORDER BY CompanyName, id) AS ColumnNumber
        FROM @company
)
SELECT DISTINCT
    t.CompanyName
        ,t1.website AS website1,t2.website AS website2,t3.website AS website3, t4.website AS website4, t5.website AS website5, t6.website AS website6
    FROM @Company                      t
        LEFT OUTER JOIN CompanyGrouped t1 ON t.CompanyName=t1.CompanyName AND t1.ColumnNumber=1
        LEFT OUTER JOIN CompanyGrouped t2 ON t.CompanyName=t2.CompanyName AND t2.ColumnNumber=2
        LEFT OUTER JOIN CompanyGrouped t3 ON t.CompanyName=t3.CompanyName AND t3.ColumnNumber=3
        LEFT OUTER JOIN CompanyGrouped t4 ON t.CompanyName=t4.CompanyName AND t4.ColumnNumber=4
        LEFT OUTER JOIN CompanyGrouped t5 ON t.CompanyName=t5.CompanyName AND t5.ColumnNumber=5
        LEFT OUTER JOIN CompanyGrouped t6 ON t.CompanyName=t6.CompanyName AND t6.ColumnNumber=6

Output:

CompanyName website1      website2   website3   website4    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

(2 row(s) affected)

Upvotes: 2

Thomas
Thomas

Reputation: 64674

If you are using SQL Server 2005+ you can do something like so:

With NumberedSites As
    (
    Select CompanyName, Website
        , ROW_NUMBER() OVER( PARTITION BY CompanyName ORDER BY Id ) As Num
    From Table
    )
Select CompanyName
    , Min( Case When Num = 1 Then Website End ) As Website1
    , Min( Case When Num = 2 Then Website End ) As Website2
    , Min( Case When Num = 3 Then Website End ) As Website3
    , Min( Case When Num = 4 Then Website End ) As Website4
    , Min( Case When Num = 5 Then Website End ) As Website5
    , Min( Case When Num = 6 Then Website End ) As Website6
From NumberedSites
Where Num <= 6
Group By CompanyName

Now, this solution is obviously not dynamic and assumes six columns (seven including company name). If you want a dynamic set of columns, the only way to do that is with some fugly dynamic SQL code. Instead, if you want a dynamic crosstab, I would suggest doing that in the middle-tier or in a reporting tool.

Upvotes: 2

Related Questions