I know there's got to be a way to do this, but for the life of me I can't figure it out: I have 3 tables I want to join together (simplified to illustrate): users uid mail 1 [email protected] 2 [email protected] 3 [email protected] profile_fields fid name label 1 full_name Full Name 2 phone Phone profile_values uid fid value 1 1 ...
Hi, Using Code to change the pivot cache of the pivottable specifically: Dim pc As PivotCache, pt As PivotTable Set pt = Sheets("Sheet1").PivotTables("PivotTable1") Set pc = pt.PivotCache With pc .CommandType = xlCmdSql .CommandText = "Select * From Query1 Where Query1.Date BETWEEN #" & startval & "# AND #" & endval & "#" .Refres...
How can I get the second column with the same column name using OPENXML in MSSQL 2005? Here is the result set that I plan to get. columnData1 columnData2 A B C D E F DECLARE @hDoc int, @xmldata varchar(max) SELECT @xmldata = '<?xml version="1.0" encoding="utf-8" ?> <reportResponse> <reportDataRow rowNum="1"> <columnData colNum...
I was using mysql just fine until I recently switched one of my rails apps to heroku and had to change over. Almost everything works as expected except I have one query which does something totally funky. This is the postgres, but under mysql it is mostly identical except for the EXTRACT DOW and some group by additions, but that isn't ...
I've SQL Server 2005 table COMPETITOR (Id, Title, cDate, Price) I want to format the query so that its view in Gridview will be like this: Please help me writing the sql query. ...
Yes I've tried the code. My requirement is that user inputs Year and Month & prices are shown date-wise in columns for that year and month, with first column as CompetitorID. I want my result like: Competitors | day1 | day2 | day3 | day4 ..........................|day31 ============================================= competitor 1|Price ...
I have a table tblUser , there is userId,firstName,LastName,Mobile,.......,QuestionID. Another table it's name tblResults There is questionID,Question,choiceID,choice,......... EG: tblUser userID - FirstName- LstName -Mobile ... ... ... - QuestionID - ChiceID 001 - xx - yy - 03212 - ...
I have a table like this (of course there are many more values but you get the idea): ID Name --- ---- 1 A 1 B 2 C 3 D 4 A 4 D 4 E 4 F 4 G 4 H I want to write a query that would output this, given that an ID cannot have more than 6 names. ID Name1 Name2 ...
Hello all, I have an interesting conundrum which I believe can be solved in purely SQL. I have tables similar to the following: responses: user_id | question_id | body ---------------------------- 1 | 1 | Yes 2 | 1 | Yes 1 | 2 | Yes 2 | 2 | No 1 | 3 | No 2...
I've got an Occurrences table that contains one row for each time a user took an action. A user take an action multiple times per day. It looks like this: Date Username ------ -------- 1/1/9 User1 1/1/9 User1 1/1/9 User2 1/2/9 User1 1/2/9 User3 1/3/9 User1 1/3/9 User1 1/3/9 User1 1/3/9 User2...
update: what I was calling coalesce I should have been calling pivot. I'm extracting some daily usage counts from a log table. I can easily get this data one row per date/item, but I would like to pivot coalesce the columns into a single row. e.g., I have: date item-to-be-counted count-of-item 10/1 foo 23 10/1 ...
I have a table that looks like this: Month Site Val 2009-12 Microsoft 10 2009-11 Microsoft 12 2009-10 Microsoft 13 2009-12 Google 20 2009-11 Google 21 2009-10 Google 22 And I want to get a 2-dimension table that gives me the "Val" for each site's month, like: Mont...
Hi! When the query is like this there is no problem This works: TRANSFORM Count(Aktivitet.ID) AS AntalförID SELECT Aktivitet.region, Sum(Aktivitet.antalMän) AS [Antal Män], Sum(Aktivitet.antalKvinnor) AS [Antal Kvinnor] FROM Aktivitet GROUP BY Aktivitet.region PIVOT Aktivitet.aktivitetstyp But when I add this line I get into trouble...
I have a simple query like this.. USE AdventureWorks; GO SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.Product GROUP BY DaysToManufacture; DaysToManufacture AverageCost 0 5.0885 1 223.88 2 359.1082 4 949.4105 A simple piv...
Hi, Is there any way to create a pivot and/or cross-tab query using for SQLite using any of the available .NET wrappers? Thanks. ...
Hi, I have the following SQL query: SELECT CountryID, [10201] AS CountryGDPPerCapita, [10677] AS LifeExpTotal FROM ( SELECT CountryID,FieldID,numeric FROM globaledge.dbo.DIBS_Data WHERE CountryID IN (3,5) AND FieldID IN (10201,10677) AND year = 2002 ) SourceTable PIVOT ( MAX(numeric) FOR FieldID IN ([10201...
This is probably a very simple question. All I want to really do is to make the column into a row whose data type is a bit. SUM, MIN, MAX doesn't work on bits. COUNT works but I really don't want to count. I just want to move all the stuff from columns into rows like if I took a pair of scissors, cut the information and moved it -90...
I have the following Quicksort that always chooses the first element of the subsequence as its pivot: void qqsort(int array[], int start, int end) { int i = start; // index of left-to-right scan int k = end; // index of right-to-left scan if (end - start >= 1) { // check that there are at least two elements to sort ...
I would like to extract some data from three tables in a SQL Server 2005 database. While this can surely be done in code, it seems like this could be done reasonably well in SQL (bonus points for LINQ!). Basically, I would like to know for each month how many calls and meetings each employee has held with each of our clients. Somethin...
There is a question here in stackoverflow with the same title but that is not what I am looking for. I have a table like the one below Name | Count ---------------- Chery | 257 Drew | 1500 Morgon | 13 Kath | 500 Kirk | 200 Matt | 76 I need to trasform this result set into something like this Chery | Drew...