sql server - SQL query to return identical rows as columns dynamically named with increment number -

what need obtain return rows columns dynamically , in case label value exist more 1 same value return (e.g. phone, phone1, phone2):

for data isactive=1 , isprincipal=1 displayed on column without number (e.g. phone, fax, e-mail )

create table #contacts   (      contactid int,       labelid int,       label nvarchar(25),       [value] nvarchar(25),       isactive bit,       isprimary bit,       customerid int )  insert #contacts   values     (1, 1,  'phone', '(541) 754-3010', 1, 1, 1),     (2, 1,  'phone', '(541) 764-3011', 1, 1, 2),     (3, 1,  'phone', '(541) 754-3013', 1, 0, 2),     (4, 1,  'phone', '(541) 754-3014', 1, 0, 2),     (5, 2,  'phone personal', '1111 111 1111', 1, 1, 2),     (6, 3,  'fax', '+44 41 444-5555', 1, 1, 2),     (7, 3,  'fax', '+44 41 444-5595', 1, 0, 2),     (8, 4,  'e-mail', 'john@sales.com', 1, 0, 1),     (9, 4,  'e-mail', 'office@expertbike.com', 1, 1, 1),     (10, 4, 'e-mail', 'mary@purchasing.com', 1, 0, 1)   create table #customer (customerid int, [name] nvarchar(30))  insert #customer values     (1, 'sport playground'),     (2, 'expert bike eu') 

what need display after joining tables shown here:

    customer name       phone            phone1            phone2            phone personal      fax                  fax1              e-mail                    e-mail1               e-mail2     sport playground    (541) 754-3010                                                                                                  office@expertbike.com     john@sales.com        mary@purchasing.com     expert bike eu      (541) 764-3011   (541) 754-3013    (541) 754-3014    1111 111 1111       +44 41 444-5555      +44 41 444-5595 

this requirement puts me in trouble.

something following should trick...

set nocount on;  if object_id('tempdb..#contacts', 'u') not null  drop table #contacts;  create table #contacts (      contactid int,       labelid int,       label nvarchar(25),       [value] nvarchar(25),       isactive bit,       isprimary bit,       customerid int );  insert #contacts   values     (1, 1,  'phone', '(541) 754-3010', 1, 1, 1),     (2, 1,  'phone', '(541) 764-3011', 1, 1, 2),     (3, 1,  'phone', '(541) 754-3013', 1, 0, 2),     (4, 1,  'phone', '(541) 754-3014', 1, 0, 2),     (5, 2,  'phone personal', '1111 111 1111', 1, 1, 2),     (6, 3,  'fax', '+44 41 444-5555', 1, 1, 2),     (7, 3,  'fax', '+44 41 444-5595', 1, 0, 2),     (8, 4,  'e-mail', 'john@sales.com', 1, 0, 1),     (9, 4,  'e-mail', 'office@expertbike.com', 1, 1, 1),     (10, 4, 'e-mail', 'mary@purchasing.com', 1, 0, 1);   if object_id('tempdb..#customer', 'u') not null  drop table #customer;  create table #customer (     customerid int,      [name] nvarchar(30)     );  insert #customer values     (1, 'sport playground'),     (2, 'expert bike eu');  --======================================================= --=======================================================  if object_id('tempdb..#contactlabelrn', 'u') not null  drop table #contactlabelrn;  select      c.contactid, c.labelid, c.label, c.value, c.isactive, c.isprimary, c.customerid,     rn = row_number() on (partition c.customerid, c.label order c.isprimary desc, c.contactid asc) - 1     #contactlabelrn     #contacts c      c.isactive = 1 order     c.contactid;  -------------------------------------------------  declare      @contactlabel varchar(8000) = '',     @sql varchar(8000) = '',     @debug bit = 0;       cte_labelrn (         select distinct              clr.labelid,             clr.label,             clr.rn                      #contactlabelrn clr         ) select      @contactlabel = concat(@contactlabel, ',     [', lr.label, nullif(lr.rn, 0), '] = max(case when clr.label = ''', lr.label, ''' , clr.rn = ', lr.rn, ' clr.[value] end)')     cte_labelrn lr order     lr.labelid,     lr.rn;  set @sql = concat(' select      [customer name] = c.[name]',     @contactlabel, '      #contactlabelrn clr     join #customer c         on clr.customerid = c.customerid group      c.[name];')  if @debug = 1 begin      print(@sql); end; else begin      exec(@sql); end; 

results...

customer name                  phone                     phone1                    phone2                    phone personal            fax                       fax1                      e-mail                    e-mail1                   e-mail2 ------------------------------ ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- expert bike eu                 (541) 764-3011            (541) 754-3013            (541) 754-3014            1111 111 1111             +44 41 444-5555           +44 41 444-5595           null                      null                      null sport playground               (541) 754-3010            null                      null                      null                      null                      null                      office@expertbike.com     john@sales.com            mary@purchasing.com 

hth, jason