Dynamic Core in SQL Server

Question

I have a problem getting out of the fulcrum. I need this in my desired form for coalescence tables.

My result:

us_id Phone Mail 1 555121313 NULL 1 NULL [e-mail] 2 14124124 NULL 2 NULL [e-mail] 

Required Conclusion:

 us_id Phone Mail 1 555121313 [e-mail] 2 14124124 [e-mail] 

Script for the test:

 create table #user (us_id int, us_login varchar(255)) INSERT INTO #user VALUES (1, 'LoginOne') INSERT INTO #user VALUES (2, 'LoginTwo') create table #atr_type (at_id int, at_name varchar(255)) insert into #atr_type values (1,'Phone'); insert into #atr_type values (2,'Mail') create table #atr (atr_id int, atr_us_id int, atr_at_id int, atr_value varchar(255)) insert into #atr values(1,1,1,'555121313') insert into #atr values(2,1,2,' [e-mail] ') insert into #atr values(3,2,1,'14124124') insert into #atr values(4,2,2,' [e-mail] ') 

My code for dynamic pivot:

 DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX); SET @columns = N''; SELECT @columns += N',' + QUOTENAME(at_name) FROM ( SELECT at_name FROM #atr_type) AS x; select @columns declare @cmd varchar(max) set @cmd = ' SELECT us_id' +@columns +' FROM #user inner join #atr on atr_us_id = us_id inner join #atr_type on at_id = atr_at_id PIVOT ( MIN(atr_value) FOR at_name IN (' set @columns = RIGHT(@columns, LEN(@columns)-1) set @cmd += @columns + ') ) AS PT' exec (@cmd) 

Answer 1

Try the following:

 DECLARE @columns NVARCHAR(1000) = '' , @columns2 NVARCHAR(1000) = '' , @sql NVARCHAR(MAX) SELECT @columns += N', [' + at_name + ']' , @columns2 += N', [' + at_name + '] = MAX([' + at_name + '])' FROM #atr_type SET @sql = ' SELECT us_id' + @columns2 + ' FROM #user inner join #atr on atr_us_id = us_id join #atr_type on at_id = atr_at_id PIVOT ( MIN(atr_value) FOR at_name IN (' + STUFF(@columns, 1,1,'') + ') ) AS PT Group by us_id' EXEC sp_executesql @sql 

Answer 2

 DECLARE @columns NVARCHAR(MAX),@columns2 NVARCHAR(MAX), @sql NVARCHAR(MAX) SET @columns = N'' SET @columns2 = N'' SELECT @columns += N',' + QUOTENAME(at_name) ,@columns2 += N',MAX(' + QUOTENAME(at_name)+') as'+ QUOTENAME(at_name) FROM ( SELECT at_name FROM #atr_type) AS x select @columns declare @cmd varchar(max) set @cmd = ' SELECT us_id' +@columns2 +' FROM #user inner join #atr on atr_us_id = us_id inner join #atr_type on at_id = atr_at_id PIVOT ( MIN(atr_value) FOR at_name IN (' set @columns = RIGHT(@columns, LEN(@columns)-1) set @cmd += @columns + ') ) AS PT Group by us_id' EXEC( @cmd)