SQL Server 中的动态数据透视 [英] Dynamic pivot in SQL Server

问题描述

我对枢轴的输出有疑问.我需要它以我想要的形式来合并表.

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

我的结果:

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

期望的输出:

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

测试脚本:

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]')

我的动态枢轴代码:

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)

推荐答案

试试这个:

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

这篇关于SQL Server 中的动态数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！