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

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

Rows to Column (PIVOT)

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

I have following data :

  ID  ID2  Name   Details  Value
   1   11   A      Alpha     101
   1   11   A      Beta      201
   1   11   A      Charlie   301
   1   11   A      Delta     401
   2   22   B      Alpha     102
   2   22   B      Beta      202
   2   22   B      Charlie   302
   2   22   B      Delta     402
   3   33   C      Alpha     103
   3   33   C      Beta      203
   3   33   C      Charlie   303

That needs to transform to this using TSQL:


View Complete Post

More Related Resource Links

How to get column value difference of rows in same table

Dear all, I need a TSQL statement to find the difference of values of two rows in the same table, by taking into consideration some conditions. I have the following rowss in a table named table1 dbname  sqlinst     size1   ddate sqldb1    inst1        200    1/1/2009 sqldb1    inst1        250    1/1/2010 sqldb1    inst1        170    1/1/2008 sqldb2    inst2        300    1/1/2009 sqldb2    inst2        340    1/1/2010 I need to find the difference between size1 values, for columns where their dbname and sqlinst are the same. I also need to define in TSQL that the ddate of row from where I subtract (size1) from is 1/1/2010 and that the ddate of the row I subtract (size1) is 1/1/2009 (e.g. in the above example: for sqldb1 inst1, I need to perform 250-200 and ignore 170, and for sqldb2 inst2  340-300). Please let me know if you have a solution for this. A million thanks!

Searching a NVarChar Column with multiple wildcards returns all rows

I'm building a stored procedure to return a set of records, yes nothing big. The column is an NVarchar column and I'm using a select statement of Select * from Table1 where Column1 Like @Column1 Table is currently one record containing the words:  ***Some Test Word***  Is my test word I've set the value of @Column1 to: Some - no records returned Some% - no records returned %Some% - 1 record returned  % - 1 record returned *% - no records returned %*% - 1 record returned %Not Here% - 1 record returned   Can someone tell me why if I have a leading and trailing wildcard I will get all records returned?  Does it have something to do with the '*' characters in the field because some of my users are using these characters. I've also tried changing the select to: where RTRIM(Column1) Like @Column1 with the same result and where RTRIM(Column1) Like N'%' + @Column1 with the same result   What way should I give a user the way to search for a substring inside of an NVarchar field? Oh, I tried using the Substring function and got the same result. Thanks Mike    

How to sum a column in a datagrid for just the multiselected rows?

I have a WPF4 datagrid, populated via linq to sql, which has some numeric fields. How do I sum the value of one of these fields for just the selected rows? (multiselect is turned on)  

Infopath 2007 Repeating Table - Multiple Value Column Text - Hiding Rows based on Column text values

Infopath 2007 browser based form Full Trust Example: I have a repeating table (FruitChoice) that has multiple columns. Both drop down list point to sharepoint list data sources. Choose your tree ft. drop down list – 6Ft Choose your Department drop down list - 103 This repeating table is conditional on the drop down values. This works great. Trees     Fruit       Cost   Date Ordered    Date Delivery Department 6Ft        Peaches                                                        103 3Ft        Apples                                                          102 3Ft        Peaches         &

Adding new rows to Datagrid with a Combobox column generate "two-way binding requires path or xpath"



    I have datagrid control with a template combobox column like:

 <DataGridTemplateColumn Header="Fault code" Width="75">
                                    <TextBlock Text="{Binding FaultCode1}" />

iterate throw rows of Template Column in Datagrid



I am using WPF DataGrid (VS 2008). i am using Template column with a button in it i want to change the content of the button(Caption) when it is clicked .i am doing it in its click event ,but it  changes the content of only the button clicked(only one row). i want to change the content of button in all rows.

how can access the button instance of all rows in this template column.



Iterating through Rows in Template Column



I am using WPF DataGrid (VS 2008). i am using Template column with a button in it i want to change the content of the button(Caption) when it is clicked .i am doing it in its click event ,but it  changes the content of only the button clicked(only one row). i want to change the content of button in all rows.

how can access the button instance of all rows in this template column.



Convert excel rows into column


 this is the data into excel sheet. i have excel sheet like following way. i want to upload and when it download so it should be following way.

REG_NO   2222    
Name   xyz
ADDRESS xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx      
PHONE   3333333333      
Description asasdfasfasfasdfasdf      

Convert excel rows wise data into column wise


public static string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

               OleDbDataAdapter adapter = new OleDbDataAdapter();

            OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [Sheet1$]");

            OleDbConnection connection = new OleDbConnection(connectionString);

            selectCommand.Connection = connection;

            adapter.SelectCommand = selectCommand;

            DataSet cities = new DataSet();


need to join two tables and also convert top 3 rows to column


Hi, I have two tables, CUSTOMER and PAYMENT_DETAILS. I need to extract "name" and "phone" from CUSTOMER table and TOP 2 payments for matching email from PAYMENT_DETAILS table. Kindly refer to the attached image for table structure and Result required.




Pivot table query dynamic column.


Hello All,


I want to export my data to excel sheet. The logic to export data in excel is clear.

Now my query is I have 3 tables and I want to generate the data jst like below.



Company Name

Company Email


Create Date


Fax No














And so on.....


The data will come from 3 tables I.e


  1. Master Table for Company

  2. Using ntext column i a table with many many rows?


    I want to know more about implications of adding a ntext column to an already existing table with 10000+ rows. My estimate is that only 1 out of 100 rows will haveanything stored in the ntext field. Does this mean that space required is counted for every row this table contains? Not only for those rows that have data in this field?

    Shall I instead constuct a sub table storing the ntext column with a relation id pointer to the specific row in the mother tale to save space?


coalesce building pivot column headers - making them descending instead of ascending


Hi, I was somewhat surprised and happy to see that the t-sql statement below can be used to dynamically build my pivot column headers.   I was also surprised to see that either it is smart enought to build the headers alphabetically or I'm incredibly lucky.  Date is a char(7) column in my table with format yyyy/mm.

Anyway, it would be really cool for me if the headers were build descending instead of ascending.  I tried using a view to do this but the thing has a mind of its own.  I'll start writing a function but would like to know if the community knows of a better way. 

It generates a string that looks something like ...




@PivotColumnHeaders VARCHAR

A tidy and efficient way of excluding rows with the same column value or equal to null


Good morning

I frequently use the pivot statement to generate columns I wish to compare, often looking for differences between the values in the columns. Quite often the pivot returns some null values which I am having some difficulty in comparing.


Rownum              Col1       col2        col3    col4  etc

1                              A             A             A     &

Concatenating column values for multiple rows


I know this question has been asked many times. I am just trying to verify that my solution is correct.

I need to concatenate a column values with the rows being ordered on another column.

I initially used the FOR XML PATH clause for the purpose, and it worked wonders, until I had XML entities (&, < etc) in my data, and FOR XML PATH encoded those. After some searching on experts-exchange, I came up with the following code for myself:

	SET @freeFieldXml = '';
	SELECT @freeFieldXml = @freeFieldXml + '/' + Value FROM @fields ORDER BY CodeType;
	SET @freeFieldXml = STUFF(@freeFieldXml, 1, 1, '');

In my testing, the above code worked perfectly. I just want to confirm that the data would always be sorted on the desired column and the concatenated string would actually have the field values sorted by that column.

I always think tomorrow will have more time than today. And every today seems to pass-by

How to Add Multiple Column's Sum in Pivot Table...???


hi Fredz..


My Final Table is #Example and in that table m adding Addition of 5 Columns but its not working Properly..???


declare @columnheader varchar(max)

Select @columnheader = COALESCE(@columnheader + ',isnull([' + cast(@FieldName as varchar) + '],0)','isnull([' + cast(@FieldName as varchar)+ '],0)') 

FROM #Example



SET @query = '

SELECT * into fianltable

FROM #Example



 SUM(Col1+ Col2+ Col3Col4 + Col5)     ----In that line error will occur

----If m can change this line n taking only SUM(Col1then its Works, but i want addition of

---- FIVE columns

 FOR [MergeValue]

 IN (' + @columnheader + ')


 AS p order by ' + @columnheader + ''


Analysis Services 2008 e Excel pivot table: different filters for each column


I have to create a report in excel 2010 based on a  analysis services 2008 cube.

I created a pivot table that contains four columns containing the elements of a hierarchy. My problem is that each column should be filtered with different values of the same field.

For example: given a hierarchy that describes me a set of products, I have to filter the product for customer  1 and 3, Product B for  customer 2 and the product C for the customer 3.

I think this thing is not possible in Excel (right?)

So I decided to use a mdx script to filter each product 

I tried so (for example, product A):
scope (descendants([Products].[products].[liv0].&[A], 1, leaves));
[Measures].Quantity = sum({[Customer].[Code].&[01], [Customer].[Code].&[03]}, [Measures].quantity);
end scope;

I still get an error of infinite recursion. How can I solve my problem? Is there a better system than what I am following?

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