pivot

Flattening XML in SQLXML

I have this XML in T-SQL: <Elements> <Element> <Index>1</Index> <Type>A</Type> <Code>AB</Code> <Time>1900-01-01T10:21:00</Time> </Element> <Element> <Index>2</Index> <Type>M</Type> <Code>AL</Code> <Time>1900-01-01T10:22:00</Time> </Element> </Elements> And...

Oracle PIVOT, twice?

I have been trying to move away from using DECODE to pivot rows in Oracle 11g, where there is a handy PIVOT function. But I may have found a limitation: I'm trying to return 2 columns for each value in the base table. Something like: SELECT somethingId, splitId1, splitName1, splitId2, splitName2 FROM (SELECT somethingId, splitId ...

sql combining two queries and truncating date

I am trying to truncate dates to only get year/month as opposed to the form they are in which is year/month/day.time What I want to do is count all of the cars that sold each month and all of the suvs that sold each month, having something like: // counts cars select SellDate, count(*) from category where machineIdentification =...

SQL Pivot with multiple columns

Hi Guys, Need help with the pivot clause in sql server 2008. I have a table with this info: Weekno DayOfWeek FromTime ToTime 1 2 10:00 14:00 1 3 10:00 14:00 2 3 08:00 13:00 2 4 09:00 13:00 2 5 14:00 ...

display rows as colum

I want to display rows as column in SQL Server. My table looks like this: images_id item_id images_name ------------------------------- 1 1 image1.jpg 2 1 image2.jpg 3 1 image3.jpg 4 2 image4.jpg 5 2 image5.jpg 6 2 image6.jpg I'd ...

T-SQL for changing data from one table and insert into another table

My base table is like: ColumnA|ColumnB --------------- A | C1 A | C2 A | C3 B | C1 B | C3 C | C4 I want to read records from the base table and write it into the below table: ColumnA | C1 | C2 | C3 | C4 ---------------------------- A | Y | Y | Y | N B | Y | N | Y | N C | N ...

Can I do pivot with mysql 5.1?

Can I do pivot with mysql 5.1? (or emulate it somehow). ...

replace null values in sql pivot

I have the following query : SELECT * FROM Table1 PIVOT ( SUM(Value) FOR [Period] IN ([06/1/2007],[07/1/2007]) ) AS p Some of the rows returned are null but i want to replace them with 0. I've tried SUM(ISNULL(Value,0)) as Val but it's not working. ( it's saying incorrect syntax) ...

Like PIVOT but values not discrete

A table lists multiple events where each event has four attributes (columns), call them A, B, C, P It would be simpleto pivot to get a table with columns for A, B, C, P=1, P=2, P=3, etc. However, I need the columns to be A, B, C, P<1, P<2, P<3, etc. In other words, if a row of the "simple" way were X, Y, Z, 7, 3, 5, 2 then what I actu...

Pivot a one row column T-SQL

I have a one row table returned from a query that looks something like this [Date1] [Date2] [Date3] [Date4] [Date5] [Date6] and I want all the Dates to stack up like this [Date1] [Date2] [Date3] [Date4] [Date5] [Date6] How would I go about doing this without a bunch of separate queries and union statements? I have tried playing a...

Convert colums to rows in SQL

I need to write a query which takes rows and converts it into columns - here's my table: Count fname lname id ----------------------------- 1 abc def 20 2 pqr 20 3 abc xyz 20 4 xyz xyz 20 1 abc def 21 1 pqr xyz 22 2 abc abc...

Combining multiple rows into one row

Hello everyone. I have a table containing user-account permissions and I'm trying to write a query to return one row for each user-account combination. Here is what I have. CltKey AcctKey TranTypeID Access 10 2499 10 0 10 2499 11 1 10 2499 12 1 10 2764 10 1 10 ...

SQL Query Problem -- Are Pivots Necessary?

Given the following data: CardholderID Source DateTime -------------- ---------------------------------------- ----------------------- 3 2nd_Flr_Ramp_Out_Reader 2010-06-30 13:58:42.410 3 2nd_Flr_Ramp_In_Reader 2010-06-30 13:44:22.417 3 ...

SQL - How to transpose?

I have something similar to the following table: ================================================ | Id | UserId | FieldName | FieldValue | =====+========+===============+================| | 1 | 100 | Username | John Doe | |----+--------+---------------+----------------| | 2 | 100 | Password | pass123! ...

Is there a way to pivot a customer ID and a their most recent order dates?

I have a query that gives me all customer's and their last three order dates. EX: CustomerId DateOrdered 167 2006-09-16 01:25:38.060 167 2006-09-21 13:11:53.530 171 2006-08-31 15:19:22.543 171 2006-09-01 13:30:54.013 171 2006-09-01 13:34:36.483 178 2006-09-04 11:36:19.983 186 2006-09-05 12:50:27.153 186 2006-09-05 12:51:08.513 I want...

Combine multiple rows in table into 1 resultset row

I have a table that stores records like this: name | stat | value -------------------------- object1 | stat1 | val1 object1 | stat2 | val2 object1 | stat3 | val3 object1 | stat4 | val4 But I would like to query the data so it returns rows like this name | stat1| stat2| stat3| stat4 ------------------------------------- ob...

SQL query to join values from multiple records

I've got a table Employees with employees (say the important fields are ID int, Name varchar(50)) and a table Areas with sales areas (ID int, EmployeeID int, USState char(2)). The sample values are: Employees ID Name 1 Shoeman 2 Smith 3 Johnson Areas ID EmployeeID USState 1 1 NY 2 1 ...

Excel 2007 Pivot Tables Custom calculation?

Hi all, Again I'm wrestling with Excel 2007! I have some data I'm using to generate a Pivot Table and I'd like to divide all of the data by 1000... I know I could go in and change the original data by hand but I'd much rather have a formula in the Pivot table itself that does this for me. Any thoughts? ps: You guys are awesome! ...

mysql query for transposing data

I need to transform the values in my column into different columns. Example data with me currently: id dtime std_time ifInOctets ----------------------------------------------------- 4 1279027201 13:20:01 1.34E+08 1 1279027201 13:20:01 34562 2 1279027201 13:20:01 9.72E+07 4 1279027201 13...

Joining new columns in MySQL One-To-Many

I asked a similar question the other day but it seems no one was able to answer it, and searched the internet for a few days but still fruitless, perhaps I am not asking the question the right way: http://stackoverflow.com/questions/3444718/one-to-many-query-in-mysql So I while try again and maybe word it a bit differently. This is ess...