.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

problems in pivot query

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :ASP.Net



             I am using following pivot query  but not getting result as single row getting as 4 rows.

One more problem i am unable to pass dates as parameters to storeprocedure something like this

select [@date] .

        SELECT 'Forecasted' AS HeadCount,
[8/1/2010], [8/8/2010], [8/15/2010], [8/22/2010]
FROM TblEmpCount) AS SourceTable
FOR StartDate IN ([8/1/2010], [8/8/2010], [8/15/2010], [8/22/2010])
) AS PivotTable;

I am getting result as 4 rows but i want result in single row like this

HeadCount    8/1/2010     8/8/2010    8/15/2010    8/222/2010

Forecasted     191                182                 176                169





View Complete Post

More Related Resource Links

Pivot w/o aggregation...query not working

Hi all--Given this query, I need to make this query into a pivot table: select Personnel_id, First Name, Last Name, E-mail, Division, Region, Country, Manager, Question, Answer, Date Completed from #pivot where Question Answer 01. Received File No 02. Read File Yes 03. Signed File Yes ... Needs to look like this: Personnel_id First Name ... 01. Received File 02. Read File 03. Signed File ... Test01 Test No Yes Yes I have the following pivot query: SELECT [Personnel_id] ,[First Name] ,[Last Name] ,[Email] ,[Division] ,[Region] ,[Country] ,[Manager] ,[Question] ,[Answer] ,[Date Completed] FROM (SELECT [Personnel_id] ,[First Name] ,[Last Name] ,[Email] ,[Division] ,[Region] ,[Country] ,[Manager] ,[Question] ,[Answer] ,[Date Completed] FROM #pivot) topvt PIVOT (MAX([Personnel_id]) FOR Answer in ([1],[2], [3], [4], [5], [6], [7], [8] , [9], [10])) as pvt ORDER BY pvt.[Personnel_id] I get this error: Msg 207, Level 16, State 1, Line 83 Invalid column name 'Personnel_id'. Any ideas? Thanks!

Sub Query Problems

Apologies if I have not posted this in the correct section.  I am having some difficulties with some sub queries. First I'd like to show the database design. Rez_Desc Table Rez_ID Rez_Number Client_Name Arriving_ID Pickup_ID 1001 201000123 Mr. Ross 1 2 1002 201000124 Mrs. Smith 2 1 Arrival_Desc Table Arriving_ID Label 1 AUS 2 USA OffSite_Desc Table OffSite_ID Label 1 AUS 2 USA Ok so this is my table structure, simplified. This is the query I am trying to run. SELECT Rez_Desc.Rez_Number AS RezNum, (SELECT OD.Label FROM Offsite_Desc OD LEFT JOIN Rez_Desc AS RD ON OD.Offsite_ID = RD.Pickup_ID AND RD.Rez_ID=Rez_Desc.Rez_ID) AS Pickup, (SELECT AR.Label FROM Arrival_Desc AR LEFT JOIN Rez_Desc AS RD ON AR.Arriving_ID = RD.Arriving_ID AND RD.Rez_ID=Rez_Desc.Rez_ID) AS DropOff FROM Rez_Desc And I get the following error:- Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. To my knowledge and understanding it is being caused because the sub query is returning more than 1 result which it should not be doing.  Seeing as how in my example table above I have 2 entries, both with relevant ID's etc. Need some help!

Problems writing a dynamic L2E query

I'm trying to re-work a L2E query to be more dynamic, but I'm not having much luck. Basically, I have two parameters (and many more to come, just laying the foundation), and the parameters are both optional from a user-endpoint.Originally I wrote this static expression:int personnelId = 1234; int divisionId = 1234; var results = (from a in ctx.Attendees from d in a.Divisions join p in ctx.Personnel on a.PersonnelID equals p.PersonnelID where a.PersonnelID == personnelId && d.DivisionID == divisionId select new Attendee { firstName = a.FirstName, lastName = a.LastName });Attendee is a POCO. After I wrote this, I realized that if personnelId or divisionId weren't passed in (or if just one were passed in) I'd want a different result set. I'm conceptualizing the idea like this (doesn't compile, but you get the drift):var results = (from a in ctx.Attendees select new Attendee { firstName = p.FirstName, lastName = p.LastName }); if (personnelId != null) { results = (from a in results join p in ctx.Personnel on a.PersonnelID equals p.PersonnelID where a.PersonnelID == personnelId select a); } if (divisionId != null) { results = (from a in results from d in a.Divisions where d.DivisionID == divisionId select a); } results = results.ToList();Doesn't work well though, because my

Problems escaping apostrophe when using an XPath query with SelectSingleNode

I have an XML node which I am trying to search for a specific node which has a given value, but I am having problems when the value contains an apostrophe. I have tried replacing the apostrophe with &apos as follows:                string encodedTitle = title.Replace("'", "&apos;");                 string XmlPath = String.Format("item[title = \'{0}\']", encodedTitle);                 return NodeChannel.SelectSingleNode(XmlPath);                string encodedSearchString = searchString.Replace("'", "&apos;");                string XmlPath = String.Format("item[title = \'{0}\']", encodedSearchString);                return myNode.SelectSingleNode(XmlPath);However, this does not seem to find search strings with an apostrophe (it doesn't throw an exception or anything, it just returns null). Search strings without an apostrophe work fine. Is there any way I can fix this?

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. Converting an Access pivot query to SQL 2005


    Hi, I am new to table pivot and would like to know how to go about converting the following query in Access to SQL Server 2005:


    SELECT tbl_Proj_Budget.PROJ_ID,
    FROM tbl_Proj_Budget 
    GROUP BY tbl_Proj_Budget.PROJ_ID 

    1) what does the 1st line mean?


    2) How would I conver this to TSQL ?




database query problems


Hello All, I have a result table in MS SQL Database server (2005).

Subject   Exam                 SubExam   Year     Marks

Math       First Terminal    CT              2010    18
Math       First Terminal    Hall Exam   2010    67.2

Science   First Terminal    CT              2010    20
Science   First Terminal    Hall Exam   2010    78

Now how can I get the result followningly

                                First Terminal

                        CT       Hall Exam      Total


query problems


Hello All, I have a result table in MS SQL Database server (2005).

Subject   Exam                 SubExam   Year     subjective objective practical

Math       First Terminal    CT              2010    18             0              0
Math       First Terminal    Hall Exam   2010    38             46            0

Science   First Terminal    CT              2010    20             0              0
Science   First Terminal    Hall Exam   2010    32            40      &

SELECT and UPDATE query problems



I have a web form that should display advertisements depending of the number of impressions (number stored in a database) and I also need to update the number of impressions when page is loaded. The advertisements are displayed on a web page, but the problem is I don't know where to insert the SqlCommand for updating number of impressions.

Labels are used for displaying the number of impressions.  

        String connectionString = WebConfigurationManager.ConnectionStrings["CMS"].ToString();
        using (SqlConnection connection = new SqlConnection(connectionString))

            using (SqlCommand commandReklama = new SqlCommand("SELECT * FROM Reklama", connection))
                SqlDataReader readerReklama = commandReklama.ExecuteReader();
                while (readerReklama.Read())
                    string redirectUrl = readerReklama.GetString(1);
                    string url = readerReklama.GetString(2);
                    int brojPrikazivanja = readerReklama.GetInt32(3);
                    bool show = readerReklama.GetBoolean(4);                  
                    string naziv = readerReklama.GetString(5);
                    int location = readerReklama.GetInt32(6);

                    if (

Help figure out where records are being dropped from a pivot query


I have a stored procedure that creates a pivot query. The problem I'm having is that the inner query is returning 1641 records, then when the pivot is executed, only 1450 records make it to the result set.

The query is as follows:

SELECT CallSegments.AppName, CH.CallDate, dbo.fn_IsContacted(CH.AppName, CH.Result) AS Contacted, CallSegments.QueueID, dbo.fn_GetResultCategory( CallSegments.AppName, CH.Result ) AS CallResult 
FROM CallSegments, CallHistory AS CH WHERE CH.CallInstance = CallSegments.CallInstance 
AND RTRIM( CallSegments.AgentID ) = '' 
AND RTRIM( CallSegments.QueueID ) <> '' 
AND CH.AppName = 'some_client' 
AND CH.CallDate >= '11/3/2009 00:00:00.000' AND CH.CallDate <= '11/3/2009 23:59:59.997' 


The interesting thing, for me, is that I can copy the internal select clause and run it separately. When it executes, I receive 1641 records in the result. Then when it goes throug the pivot, records are dropped. I don't quite understand because all of the inner records have a category listed in the pivot list. I've looked it over several times but I just can't see where I'm losing records.

Use power pivot to query the data in MS CRM


Hi All,

I want to query the data in MS CRM (entities) using power pivot from IT Share Point. Can any one guide me in this please.


Problems with AND/OR in SQL query in strongly type datasets



In my ASP.NET web application I am using strongly typed datasets for data access. In my dataset.xsd class I have added an SQL query that works in SQL Management Studio, and when executed in the Table Adapter Query Configuration Wizard --> Execute Query form. But when invoked from the application using the autogenerated table adapter it returns no hits.

INNER JOIN Department ON Department.Dep_Id = Device.Dev_Dep_id 
INNER JOIN Customer ON Customer.Cus_Id = Department.Dep_CustomerNr 
INNER JOIN RootCustomer ON RootCustomer.RCus_Id = Customer.RCus_Id
(RootCustomer.RCus_Id = @CustomerId) AND (Device.Dev_Status = 10) AND (Device.Dev_serial LIKE '%' + @searchPattern + '%') OR
(RootCustomer.RCus_Id = @CustomerId) AND (Device.Dev_Status = 10) AND (Device.Dev_CustMark LIKE '%' + @searchPattern + '%') OR
(RootCustomer.RCus_Id = @CustomerId) AND (Device.Dev_Status = 10) AND (Device.Dev_IpAddress LIKE '%' + @searchPattern + '%') OR
(RootCustomer.RCus_Id = @CustomerId) AND (Device.Dev_Status = 10) AND (Device.Dev_ArticleNr LIKE '%' + @searchPattern + '%') OR
(RootCustomer.RCus_Id = @CustomerId) AND (Device.Dev_Status = 10) AND (Device.Dev_Descr LIKE '%' + @searchPattern + '%')
ORDER BY Device.Dev_serial

What possib

Sub query and problems

SELECT FirstName, LastName, EmployeeNumber, SSN, SUM(Credits)
FROM Employees E 

Query issue - Pivot?


I a query that returns the following:

Day    TypeID     Product A     Product B
1        1            10               20 
1        2            30               40
2        1            50               60 
2        2            70               80

But, i need to return the following:
Day    Product A Type1     Product A Type2     Product B Type1
     Product B Type2
1       10                        30                        20                        40

Users use Excel-Pivot table to query the cube, how to capture those queries?


Most of our users use Excel'10 for querying the cube,

I've set up the querylog-properties in SSAS engine but it only captures the query from SSAS engine and not from the other platform such as Excel. So, when I use Usage based aggregation wizard, it shows the users/ueries from SSAS engine only. Is there any way to capture the user/query information that comes through Excel pivot table and somehow create aggregation through that information?


Any suggestion/help is very much appreciated,


MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Top .NET Performance Problems and how to avoid them

Every time I work with one of our .NET customers to help them with managing their application performance I come across the same problems as seen with other clients before: lots of ADO.NET queries, many hidden exceptions in core or 3rd party .NET libraries, slow 3rd party components, inefficient custom code
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