Dynamic pivot in SQL Server

7
votes

I have problem with output from pivot. I need it in my desired form to coalesence tables.

My result:

us_id   Phone           Mail
1   555121313   NULL
1   NULL            [email protected]
2   14124124    NULL
2   NULL            [email protected]

Desired output:

us_id   Phone   Mail
1   555121313   [email protected]
2   14124124    [email protected]

Script for 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,'[email protected]')
insert into #atr values(3,2,1,'14124124')
insert into #atr values(4,2,2,'[email protected]')

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)
2
sql-serverpivot

2 Answers

5
votes

Try this:

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
4
votes
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)