2

I am building pivot query to display data in report form as below.

CREATE TABLE #Table1 (ColId INT)
INSERT INTO #Table1 VALUES(29)
INSERT INTO #Table1 VALUES(49)
INSERT INTO #Table1 VALUES(59)


DECLARE @cols NVARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ',[' + Convert(varchar(10), ColId)  + ']',
                         '[' + Convert(varchar(10), ColId) + ']')
FROM    #Table1
print (@cols)

Declare @final NVARCHAR(2000)
set @final=
'SELECT *
FROM (
SELECT tv
        , rv
        , dpv
FROM (

      ---Inner join query with multiple tables ----    

) As C
) P
PIVOT (
Max(dpv)
FOR tv IN (' + @cols + ')
) AS PVT'

exec(@final)


drop table #Table1

and result of above query as below.

enter image description here

I would like result as below

enter image description here

In this i need to append 'C' in tv so there would be 2 column 29C & 29 and under 29C i need to display 29C + dpv and under 29 i need to display summation of tv + dpv (i.e. 29+ 5=34) like that for all column.

How i can achieve it? How can we add multiple aggregate value? thanks in advance.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
Arun Rana
  • 8,426
  • 14
  • 67
  • 107
  • This may be helpful http://stackoverflow.com/questions/159456/pivot-table-and-concatenate-columns – Prasanna Sep 26 '12 at 08:53
  • Can you create a [SQL Fiddle](http://sqlfiddle.com/) with a working data model? There might be multiple ways to do this but it would be easier to see a working model. – Taryn Sep 26 '12 at 10:16

1 Answers1

2

Add this ...

DECLARE @c2 varchar(2000)
select @c2 = ''
SELECT  
        @c2 = @c2+ ', '''+ col + 'C+'' + CONVERT(varchar(5), ['+col+']) as ['+col+'C], ['+col+']+'+col+' as ['+col+'] '                             
FROM    (select convert(varchar(10),colid) as col from #Table1) v


declare @sql nvarchar(2000)
    select @sql = 'select rv ' + @c2 + ' from (' +@final + ') v'

exec (@sql)
podiluska
  • 50,950
  • 7
  • 98
  • 104