pivot

SQL Server PIVOT examples?

Trying to find some simple SQL Server PIVOT examples. Most of the examples that I have found involve counting or summing up numbers. I just want to pivot some string data. For example, I have a query returning the following. Action1 VIEW Action1 EDIT Action2 VIEW Action3 VIEW Action3 EDIT I would like to use PIVOT (if even possibl...

Transpose a set of rows as columns in SQL Server 2000

Is there any facility of transposing rows to columns in SQL Server (it is possible in MS-Access)? I was befuddled because this facility is available in MS-Access but not in SQL Server. Is it by design that this feature has not been included in SQL Server? ...

How to join the newest rows from a table?

I frequently run into problems of this form and haven't found a good solution yet: Assume we have two database tables representing an e-commerce system. userData (userId, name, ...) orderData (orderId, userId, orderType, createDate, ...) For all users in the system, select their user information, their most recent order information w...

Pivot Table and Concatenate Columns - SQL Problem

I have a database in the following format: ID TYPE SUBTYPE COUNT MONTH 1 A Z 1 7/1/2008 1 A Z 3 7/1/2008 2 B C 2 7/2/2008 1 A Z 3 7/2/2008 Can I use SQL to convert it into this: ID A_Z B_C MONTH 1 4 0 ...

SQL command to LINQ (pivoting)

Hello! I'm moving DB from MySQL (used ODBC) to MS SQL and I want to "translate" SQL queries to LINQ. Can someone help me with this (it should SUM Charge column for every location and group result by months): SELECT sum(case when Location="Location1" then Charge else 0 end) as Location1, sum(case when Location="Location2" then Charge el...

PIVOT in sql 2005

I need to pivot one column (Numbers column). example need this data: a 1 a 2 b 3 b 4 c 5 d 6 d 7 d 8 d 9 e 10 e 11 e 12 e 13 e 14 Look like this a 1 2 b 3 4 c 5 d 6 7 8 9 e 10 11 12 13 14 any help would be greatly appreciated... ...

"Pivoting" a Table in SQL (i.e. Cross tabulation / crosstabulation)

I'm working on trying to generate a report from a couple of database tables. The simplified version looks like this Table = Campaign CampaignID Table = Source Source_ID | Campaign_ID Table = Content Content_ID | Campaign_ID | Content_Row_ID | Content_Value The report needs to read like this: CampaignID - SourceID - ContentRow...

SQL Server 2005 Pivot on Unknown Number of Columns

I am working with a set of data that looks something like the following. StudentName | AssignmentName | Grade --------------------------------------- StudentA | Assignment 1 | 100 StudentA | Assignment 2 | 80 StudentA | Total | 180 StudentB | Assignment 1 | 100 StudentB | Assignment 2 | 80 Student...

Accessing dynamically created stored procedure from LINQ

Hello! I'm pivoting data in MS SQL stored procedure. Columns which are pivoted are dynamically created using stored procedure parameter (for exampe: "location1,location2,location3,") so number of columns which will be generated is not known. Output should look like (where locations are taken from stored procedure parameter): OrderTi...

SQL query help with bridge table

I'm working with a existing database and trying to write a sql query to get out all the account information including permission levels. This is for a security audit. We want to dump all of this information out in a readible fashion to make it easy to compare. My problem is that there is a bridge/link table for the permissions so there a...

How can I use an SQL Pivot for this?

I have a data set that is organized in the following manner: Timestamp|A0001|A0002|A0003|A0004|B0001|B0002|B0003|B0004 ... ---------+-----+-----+-----+-----+-----+-----+-----+----- 2008-1-1 | 1 | 2 | 10 | 6 | 20 | 35 | 300 | 8 2008-1-2 | 5 | 2 | 9 | 3 | 50 | 38 | 290 | 2 2008-1-4 | 7 | 7 | 11 | 0 | 30 | ...

How to convert fields into rows through SQL in MS Access 2007 or MS SQL Server 2005

I have a legacy MS Access 2007 table that contains 52 fields (1 field for each week of the year) representing historical sales data (plus one field for the year actually). I would like to convert this database into a more conventional Time/Value listing. Does anyone knows how to do that without writing queries with 52+ explicit paramete...

Another LINQ Pivot Problem - Convert SQL Script to LINQ

There are a few questions on SO already regarding LINQ pivots and while a couple of them outline my exact problem, I can't successfully translate them to a working solution. I feel that this is mostly due to a join in my tables. So for the benefit of all the LINQ junkies out there who love a problem, here's another puzzle for you to wo...

Cross join (pivot) with n-n table containing values

I have 3 tables : TABLE MyColumn ( ColumnId INT NOT NULL, Label VARCHAR(80) NOT NULL, PRIMARY KEY (ColumnId) ) TABLE MyPeriod ( PeriodId CHAR(6) NOT NULL, -- format yyyyMM Label VARCHAR(80) NOT NULL, PRIMARY KEY (PeriodId) ) TABLE MyValue ( ColumnId INT NOT NULL, PeriodId CHAR(6) NOT NULL, Amount DECIMAL(8, 4) NOT NU...

How to pivot my data with SQL Server 2005

I have the following data How do I transform it (with SQL Server 2005) into the following format? I have a example solution that I came up with but it seems a little clunky. It smells perhaps? DECLARE @ProductLanguage TABLE ( [PRODUCT_ID] int , [LANGUAGE] varchar(50) ) INSERT INTO @ProductLanguage ([PRODUCT_ID],[LANGUAGE...

Display a single record (row) as a single column

I'm looking for ways to display a single row of data as a single column (with multiple rows). For example, FieldA FieldB ------- --------- 1 Some Text [row] Header Value [col] ------ ------ FieldA 1 [row1] FieldB SomeText [row2] Is there a way to do this with SQL Server 2005? ...

Error using the Pivot transformation in MS SQL Server 2005

Hi, I have the following table which I'm pivoting: (ID, Name, Value) 1, 'Task ID', 123 1, 'Language', 'Java' 2, 'Task ID', 456 I have set the PivotKey to be the Name column, and the PivotedValue to be the Value column. For each of my new columns (Task ID and Language) I have set their PivotKeyValue to be 'Task ID' and 'Language' re...

Loop through PageField in OLAP Cube [PivotTable]

Hi, I'm trying to write a VBA script that will draw buttons beside the PageFields in a Pivot Table, these buttons will loop through the values in the PageField. I had this working for a regular Pivot Table, but I've been asked to adapt it for an OLAP Cube (External Data Source) and I can't work out how to find the values for a Member usi...

Doing a pivot using XSLT

I have an xml file like this: <root> <item> <name>one</name> <status>good</status> </item> <item> <name>two</name> <status>good</status> </item> <item> <name>three</name> <status>bad</status> </item> <item> <name>four</name> <status>ugly</status...

SQL Server 2005, turn columns into rows.

I am trying to turn a table 90 degrees: make columns rows. No PIVOT is allowed since PIVOT requires aggregate functions. Example: I have a table with the columns: ID int, ISO char(2), Text varchar(255). So I have this: ID ISO Text -- --- ---- 1 DE Auto 2 EN Car I'd like to get the following: ID EN DE -- --- ---- 1 Car ...