需要此查询的解决方案 [英] Need solution for this query

问题描述

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?

推荐答案

您已选择2010/4/19两次，一次作为workpropertyvalue38，一次作为workpropertyvalue41.然后，您在列表中两次引用2010年4月19日...这可能会混淆您的数据透视.

You have selected 4/19/2010 twice, once as workpropertyvalue38 and once as workpropertyvalue41. You then refer to 4/19/2010 twice in your in list... This will likely confuse your pivot.

好了，我知道了解决方法，我使用C#.Net代码删除了重复项因为Pivot将重复项弄糊涂了


公共DataTable RemoveDuplicateRows(DataTable dTable，字符串colName)
{
Hashtable hTable = new Hashtable();
ArrayListplicateList =新的ArrayList();
//将所有唯一项值的列表添加到哈希表，该表存储键，值对的组合.
//并在arraylist中添加重复的项目值.
foreach(dTable.Rows中的DataRow卓尔)
{
如果(hTable.Contains(drow [colName]))
repeatList.Add(drow);
其他
hTable.Add(drow [colName]，string.Empty);
}
//从数据表中删除重复项的列表.
foreach(重复列表中的DataRow dRow)
dTable.Rows.Remove(dRow);
//包含唯一记录的数据表将作为输出返回.
return dTable;

Ok I got the solution,I removed the duplicates using C#.Net Code since Pivot is confusing with duplicates


public DataTable RemoveDuplicateRows(DataTable dTable, string colName)
{
Hashtable hTable = new Hashtable();
ArrayList duplicateList = new ArrayList();
//Add list of all the unique item value to hashtable, which stores combination of key, value pair.
//And add duplicate item value in arraylist.
foreach (DataRow drow in dTable.Rows)
{
if (hTable.Contains(drow[colName]))
duplicateList.Add(drow);
else
hTable.Add(drow[colName], string.Empty);
}
//Removing a list of duplicate items from datatable.
foreach (DataRow dRow in duplicateList)
dTable.Rows.Remove(dRow);
//Datatable which contains unique records will be return as output.
return dTable;

这篇关于需要此查询的解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！