sql-server - SQL Server 中的动态枢轴

Question

我对数据透视表的输出有问题。我需要它以我想要的形式来合并表。

我的结果:

us_id   Phone           Mail
1   555121313   NULL
1   NULL            LoginOne@mail.com
2   14124124    NULL
2   NULL            LoginTwo@mail.com

期望的输出:

us_id   Phone   Mail
1   555121313   LoginOne@mail.com
2   14124124    LoginTwo@mail.com

测试脚本:

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,'LoginOne@mail.com')
insert into #atr values(3,2,1,'14124124')
insert into #atr values(4,2,2,'LoginTwo@mail.com')

我的动态枢轴代码:

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

试试这个:

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