tsql - Complicated irregular pivot in SQL Server 2012 -

i have 2 tables:

a (column1, column2, column3) b (column6, column7, column8) 

a.column1 foreign key in b.column6.

one row table a matches 3 rows in table b, 5, 1.... no definite count of returned rows.

i have business requirement flip corresponding columns in table b 1 row.. this:

a.column1, a.column2, a.column3, b.column7, b.column8, b.column7, b.column8 a.column1, a.column2, a.column3, b.column7, b.column8 a.column1, a.column2, a.column3, b.column7, b.column8, b.column7, b.column8, b.column7, b.column8 a.column1, a.column2, a.column3, b.column7, b.column8, b.column7, b.column8, b.column7, b.column8b.column7, b.column8, b.column7, b.column8   

you see , number of columns in each row table 3... table b, might have variable number of columns.... , column7 , column8 have repeated appear in order.

how can this? thanks.

it sounds going need unpivot , pivot data. if have unknown number of values have use dynamic sql first suggest writing hard-code or static version of query first, convert dynamic sql.

the process unpivot data going take multiple columns in tableb , convert multiple rows. since using sql server 2012 can use cross apply unpivot data:

select column1, column2, column3,   col = col + '_' + cast(seq varchar(10)),  value (   select a.column1, a.column2, a.column3,     b.column6, b.column7, b.column8,     row_number() over(partition a.column1                       order a.column1) seq   tablea   inner join tableb b     on a.column1 = b.column6 ) d cross apply (   select 'column6', column6 union   select 'column7', column7 union   select 'column8', column8 ) c (col, value); 

see sql fiddle demo. give result similar to:

| column1 | column2 | column3 |       col | value | |       1 |       2 |       3 | column6_1 |     1 | |       1 |       2 |       3 | column7_1 |    18 | |       1 |       2 |       3 | column8_1 |    56 | |       1 |       2 |       3 | column6_2 |     1 | |       1 |       2 |       3 | column7_2 |    25 | |       1 |       2 |       3 | column8_2 |    89 | 

as can see have multiple rows can apply pivot function to. pivot code be:

select column1, column2, column3,   column6_1, column7_1, column8_1,   column6_2, column7_2, column8_2,   column6_3, column7_3, column8_3 (   select column1, column2, column3,     col = col + '_' + cast(seq varchar(10)),     value     (     select a.column1, a.column2, a.column3,       b.column6, b.column7, b.column8,       row_number() over(partition a.column1                         order a.column1) seq     tablea     inner join tableb b       on a.column1 = b.column6   ) d   cross apply   (     select 'column6', column6 union     select 'column7', column7 union     select 'column8', column8   ) c (col, value) ) src pivot (   max(value)   col in (column6_1, column7_1, column8_1,               column6_2, column7_2, column8_2,               column6_3, column7_3, column8_3) ) piv; 

see sql fiddle demo. since stated might have unknown or dynamic number of entries in tableb need use dynamic sql. generate sql string executed final result:

declare @cols nvarchar(max),     @query  nvarchar(max)  select @cols = stuff((select ',' + quotename(col +'_'+cast(seq varchar(10)))                                          (                       select row_number() over(partition column6                                                 order column6) seq                       tableb                     ) t                     cross apply                     (                       select 'column6', 1 union                       select 'column7', 2 union                       select 'column8', 3                     ) c (col, so)                     group col, so, seq                     order seq,             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  set @query = 'select column1, column2, column3,' + @cols + '                          (               select column1, column2, column3,                 col = col + ''_'' + cast(seq varchar(10)),                 value                             (                 select a.column1, a.column2, a.column3,                   b.column6, b.column7, b.column8,                   row_number() over(partition a.column1                                     order a.column1) seq                 tablea                 inner join tableb b                   on a.column1 = b.column6               ) d               cross apply               (                 select ''column6'', column6 union                 select ''column7'', column7 union                 select ''column8'', column8               ) c (col, value)             ) x             pivot              (                 max(value)                 col in (' + @cols + ')             ) p '  execute sp_executesql @query; 

see sql fiddle demo. both versions give result:

| column1 | column2 | column3 | column6_1 | column7_1 | column8_1 | column6_2 | column7_2 | column8_2 | column6_3 | column7_3 | column8_3 | |       1 |       2 |       3 |         1 |        18 |        56 |         1 |        25 |        89 |    (null) |    (null) |    (null) | |       2 |       4 |       6 |         2 |        78 |       245 |    (null) |    (null) |    (null) |    (null) |    (null) |    (null) | |       3 |       8 |       9 |         3 |        10 |        15 |         3 |        45 |       457 |         3 |        89 |        50 |