.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

PIVOT or UNPIVOT howto ...

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server

Hi all

Does anybody knows PIVOT or UNPIVOT ?

I have the following Table:
ID    String
1    ZURN
2    SEP10
3    PUT
4    240.OE
1    UBSN
2    OKT10
3    CALL
4    150.OE

and from this I like to get this:

[Col1]          [Col2]           [Col3]       [Col4]
ZURN           SEP10           PUT          240.OE
UBSN           OKT10          CALL         150.OE

Thanks a lot for a quick feedback.

Best regards
Frank Uray

View Complete Post

More Related Resource Links

Using PIVOT and UNPIVOT for ranged data

Hi everyone!

I was just wondering if a SQL SELECT statement can be created for the following:

The table looks like this (TableName = Employees):

EmployeeId INTEGER
Salary MONEY

And the output I need is this:


Salary Range           21 - 25      26 - 30       31 - 35

1000 - 1999               12               15              18

2000 - 2999                6                 4                7

3000 - 3999             

SharePoint Wiki HowTo Template



was wondering if anyone has or has suggestion on the following:

we are contemplating creating a wiki type howto for our users which needs to be categoriesed into permission level (eg user, super user, admin).

We would quite like to have the ability to add content, and then link the relevent catories to it (done via a lookup at moment).

And relevent video (e.g embeded you tube).


The video part I am thinking of a library with a field type that allows embed (do you know of any?).  On the wiki page I quite like it if the video would show rather an having to navigate to them, but doubt this is possible.


I know wiki can't have a uniformed template due to the way they are produced and web parts have to be added manually.  Does this mean are idea would maybe be better as a publishing site with a custom template?

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!

Using Pivot to arranging data

Hello, I am attempting to optimize a selection query and I thought I would try a PIVOT. My question is simply is this the best way to do it? If not, why and what may be better? Please note the Tmstmp column is not necessarily spaced evenly like this example. Sample table:   Declare @temp table (Tmstmp datetime, Value integer ) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/01/10',1) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/02/10',2) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/03/10',11) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/04/10',1) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/05/10',3) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/06/10',5) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/07/10',11) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/08/10',1) INSERT INTO @temp (Tmstmp, Value) VALUES ('01/09/10',2) ;With CTE as ( SELECT t1.Tmstmp as StartDate, t2.Tmstmp as EndDate, t1.Value as Start_Val, t2.Value as End_Val, Row_Number() Over(Partition By t1.TmStmp ORDER BY DateDiff(day,t1.TmStmp,t2.TmStmp)) as Seq FROM @temp t1 INNER JOIN @temp t2 ON t2.Tmstmp > t1.Tmstmp WHERE t1.Value = 1 AND t2.Value = 11 ) SELECT StartDate, EndDate, [2] as TmStmp2, [3] as TmStmp3, [4] as TmStmp4, [5] as TmStmp5 FROM ( SELECT a.StartDate, a.EndDat

HOWTO add a C#.NET library to SQL Server as an assembly that uses such references as system.web.

HOWTO add a C#.NET library to SQL Server as an assembly that uses such references as system.web. I have a C# library (dll) I want to add to SQL Server via "CREATE ASSEMBLY", but the DLL uses these references: Microsoft.VisualBasic System System.Core System.Data System.Data.DataSetExtensions System.Drawing System.Management System.Web System.Web.Extensions System.Web.Mobile System.Web.Services System.Xml System.Xml.Linq And just to be clear, the DLL is a .NET 3.5 app, with the SQL Server being SQL Server 2008. Right off the bat, the "CREATE ASSEMBLY" call fails due to System.Web, which strangely, is The CREATE ASSEMBLE says this error: Msg 10301, Level 16, State 1, Line 1 Assembly 'MyLibrary' references assembly 'system.web, version=, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request. I have tried to use VS 2010, to create a SQL Server DB project, and it point blank does not let one add references to things like System.Web. When trying to use "Add Ref", VS 2010 literally lo

PIvot Table in SSRS 2008

Hi All, Is there any data Region (Pivot) in SSRS 2008 or higher version? Our requirement is to show the report like excel pivot gererated from a OLAP Cube where we can select or unselect any dimesion attribute or measures. Thanks, IndiaSQl  

SSAS Cube & Pivot table

Hello, I am connecting to my SSAS cube through excel Pivot table. I have the following questions: 1) I have a filter for Company. However, instead of selecting multiple companies from a drop down list, I would like to have a range e.g. Company between 100 and 200. We have this "Filter expression and operator" option in SSAS but I dont see in excel. 2) I have 90 users who will be using my cube via pivot tables. How do I automatically have the data in excel refresh automatically such that when the user comes in, in the morning and open the pivot table, they have the refreshed data. Instead of them manually refresh it as it runs the query for a long time. Thanks in advance for all your help and suggestions! KJ

Excel 2007 Pivot table OLAP- SSAS 2008 Error

Hi, I do have a strange issue with Excel 2007 pivot table report; the data source is SSAS 2008 cube. When refresh the excel 2007 report data using the 'Refresh All' or 'Refresh' button available under the Data menu, the below error is thrown. "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." After some analysis, the report works well on the below workaround, 1. Remove all the calculated measures from report (3 measures in this scenario) 2. Remove one row label (dimension attribute) from report But the measures, attributes are valid one from cube and it is required fields in the report. So, understand that removing few items is not at all a solution. The below workaround also works. 1. Check the 'Defer Layout Update' option from the Pivot Table Field List window. 2. Click on the Update button After that Unchecked the 'Defer Layout Update' option and apply the filter, etc in the report. The report works very well. But every time, we need to use the above work around after open the excel report. I have NO clue on this issue. Please help to resolve this issue. Thanks, Jey


Hello EveryOne,               I am making a monthly attendance details of all employees who is working in my company. I am using sql server management studio 2005.       My table is in this format. staffcode   name                                   date            Intime   Outtime Duration                         1               Amit                                  01-08-2010    00:00    00:00    00:00 1              Amit                                   02-08-2010    09:52    20:01    10:09 1&n

using Live Labs Pivot in SharePoint 2010

Hi, Any pointers to get started with PIVOT in SharePoint applications. Want to create application similar to http://www.wssdemo.com/SitePages/LiveLabsPivot.aspx Any pointers will be very appreciated. Thanks!

Excel 2003 pivot table - parent showing grand total than the sum of its children

All, In Excel 2003, I have a product hierarchy, for simplistic sake lets say it goes A > B > C > model nbr.  I have filtered on model nbr 1.  I have the product hierarchy in my pivot table and unit cnt as the measure. Starting at level A in the hierarchy I am showing a total unit of 12000, if I drill down to B it still shows 12000, if I drill down to C it shows 100 and if I drill down to model nbr it shows me model nbr 1 with a unit cnt of 100. So my question is why are my parents not filtering the model nbr, it is taking the total for ALL records that have level A as their parent regardless of the model nbr? This does NOT happen in Excel 2007 or when I browse my cube in SSAS.

"Non-additive" target value in KPI. How to present it in a scorecard or Pivot Table.

Hi.. I would like to have some help in designing the following solution. It musn't be to hard, but i can't figure it out yet.. I have 2 tables: Group and Worker. Table Group has GroupID and GroupTarget. Table Worker has WorkerName, GroupID, WorkerTarget and Amount. The relation between tables is GroupID, as each worker belongs to a group. Now I want to take this to a pivotTable, to present KPI for each worker, and the value of the indicator is calculated comparing the Amount vs the WorkerTarget. Also I setup a single hierarchy in the PivotTable, where the parent is the GroupID, and when displayed, you can see all the Workers belonging to that group. Now the problem is, Everything is fine when I want to see the actual group total amount value, as I just have to SUM all the Amounts of each Worker, and I can see the GroupAmount. But.. ¿How can I display the GroupTarget in the PivotTable in the same column as the WorkerTarget? I don't mind a solution using SSAS or PerformancePoint, but my idea is in SSAS to put some kind of formula in the KPI target, so when I'm in a level of the hierarchy, the target is WorkerTarger, and when I'm in the parent, the target is GroupTarget. Is there some kind of formula like this??   If not, any other idea will be much apreciated.. Thanks a lot.. regards.. Canario O.

Howto set the value of webgridcolumn.format in the class file?

About:  I want to define format string of the webgridcolumn by myself.Example:There is a ID column in a webgrid ,i want the cell of this column filled the data like "<a href="/Default/@ID">@ID</a>"Code: Microsoft.WebPages.Helpers.WebGrid mygrid = new Microsoft.WebPages.Helpers.WebGrid(tarobjs,canPage:Convert.ToBoolean(data.Groups["enablepager"].Value), rowsPerPage: Convert.ToInt32(data.Groups["pagesize"].Value));                 Microsoft.WebPages.Helpers.WebGridColumn[] columns = new Microsoft.WebPages.Helpers.WebGridColumn[data.Groups["col"].Captures.Count];                                 for (int i = 0; i < columns.Length; i++ )                 {                     columns[i] = mygrid.Column(columnName: data.Groups["col"].Captures[i].Value, header: data.Groups["headertitle"].Captures[i].Value);                                      }                 HtmlString myhtml = (HtmlString)mygrid.GetHtml(columns:colu

How to change connection string of a pivot table pointing to SSAS 2005 cube using excel 2003?

Hi All,I am not sure if I should have posted this query to Excel 2003 forum. But posting it here as it applies to SSAS 2005 as well.Ok, let me give the background before I tell the actual problem.We have users on ABC domain and the SSAS server is also on ABC domain. Users on this domain can acess the excel pivots by connecting to cube to browse the data. They leave the Userid & password field blank while they setup the connection string and it works fine. Thanks to windows authentication that takes the credentials of user logged in. Let's say I have two users A and B, they login to ABC domain with their own windows ids.  Now when user A creates a excel file having a cube pivot and then sends this file to user B, user B can refresh and modify the same excel file (he can select new measures to pivot, new hierarchies in filters and so on).Now, let's say I have another user, user C. He has excel 2003 installed on his PC and cannot migrate to excel 2007. He is on different domain XYZ but have a valid windows userid on domain ABC. The domain ABC & XYZ can not be setup to have trusted relationship. Now, when user A sends the same excel file to user C. When user C opens the file and try to refresh it or try to modify the pivot by selecting/deselecting any elements, he gets below error prompt:" An error was encountered in the transport layer." and "Errors in the

SQL PIVOT Table to regular sql table.

Hello, I have a fairly complex SP which produce a pivot table with dynamic columns. I was wondering if there is a way to copy that result table to another one with same columns/rows/data, except that the second table is actually stored in the database rather than just produced at runtime. I tried something like: SELECT * FROM (runtime_generated_PIVOT_Table) INTO (new_Table) but that did not work.... (probably didn't work because of the PIVOT table query, not sure)Steve

Create an Excel contraining PIVOT feature in ASP.NET

Hi, We have to generate an excel sheet with pivot table from ASP.NET Web page. Please advice me with some approaches. Thanks in advance. Ratheesh 

Retrieving users from mapping/pivot table

I have three tables.CustomProfileUserID (pk)DisplayNameCompanyIDTeamsTeamRecordID (Pk)TeamIDCompanyIDEmployeesInTeamsUserIDTeamIDI want to assign employees to teams via a gridview control with a templated checkbox.I have a dropdownlist control to select the TeamID.I am having problems with the select statement.The gridview needs to select employees that are not assigned to any teams as well as assigned to other teams. An employee can be assigned to many teams.What I have been trying is:cp.UserID, cp.DisplayName, et.TeamIntID, cp.CompanyID FROM            EmployeesInTeams AS et CROSS JOIN                          CustomProfile AS cp WHERE        (et.TeamIntID <> @TeamIntID) AND (NOT EXISTS                              (SELECT        TeamIntID, UserID                                FROM            EmployeesInTeams                                WHERE        (cp.UserID = UserID))) AND (cp.CompanyID = @companyid)Select cp.UserID, cp.DisplayName, et.TeamIntID, cp.C
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