I have a query like this with pivot table
SELECT WorkID,
[Easter la la la] as [workpropertyvalue1],
[la la] as [workpropertyvalue2],
[la la l a - remix 2] as [workpropertyvalue3],
[la la la - the remix] as [workpropertyvalue4],
[la la la song] as [workpropertyvalue5],
[padma song1] as [workpropertyvalue6],
[Pop Music] as [workpropertyvalue7],
[saregama] as [workpropertyvalue8],
[test song ] as [workpropertyvalue9],
[Title] as [workpropertyvalue10],
[Title Song] as [workpropertyvalue11],
[Title Song 3] as [workpropertyvalue12],
[Title Song 4] as [workpropertyvalue13],
[Title song2] as [workpropertyvalue14],
[2/23/2010] as [workpropertyvalue15],
[Blues] as [workpropertyvalue16],
[Jazz] as [workpropertyvalue17],
[Pop] as [workpropertyvalue18],
[Rock] as [workpropertyvalue19],
[124] as [workpropertyvalue20],
[189] as [workpropertyvalue21],
[2] as [workpropertyvalue22],
[234] as [workpropertyvalue23],
[3] as [workpropertyvalue24],
[4] as [workpropertyvalue25],
[654.. i'll chnage desc (seconds)] as [workpropertyvalue26],
[1/1/0001] as [workpropertyvalue27],
[2/14/2010] as [workpropertyvalue28],
[2/15/2010] as [workpropertyvalue29],
[2/22/2010] as [workpropertyvalue30],
[2/24/2010] as [workpropertyvalue31],
[2/8/2010] as [workpropertyvalue32],
[3/14/2010] as [workpropertyvalue33],
[3/15/2010] as [workpropertyvalue34],
[3/22/2010] as [workpropertyvalue35],
[3/23/2010] as [workpropertyvalue36],
[3/31/2010] as [workpropertyvalue37],
[4/19/2010] as [workpropertyvalue38],
[223] as [workpropertyvalue39],
[False] as [workpropertyvalue40],
[4/19/2010] as [workpropertyvalue41],
[4/21/2010] as [workpropertyvalue42],MemberId,IsValidated
FROM ( SELECT WorkID, workpropertyvalue,aa.MemberId,aa.Isvalidated FROM
(SELECT distinct wp.WorkID, wp.WorkPropertyValue, w.IsValidated,w.MemberId FROM dbo.Works AS w INNER JOIN(SELECT WorkID, WorkPropertyValue, WorkTypePropertyID FROM dbo.WorkProperties) AS wp ON w.WorkID = wp.WorkID INNER JOIN(SELECT WorkTypePropertyID, WorkTypeID FROM dbo.WorkTypeProperties) AS wtp ON wtp.WorkTypePropertyID = wp.WorkTypePropertyID INNER JOIN(SELECT WorkTypeID FROM dbo.WorkTypes WHERE (DictionaryKey = 'work_Song')) AS wt ON wt.WorkTypeID = wtp.WorkTypeID)aa)bb PIVOT ( MAX(WorkPropertyValue) FOR WorkPropertyValue IN (
[Easter la la la],[la la],[la la l a - remix 2],[la la la - the remix],
[la la la song],[padma song1],[Pop Music],[saregama],
[test song ],[Title],[Title Song],[Title Song 3],
[Title Song 4],[Title song2],[2/23/2010],[Blues],
[Jazz],[Pop],[Rock],[124],
[189],[2],[234],[3],
[4],[654.. i'll chnage desc (seconds)],[1/1/0001],[2/14/2010],
[2/15/2010],[2/22/2010],[2/24/2010],[2/8/2010],
[3/14/2010],[3/15/2010],[3/22/2010],[3/23/2010],
[3/31/2010],[4/19/2010],[223],[False],[4/19/2010],[4/21/2010]) ) AS pvt order by workid desc
It is throwing error
The column '4/19/2010' was specified multiple times for 'pvt'.
I know Pivot table is getting confused with duplicate values.but I need those dupliacte values there.then what is the solution??
what is the solution for this?