converting columns into rows
I've got these columns [in a single table]: partnumber bin description store manufacturer I need to output the store and manufacturer as rows for each partnumber, bin, description column. ...
pivot
SQL Dynamic Pivot - how to order columns
I'm working on a dynamic pivot query on a table that contains: OID - OrderID Size - size of the product BucketNum - the order that the sizes should go quantity - how many ordered The size column contains different sizes depending upon the OID. So, using the code found here, I put this together: DECLARE @listCol VARCHAR(2000) DE...
SQL Rows to Columns
Hi, I have a table and want to transpose its rows to columns, similar to a pivot table but without summarising. For example I have the following tables: Question --QuestionID --QuestionText Response --ResponseID --ResponseText --QuestionID Basically I want to be able to create a dynamic table something like: Question 1 Text | Ques...
SQL Query to get Matrix reporting
My "FeedbackSummary" table structure is GivenBy varchar(50) GivenTo varchar(50) Points decimal(15, 2) Sample Data Alice Janet 4.50 Alice Bruce 3.50 Bruce Alice 2.87 Bruce Janet 4.75 Janet Alice 5.45 Janet Bruce 3.78 What I am trying to achieve GivenBy_GivenTo Alice Bruce Janet Alice NULL 3.50 4.50 Bruce 2.87 ...
SQL Server PIVOT - Multiple Aggregates
Given the following result set: --------------------------------------------------------- CustomerID Service TransType SubTotal Tax NetTotal --------------------------------------------------------- 106 A CREDIT 12.52 - 12.52 106 A CREDIT 10.07 - 10.07 106 ...
Updating an UNPIVOTted SQL Table
Okay, I've been beating my head against this, and I'm sure I'm just missing something obvious, but... I have a table in a customer's database, that's basically: Item_Set_Key int Item_1 bit Notes_1 nvarchar(80) Item_2 bit Notes_2 nvarchar(80) Item_3 bit Notes_3 nvarchar(80) ... There's 99 items in ea...
How can I return only one resultset from a pivot query in sql server?
Hello, I have the following T-SQL Pivot query. SELECT AccountNumber, EventID, CreateDate, [ITEMBOOK] AS ITEMBOOK, [POSTER] AS POSTER FROM (SELECT ProductID, Quantity, EventID,AccountNumber,CreateDate FROM #tmpStartupItems) ps PIVOT ( SUM (Quantity) FOR ProductID IN ( [ITEMBOOK], [POSTER]) ) A...
SQL Server : Pivot table
Hi All create PROC [dbo].[Sample] @fromDate datetime, @toDate datetime, @office varchar(30) AS declare @char varchar(200) DECLARE @Temp TABLE (ID int, Name varchar(50), Countt int, Reason varchar(20)) INSERT INTo @Temp (ID, Name, Countt, Reason) SELECT DD.ID, O.Name, Count(DD.Reason) Countt, convert(varchar,DD.Reason) Reason FROM ...
PIVOT syntax in SQL Server 2005
Pulling my hair out with this query. Maybe some of the experts here can see what I'm doing wrong? I have a TimeSheetTime Table as follows: CREATE TABLE TimeSheetTime( TimeSheetTimeID int IDENTITY(1,1) NOT NULL, TimeSheetItemID int NOT NULL, OffsetToEntryDate tinyint NOT NULL, Hours float NOT NULL ) This is populated w...
TSQL Pivot without aggregate function
I have a table like this... CustomerID DBColumnName Data -------------------------------------- 1 FirstName Joe 1 MiddleName S 1 LastName Smith 1 Date 12/12/2009 2 FirstName Sam 2 MiddleName S 2 LastName Freddrick 2 ...
Help With SQL - Combining Two Rows Into One Row
Hello Everyone, I have an interesting SQL problem that I need help with. Here is the sample dataset: Warehouse DateStamp TimeStamp ItemNumber ID A 8/1/2009 10001 abc 1 B 8/1/2009 10002 abc 1 A 8/3/2009 12144 qrs 5 C 8/3/2009 12143 ...
SQL Server 2005 Pivot Table question
I have an Entity-Value set of tables, and would like to pivot the results. Here's the effect that I'm looking for, except you see that the SELECT stmt using Common Table Expressions isn't working exactly right yet. My question is: Am I on the right track, or is there some sort of easy pivot command? USE TempDB Declare @Person TABLE( Per...
How to build a consolidated pivot table when the source data contains column headings that are dates?
I have a customer who is currently using Excel to do their staff planning. They have many workbooks for different projects and each project contains 1 or more sheets containing the actual staffing data: The customer wants to consolidate all of the data from all of these many sheets and workbooks into a single pivot table. A 'consoli...
Cross Tab - Storing different dates (Meeting1, Meeting2, Meeting 3 etc) in the same column
I need to keep track of different dates (dynamic). So for a specific Task you could have X number of dates to track (for example DDR1 meeting date, DDR2 meeting date, Due Date, etc). My strategy was to create one table (DateTypeID, DateDescription) which would store the description of each date. Then I could create the main table (ID, ...
SQL Server - Dynamic PIVOT Table - SQL Injection
Sorry for the long question but this contains all the SQL I've used to test the scenario to hopefully make it clear as to what I'm doing. I'm build up some dynamic SQL to produce a PIVOT table in SQL Server 2005. Below is code to do this. With various selects showing the raw data the values using GROUP BY and the values in a PIVOT as I...
ssrs combining rows into result set
So I have a sql server 2005 query that returns results like so: Address | Doctor ----------------------- 1 Dr A 1 Dr B 1 Dr C 2 NULL 3 NULL 4 Dr D 4 Dr E 5 Dr F What I want is to get the output so that when I render the report I have them grouped together: Dr A Dr B Dr C...
Best way to flatten/denormalize SQL lookup tables?
I have a bunch of tables like this: Lookup_HealthCheckupRisks ------------ ID Name 1 Anemia 2 Anorexic 3 Bulemic 4 Depression ... 122 Syphilis PatientRisksOnCheckup ------------------ ID CheckupID RiskID 1 11 2 2 11 3 3 12 1 4 14 1 5 14 3 ... But I need a flattened version, like th...
Pivot table Values are now screwy
In Excel 2003, I have a macro that accidentally tried selecting a page field item (New York) that didn't exist. This renamed the currently selected page field (Alabama) to the value I tried to select (New York). I now have 3 or 4 values that are now wrong. Is there a way to refresh these pivot table values to the correct values without ...
How to pivot in SQL
I am not sure if this would be called pivoting. Data in my SQL 2005 table [CustromerRoles] is as such: CustId RoleId 2 4 2 3 3 4 4 1 4 2 [Roles] table: RoleId Role 1 Admin 2 Manager 3 Support 4 Assistant I want to create a view such that: SELECT * FROM [MYVIEW] will...
SQL Pivot MIN( COUNT (
I have a complicated MS SQL 2005 query with a PIVOT in it... I have data like this : Data ( clients left join visits ): ClientID VisitID ServiceID 1 34 5 1 36 2 1 36 5 1 45 2 1 48 2 1 60 2 2 44 1 3 48 2 3 78 3 3 79 2 ...