您可以使用PIVOT
为了这。如果您知道必须转换的列数,则可以使用静态版本:
select *
from
(
select RptLOB1 RptLOB1, RPTLOB2 RPTLOB2, Correlation
from yourtable
union all
select RPTLOB2, RptLOB1, Correlation
from yourtable
union all
select distinct RptLOB1, RptLOB1, 1.0
from yourtable
) x
pivot
(
max(Correlation)
for RPTLOB2 in ([AE], [Bail], [Commercial])
) p;
see SQL Fiddle 演示 http://sqlfiddle.com/#!3/4f4fa/25
如果您有未知数量的关联值,那么您将需要使用动态版本:
DECLARE @query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsPivot = STUFF((SELECT distinct ','
+ quotename(RptLOB1)
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select RptLOB1, '+@colspivot+ '
from
(
select RptLOB1 RptLOB1, RPTLOB2 RPTLOB2, Correlation
from yourtable
union all
select RPTLOB2, RptLOB1, Correlation
from yourtable
union all
select distinct RptLOB1, RptLOB1, 1.0
from yourtable
) x
pivot
(
max(Correlation)
for RPTLOB2 in ('+ @colspivot +')
) p'
exec(@query)
see SQL Fiddle 演示 http://sqlfiddle.com/#!3/4f4fa/31
编辑 - 根据您的评论,如果您希望将列标题放在另一行中,则可以使用以下内容:
DECLARE @query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX),
@colsRow as NVARCHAR(MAX),
@colsConverted as NVARCHAR(MAX)
select @colsPivot = STUFF((SELECT distinct ','
+ quotename(RptLOB1)
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsRow = STUFF((SELECT distinct ', '''
+ RptLOB1 + ''' as ' + RptLOB1
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsConverted
= STUFF((SELECT distinct ', CAST('
+ quotename(RptLOB1)
+ ' as varchar(50))'
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select ''RptLOB1'' as RptLOB1,
'+ @colsRow + '
union all
select RptLOB1, '+ @colsConverted+ '
from
(
select RptLOB1 RptLOB1, RPTLOB2 RPTLOB2, Correlation
from yourtable
union all
select RPTLOB2, RptLOB1, Correlation
from yourtable
union all
select distinct RptLOB1, RptLOB1, 1.0
from yourtable
) x
pivot
(
max(Correlation)
for RPTLOB2 in ('+ @colspivot +')
) p'
exec(@query)
see SQL Fiddle 演示 http://sqlfiddle.com/#!3/4f4fa/42