0

I have a table like this :

MNUM   EXP_TYP   ExpenseLabel   AMOUNT
572711   2          Taxes        7080
572711   3          Insurance    3730
572711   4          Electric     7800
572711   5          WaterIncome  6000
572711   7          Trash        2400
572711   8          Gardner      1200
572711   14         AnnOperExp   900
572741   2          Taxes        8400
572741   3          Insurance    1200
572741   5          WaterIncome  4800
572741   7          Trash        1200
572741   8          Gardner      1800
572741   11         RepairMaint  1200
572741   34         Pest         80

I want the result like this :

MNUM    Taxes Insurance Electric WaterIncome Trash  AnnOperExp RepairMaint Pest
572711   7080   3730     7800       6000      2400     900
572741   8400   1200                4800      1200               1200       80

This is what I tried :

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(ExpenseLabel) 
                    from #TempExpensesTab
                   group by MNUM,ExpenseLabel,EXP_TYP,AMOUNT
                    order by EXP_TYP
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select ExpenseLabel, AMOUNT
                from #TempExpensesTab
            ) x
            pivot 
            (
                max(AMOUNT)
                for ExpenseLabel in (' + @cols + N')
            ) p '

exec sp_executesql @query;

I am getting the following error : "The column 'Taxes ' was specified multiple times for 'p'."

Thanks

BumbleBee
  • 10,429
  • 20
  • 78
  • 123
  • 1
    You already know about pivot so come on,why no tries.. – Mihai Feb 24 '15 at 18:51
  • This doesn't need unpivot or cross apply, this is a pivot, unless you aren't telling us everything. – Taryn Feb 24 '15 at 18:53
  • 1
    Change this `group by MNUM,ExpenseLabel,EXP_TYP,AMOUNT` to `group by ExpenseLabel,EXP_TYP`. The problem is that you are getting multiple values for each column name because you are grouping by distinct values. You could see the incorrect columns by `print @cols` or `select @cols`. – Taryn Feb 24 '15 at 19:04
  • thank u that worked. How can i put that data into a temp table.? The temp table is out of scope when used inside dynamic sql. – BumbleBee Feb 24 '15 at 19:46

0 Answers0