pivot

Dynamic Pivot (in SQL Server 2005)

I'm writing a stored procedure for Microsoft SQL 2005 and I want to create a dynamic SQL Pivot: SELECT Book.ISBN, Book.Name StockMutation.StockLocation FROM Book INNER JOIN StockMutation AS sm ON Book.bookid = sm.bookid PIVOT ( COUNT(sm.NumberOfBooks) FOR sm.StockLocation IN (...) ) Preferable I want to r...

SQL 2005 Pivot Query

Hello all. I am trying to get a grip on the pivot query syntax. I have about 20 fields worth of aggregate data all associated with 1 field, which is the category for those 20 fields (by fields I mean columns). It looks like this Category1 column1 column2 column3 Category2 column1 column2 column3 and so on.....

Excel VBA: Update Pivot Souredata

I tried to record the code to update a pivot sourcedata which gave me this: ActiveSheet.PivotTableWizard SourceType:=xlExternal, _ SourceData:=QueryArry1, _ Connection:=Array( _ Array("ODBC;DSN=MS Access Database;DBQ=" & DBDir & "\" & DBName & ";"), _ Array("DefaultDir=" & DBDir & ";DriverId=25;FIL=MS Access;MaxB...

How do I create a view where column names are defined from query return values

First, my table layout: tblEquippedItems: <-(table name) slotid (FK), itemid (FK), charid (FK) <-(attributes) So I have this table that describes the items a character has equipped. Slot id refers to the item slot where the item, denoted by itemid resides. Charid describes the character that has these items equipped. What I'd like to ...

Excel VBA: "application-defined or object-defined error"

In Excel 2003 I'm getting a Runtime error 1004: "application-defined or object-defined error" on the last line of this code (commandtext = abc) Sub SCommandTxt() Dim abc as string abc = Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText = abc End Sub T...

SQL Column to row conversion

I have a table with the following structure: Col1 Col2 --------------- 1 2 3 4 I need to Query in the such a way that the output should be like: ColumnName | ColumnValue ---------------------------- Col1 1 Col2 2 Col1 3 Col2 4 Any help in this will be greatly appreciated. Th...

KeyNotFoundException, but not when debugging.

I've been building an extensions library, and I've utilised a great extension method found at http://www.extensionmethod.net for inclusion. In my unit test (using NUnit 1.5.2), I've come across an interesting issue. Firstly, lets look at the code: /// <summary> /// Groups and aggregates the sequence of elements. /// </summ...

Trouble creating a recordset from a SQL Server 2005 query with PIVOT

Here's the output code from my stored proc: SELECT * FROM ( select q.ccypair, q.vega, t.label from #parallel q LEFT JOIN TPRR_vega_weights t ON q.Tenor = t.Tenor ) a PIVOT ( Sum(Vega) for a.label in ([t1],[t2],[t3],[t4],[t5],[t6],[t7],[t8],[t9],[t10],[t11],[t12],[t13],[t14],[t15],[t16],[t17],[t18]) )p order b...

Combining two T-SQL pivot queries in one

Suppose you had this table: CREATE TABLE Records ( RecordId int IDENTITY(1,1) NOT NULL, CreateDate datetime NOT NULL, IsSpecial bit NOT NULL CONSTRAINT PK_Records PRIMARY KEY(RecordId) ) Now a report needs to be created where the total records and the total special records are br...

SQL Syntax to Pivot multiple tables

Hi, I have spent the past couple of days working on this and am going around in circles. My question is based around the answer I accepted in this post: stackoverflow question I now have my data moved from a single 400 column table to a much more managable database structure with many thanks to Damir Sudarevic. My database looks li...

SQL Pivot Table

I have a table like this; var1 var2 var3 cats a b b cat1 b b cat1 a cat2 a a a cat3 a a a cat2 and want to get pivot table. I want to count a and b with the category of vars and cats.I am an excel user. I've looked other samples but could not solve th...

dynamic sql pivot table

Hi Sirs, i hope you can help me with the resolution of this task we have. originally we have these tables: hwtype id name 1 router 2 switch hwelement id idhwtype name 1 1 RTR1 2 1 RTR2 3 2 SWT1 hwattributes id idhwtype name 1 1 speed 2 1 IP 3 2 ports hwtypeattri...

CrossTabs SQL Server

Hello Everyone, I am wondering how can I create Cross Tabs queries with SQL Server 2008. I have fields for Job Numbers and Employees and I want to show that how many hours an Employee has worked on the specific job. For sample, this is what I want. link text I tried PIVOT but that didn't work for. Later I want to export this data to EX...

Convert Database Rows into Columns

I need to convert Database rows into columns and show the result in Gridview. My DB is as follows: ID Hotel cDate Price ----------------------------------------------- 1 Hotel1 12/22/2009 12:00:00 AM 15.0000 2 Hotel2 12/22/2009 12:00:00 AM 25.0000 3 Hotel3 12/22/2009 12:00:00 AM 60.0000 4 Hotel4 12/22...

Get a single parent with all children in a single row

Let's asume I have a parent-child structure setup in SQL (server 2005): CREATE TABLE parent (Id INT IDENTITY PRIMARY KEY, Name VARCHAR(255)) CREATE TABLE child (Id INT IDENTITY PRIMARY KEY, parentId INT, Name VARCHAR(255)) insert into parent select 'parent with 1 child' insert into parent select 'parent with 2 children' insert into ch...

MySQL counting occurrence of values in a field

I've got a MySQL table containing 115 fields like these: v1q1 v1q2 v2q1 v2q2 v3q1 v3q3 (...) The records all contain one of the value 0, 1, 2, 3 or 4. Now I need to count for every field the number of records containing a 0, the number of records containing a 1, the number of records containing a 2,... (until 4). How I can acco...

Help with PIVOT

I have a table as follows Name | Words A words for A1 here B words for B1 here C words for C1 here A words for A2 here B words for B2 here C words for C2 here I want to pivot the above table to get the following result A ...

SQL Server PIVOT Column Data

Hi, I have a table with data as given below: DATE Price ---------- ------ 31/12/2009 10 31/12/2009 11 31/12/2009 12 30/12/2009 20 30/12/2009 21 30/12/2009 22 29/12/2009 30 29/12/2009 32 29/12/2009 31 I want to convert this data as given below: 31/12/2009 30/12/2009 29/12/2009 ---------- ---------- ------...

How do I combine two queries (union all) into one row?

I have a tricky situation in trying to get information from multiple queries into a single row. Consider the following table: CpuUage: Time time Group char(10) Subsys char(4) Jobs int Cpu int holding the following data: Time Group Subsys Jobs Cpu ----- ------ ------ ---- --- 00:00 group1 ...

SQL Pivot question

I'm having a hard time getting my head around a query im trying to build with SQL Server 2005. I have a table, lets call its sales: SaleId (int) (pk) EmployeeId (int) SaleDate(datetime) I want to produce a report listing the total number of sales by an employee for each day in a given data range. So, for example I want the see all sa...