How do I use row data as columns, with variable row data

Question

I have a table in database like :

enter image description here

And I want the data Output like :

enter image description here

FeeName Column value is not fixed i.e. another type of fee can be there too.

Kindly help to resolve it.

Looking for your valuable solutions.

Thanks...

Also, if you have to twist your data like this to get the answer you want, your data structures probably aren't correct for your requirements. — Turophile, May 23 '14 at 12:12
possible duplicate of [SQL Server PIVOT examples?](http://stackoverflow.com/questions/24470/sql-server-pivot-examples) — Kevin Hogg, May 23 '14 at 12:34
@Ninjapig, I don't think this is a duplicate of that, as the columns are well-defined in the example you link. Supriya states that her columns are dynamic. — SeeJayBee, May 23 '14 at 12:39
check out this link http://stackoverflow.com/questions/16759531/dynamic-pivot-in-sql-server take a look at answer which is marked useful. — AK47, May 23 '14 at 12:50

score 3 · Accepted Answer · answered May 23 '14 at 12:36

As Turophile says, this data format indicates a problem in your data design.

Still, if you need to do this, here's how i would do it:

(note I created a temp table, and populated it in the example. You already have the table and the data, but I needed something for testing)

Create table #feeSchedule
(
    FeeId int identity(1,1) not null primary key,
    Description nvarchar(255) not null,
    FeeAmount money not null,
    FeeName nvarchar(50) not null
)
go

insert into #feeSchedule values('Nursery', 5000, 'Admission Fee')
insert into #feeSchedule values('Nursery', 1000, 'Tuition Fee')
insert into #feeSchedule values('Nursery', 100, 'Late Fee')
insert into #feeSchedule values('Nursery', 0, 'Exam Fee')
insert into #feeSchedule values('Play Group', 5000, 'Admission Fee')
insert into #feeSchedule values('Play Group', 2500, 'Tuition Fee')
insert into #feeSchedule values('Play Group', 100, 'Late Fee')
insert into #feeSchedule values('Play Group', 0, 'Exam Fee')


DECLARE @columnHeaders VARCHAR(MAX)
SELECT @columnHeaders = 
  COALESCE( 
    @columnHeaders + ',[' + cast(FeeName as varchar) + ']',
    '[' + cast(FeeName as varchar)+ ']'
  )
FROM (select distinct FeeName from #feeSchedule) as fs

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
  SELECT *
  FROM (
    SELECT
      fs.Description,
      fs.FeeName,
      fs.FeeAmount
    FROM #feeSchedule fs
  ) AS PivotData
  PIVOT (
    SUM(FeeAmount)
    FOR FeeName IN (
      ' + @columnHeaders + '
    )
  ) AS PivotTable
'

exec sp_ExecuteSql @sql

go
drop table #feeSchedule

score 0 · Answer 2 · answered May 23 '14 at 13:20

0

SELECT *
FROM (
SELECT 
    Description(invoiceDate) as [year],FeeAmount, 
    InvoiceAmount as Amount 
FROM TableName
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (Admission Fee, Tution Fee, Exam Fee, Late Fee)
)AS pivot

answered May 23 '14 at 13:20

Pranav Bilurkar

955
1
9
26

How do I use row data as columns, with variable row data

2 Answers2