.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to add another column to sum/average/count/or any possible calculations to those pivoted columns

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server

Hi All,

I am currently using this Dynamic Pivot Table Queries from this link:


I manage to get the result i required but i need to add further column to it. I have a table such as this.

Name  |   Item   |   Points

Eddy       A             3

Eddy       B             4

John       A             2


I could get the result such as this from the above link after pivot.

Name  |  A   |    B

Eddy      3         4

John       2         null

How do i make it to become like this? Adding a column average so the final result will look like this.

Name | Average |  A  |  B

Eddy     3.5           3      4

John        1          

View Complete Post

More Related Resource Links

How to get character count in 'Multiple Text 'column using Calculated Column?


Hi All,

I have a multiple text box in a list

I want

  • A column which lists number of characters in multiple list column example 188, 199 etc
  • Multiple character column must not have more than 200 chars
Is this possible?

Appreciate all the help!


DataGridView>Edit Columns> Column Read Only property is unwilling to change

Hello, I am facing the following mystery: When changing the read only property of a column to false and reopening the "edit columns" window, it mysteriously turns back to true. Even when closing the designer and changing it in the designer code, the resulting execution does not allow edit, as well as when re-opening the designer you see true again.             this.numberDataGridViewTextBoxColumn1.DataPropertyName = "Number";             this.numberDataGridViewTextBoxColumn1.HeaderText = "Number";             this.numberDataGridViewTextBoxColumn1.Name = "numberDataGridViewTextBoxColumn1";             this.numberDataGridViewTextBoxColumn1.ReadOnly = true;//False is required We thought it might be related to data binding. rebinding the grid did not help as well. The is no other place in the code where it changes. Please advice, Thanks

split a column into multiple columns

Hi i have a column with the following values from my source FILENAME ComplianceStatus_ER_06022010.xml ComplianceStatus_AZER_07052010.xml ComplianceStatus_GEL_06022010.xml ComplianceStatus_AFF_05022010.xml ComplianceStatus_Good_2010.xml   I want to split the date into different columns as follows using ssis   FILENAME                            CLIENT      DATE ComplianceStatus_ER_06022010.xml    ER          06022010 ComplianceStatus_AZER_07052010.xml  AZER        07052010 ComplianceStatus_GEL_06022010.xml   GEL         06022010 ComplianceStatus_AFF_05022010.xml   AFF         05022010 ComplianceStatus_Good_2010.xml      Good        2010  

Error when creating PK to a column which has dupliacte columns..

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'table name' and the index name 'PK__column__118068EB'. The duplicate key value is (D1).

Using derived column for calculations

Hi Guys, Im trying to calculate a value using a derived column. I have this following recordset in my data flow. C1 C2 ITM 88 ITM 10 TX 52.45 DC 92.18 I have this formula: 1. DC = (DC- TX) * 1.12 So the result would be like this. C1 C2 ITM         88.00 ITM         10.00 TX         52.45 DC         44.50 Thanks,  

Name Column Not Included in List Columns

I created a custom content type derived from the Folder content type.  The “Name” column is included in the custom content type columns.  I add the custom content type to a custom list that allows multiple content types and the "Name" column from the custom content type is not included in the list columns.  Normally, the custom content type columns are added to the list columns, but for some reason it works differently for the “Name” column.  Even though the “Name” column is not a typical site column and not included, it’s a problem for me because I need it for a SharePoint/SQL Server data flow task. The data flow task is using the SharePoint list source and destination components and I need to map a database column to the “Name” column and it must be specified in the list columns.  Does the “File” type for the “Name” column make it impossible to be included in the list columns?  Any help is appreciated.

Discovering ODBC Tables, Columns and column Definitions

I have worked with ODBC data connections for a while.  Usually, I've setup a connection to some type of database (IBM ISeries, SQL Server, Progress, etc.) depending on the project and then I use MS Access to inspect or export the data using linked tables. I have a database that has numerous tables and columns.  In fact, I believe there are some 16k data columns in the entire database.  Furthermore, I have an excel file that gives me a list of all of these tables and a brief description of each column  but it does not have useful data types in the description.  What I would like to do is query the ODBC source and extract each table, it's columns and it's column data types. My end goal is to take the data from the spread sheet (column names and description) and insert that data into a database that also contains the correct, SQL data type in an additional field.  Is there a way to query an ODBC data source to acquire all of this information?

Hiding Columns (not column groups) in a Matrix, can it be done

Hi.  I want to be able to hide some of the columns of this report.  For example , I don't want to see the % of State Total for Total column, and I don't want to see the % of Location Total for the other column groups.  Can this be done?  Any help would be appreciated.  Thanks J I am using Visual Studio 2005   Total   Anaheim CA Arlington VA Atlanta GA Count % of State Total % Of Location Total Count % of State Total % Of Location Total Count % of State Total % Of Location Total Count % of State Total % Of Location Total Count % of State Total % Of Location Total USA   3 100.00% 0.02%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00%   AB 2 100.00% 0.01%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00%   AE 8 100.00% 0.04%   0.00% 0.00%   0.00% 0.00% 3 37.50% 0.10% 1 12.50% 0.07%   AK 32 100.00% 0.17%   0.00% 0.00%   0.00% 0.00% 3 9.38% 0.10% 1 3.13% 0.07%   AL 161 100.00% 0.85%   0.00% 0.00%   0.00% 0.00% 17 10.56% 0.54% 84 52.17% 6.11%   AP 3 100.00% 0.02%   0.00% 0.00%   0.00% 0.00%   0.00% 0.00% 1 33.33% 0.07%

restrictions on additional columns available for addition with Lookup column?

Can a choice column be added as an additional field when creating a Lookup column? I was able to do this in the beta version but have been unable to in RTM. The column does not appear in the list of availabe columns to add as additional fields.

How to know the number of columns displayed on the last page for Multi column table

Hi, How will I determine how many columns to be displayed on the last page of the report if I am using a Multiple column table.  I am going to use the number of columns to multiply with the width of the body of the report. I am going to use a Function to get the product. 

how to update a column with some rules in it (base on other columns)


Hello Sir,

i have table named mytable1 it has values as below,  with composite primarykey (CKCOL1,CKCOL2,CKCOL3)
how to update finalresult values as below math, i tried it with different groupings

CKCOL1 CKCOL2, CKCOL3, SalesID,   Team1,    Team2,     Team3,     counts,   units,      ordertotal,     otheramount,  FinalResult
DDN 175 ETQ NULL 10:40 MON JUN28 NULL 1 0 2814 NULL 
DDN 175 ETQ NULL 10:40 TUE JUN29 NULL 1 0 4938 NULL
DDN 175 ETQ NULL 10:40 MON JUL05 NULL 1 0 2814 NULL
DDN 175 ETQ 31566 10:40 NULL JUL13 9 NULL 13998 3432 NULL

DDN 175 TVQ NULL 1:40 SUN JUL25 NULL 2 0 7831 NULL
DDN 175 TVQ NULL 1:40 SAT JUL24 NULL 2 0 2168 NULL
DDN 175 TVQ 40546160 1:40 MON JUN1 3 NULL 5000 NULL NULL
DDN 175 TVQ 40546245 1:40 TUE AUG1 1 NULL

OLEDB/ADO Destionation - Add input column twice to destination columns or static data


(Using BIDS 2008 R2)

OLEDB/ADO Destionation

Reuse input column
Is there any way to map a input column to two destination columns?

State static value instead of mapping column
I have a package for each version of the production system - they insert in the same destination tables - I just need to add a version number to the version column - like '2.1' and '3.0'. The only way I have found so far is to select a fake column in the source like select '2.2' as Version, ..- This is however not very smart when I'm selecting millions of rows.

I would just like to be able to type in a static value instead of mapping a column - any way to do this?

Errors with calculated columns, the column posts, but doesn't calculate.



I have a custom list that isn't processing calculated columns anymore.

When I post a calculated column I get one of two error messages...either "SQL Server may not be started" or "The value does not fall within the expected range".  I've noticed the sql message before but the changes are made and calculations in the past have worked ok.  Now that the "value" error is cropping up it's quit calculating the columns.

i.e. I open a calculated column, simply have it reference a numeric field (=[MyNumberField]).  When I hit ok I get one of the two error messages.  When I look at the value of the calculated column it's blank.

Any references to articles, etc. would be helpful.  There are 406 columns in this list...

Best Regards,


Clone GridView-Columns Widths (assign the Column-Width from 1 Gridview to another)


Hi everyone,

I've googled quite a lot, but couldn't find a solution - in the end this forum always helped me out the best! :)

This is my problem: I've got my Gridview gv_1, it gets its data from the code behind through a DataSet, gets generated like it should be, and assigns it's column-width to the longest entry in the datarow. Now I've got my Gridview gv_2, which has other entries and column widhts (gets filled the same way) - what I want to do is to change the width of the columns that the gridviews have the same overall width.

I've uploaded a screenshot: http://stepdown.elementfx.com/image16.jpg

What I've already tried is playing around with the GridView.HeaderRow.Cells[0].Width-Property, but when I debug it (after it's DataBound) I've got no values, meaning the values are "0.0" etc. The try to assign a value also didnt work (not directly for Width and not for Width.Value).

The next problem I could encounter is that my second GridView has no visible HeaderRow - and as far as I've googled people always somehow set the width of the column - but I'm not even that far yet. ;)

So the perfect solution (working in my head at least) would be to get the width of each column from the 2 gridviews, pick the larger one, and ass

MDX count distinct calculation where another column


Hi All,

i have a fact table like the following


1-episodeKey (not unique)

2-Readmission (takes value null or 'readmission' may i chaneg to (0 or 1) if that help solve the problem)


4-other dimesnions foreign keys


i want to count distinct episode keys where readmission is not null,

so how can i write a calculation that make that count ?




Ahmed Salah

WPF: how can ListBox display three columns: first and thrid column width base on the longest content


We try to use ListBox to display three columns: so the first and last column width will depend on the longest content in that column to decide and second column take the rest of space as its width.

How can we use ListBox to achive that?

should we try with the ListView? thx!

count of columns in a table?

how to know the count of columns in a table
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend