I usually use dynamic sql. Something like this
create table #T
(
ID int,
aText1 varchar(4),
aText2 varchar(4),
aInt1 int,
aInt2 int
)
insert into #T
select 1, 'ABC1', 'XYZ1', 2, 20
union
select 2, 'ABC1', 'XYZ2', 3, 25
union
select 3, 'ABC2', 'XYZ2', 1, 30
union
select 4, 'ABC2', 'XYZ1', 4, 35
declare @sql nvarchar(max)
set @sql = 'select aText1 '
select @sql = @sql + ', SUM(case when aText2 = ''' + aText2 + ''' then aInt1 end) as [' + aText2 + ' - aInt1] '+
', SUM(case when aText2 = ''' + aText2 + ''' then aInt2 end) as [' + aText2 + ' - aInt2]'
from
(
select distinct aText2 from #T
) T
set @sql = @sql + ' from #T group by aText1'
exec sp_executeSQL @sql
drop table #T
Or you can create another view (like #T2 in my next example) and use PIVOT
create table #T
(
ID int,
aText1 varchar(4),
aText2 varchar(4),
aInt1 int,
aInt2 int
)
insert into #T
select 1, 'ABC1', 'XYZ1', 2, 20
union
select 2, 'ABC1', 'XYZ2', 3, 25
union
select 3, 'ABC2', 'XYZ2', 1, 30
union
select 4, 'ABC2', 'XYZ1', 4, 35
create table #T2
(
aText1 varchar(4),
aText2 varchar(20),
aValue int
)
insert into #T2
select aText1, aText2 + ' - aInt1' as aText2, aInt1
from #T
union
select aText1, aText2 + ' - aInt2', aInt2
from #T
declare @sql nvarchar(max), @columns nvarchar(max)
set @columns = ''
select @columns = @columns + ', [' + aText2 + ']'
from (select distinct aText2 from #T2) as T
set @columns = substring(@columns, 2, len(@columns))
set @sql =
'
SELECT *
FROM
(SELECT
aText1,
aText2,
aValue
FROM
#T2
) AS SourceTable
PIVOT
(
SUM(aValue)
FOR aText2 in ('+@columns+')
) AS PivotTable'
exec sp_executeSQL @SQL
drop table #T2
drop table #T