-3

I have a view as follows (did a view as I thought it would be easier that accessing the more complicated table)

ID | aText1 | aText2 | aInt1 | aInt2
-------------------------------------
1  | ABC1   | XYZ1   | 2     |  20
2  | ABC1   | XYZ2   | 3     |  25
3  | ABC2   | XYZ2   | 1     |  30
4  | ABC2   | XYZ1   | 4     |  35

I need the result to read

       | XYZ1          | XYZ2
aText1 | aInt1 | aInt2 | aInt1 | aInt2 
---------------------------------------
ABC1   | 2     | 20    | 3     | 25
ABC2   | 1     | 30    | 4     | 35

I've tried various pivots but all fail. aText1 and aText2 could be any number of values. As close to this solution as possible would be really helpful

pee2pee
  • 3,619
  • 7
  • 52
  • 133
  • 1
    _I've tried various pivots but all fail_ Please edit your question showing your attempts, it could be that you have something small in your code that needs to be corrected. – Taryn Jun 17 '14 at 11:17

1 Answers1

1

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
ventik
  • 877
  • 7
  • 18
  • Excellent thanks - in the first instance, how would I bring back the column names as well? Sure it's obvious but I probably can't see the wood through the trees as this point – pee2pee Jun 17 '14 at 12:15
  • Do you want to get "XYZ1" and "aInt1" from "XYZ1 - aInt1"? I usually use '#' as delimiter between column names ("XYZ1#aInt1") and then get two substrings from column name in my client application. Or for what do you need to bring them back? – ventik Jun 17 '14 at 12:25
  • Basically the output I get is fine but the end user (or I) wouldn't know what the column names are – pee2pee Jun 17 '14 at 12:36
  • Why? I don't understand what you mean. In this example end user will receive this columns - "aText1", "XYZ1 - aInt1", "XYZ1 - aInt2", "XYZ2 - aInt1", "XYZ2 - aInt2" – ventik Jun 17 '14 at 12:41
  • Sorry, I forgot to mention I would be using ASP to output the data so by "default" it won't output the columns but I should be fine I hope – pee2pee Jun 17 '14 at 12:43
  • I'm sure that it won't be a problem to output this column names. In any case it will be a problem with ASP, not with SQL server. – ventik Jun 17 '14 at 12:51