Convert rows into columns by grouping one row value

pivot, sql, sql-server, sql-server-2008

Solution

Try this:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' +  
                     QUOTENAME('Director' + CAST(ROW_NUMBER() 
                                        OVER(PARTITION BY c.companyname 
                                             ORDER BY c.companyname) 
                                      AS VARCHAR(10)))
               FROM CompaniesDirectors c
               FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')  ,1,1,'');

SET @query = 'SELECT companyname,  ' + @cols + ' from 
             (
                SELECT 
                  c.CompanyName, 
                  c.DirectorName,  
                  ''Director'' + CAST(ROW_NUMBER() 
                                      OVER(PARTITION BY c.companyname 
                                      ORDER BY c.companyname) 
                                    AS VARCHAR(10)) director_num
                FROM CompaniesDirectors c
             ) x
             PIVOT 
             (
                MAX(directorname)
                FOR director_num IN (' + @cols + ')
             ) p ';

EXECUTE(@query);

This should give you something like:

companyname    Director1    Director2    Director3    Director4
    AB            Foo          Bar           rr          tt
    ADS           Rai          Rao           Raj         Rio

SQL Fiddle Demo1

1: Thanks to @bluefeet

Problem

My table is like ``` CompanyName | DirectorName ADS | Rai ADS | Rao ADS | Raj ADS | Rio SAS | Josh SAS | John ``` But I want result like: ``` CompanyName | Director1 | Director2 | Director3 | Director4 ADS |Rai |Rao |Raj |Rio SAS |Josh |John | | ``` For each company director name count varies. So kindly suggest how to form the data as per requirement dynamically.

Original source