FAQ Database Discussion Community


List mysql row values horizontally without pivoting

mysql,sql,stored-procedures,pivot
In my table I have following columns --loannumber--disbid--username--docs-- -------------------------------------- ---1234567 -- 430 -- jhon -- NCN -- -- same ---- 425 -- sam -- NPC -- -- same -- 455 -- clin -- Mis -- -- 4567891 -- 666 -- some1 -- NCN -- -- same -- 777 -- some2...

(Un)pivoting tables on SQL

sql-server,pivot
I'm trying to create a matrix with historical data for some parameters I have in a table. The matter is, when I try to pivot it, won't work. Using the following code SELECT concept, [1] '1993', <...>, [23] '2015' FROM (SELECT concept, YEAR(begin_date) as year, value FROM params p WITH(NOLOCK))...

What is the specific use of Pivot table in SQL? [closed]

mysql,sql,sql-server,pivot
Im curious what is the distinct use of pivot table in database? I mean, how is it apply in real life.

SQL QUERY, 2 Summed Values (Columns) with same grouped by except that years are different

sql,sql-server,sql-server-2008,pivot
SQL Newbie here. I would like to ask for help from anyone regarding getting the sum of 2 values based on multiple groups but the only difference is the years. Please see my sample below: Database: ============================================= NAME COUNTRY CITY YEAR VALUE ============================================= Alpha Philippines Manila 2015 5 Beta Philippines...

Building totals based on condition in another row

tsql,data,pivot
Really struggling to understand the best way of doing this... I have a table of data **StudentID AssessmentCode ResultGroup Result** 46933 12ENG IBLevel HL 46933 12ENG Mark 6 46933 12ECO IBLevel HL 46933 12ECO Mark 5 46933 12GEO IBLevel SL 46933 12GEO Mark 6 46933 12LAN IBLevel HL 46933 12LAN...

Laravel 5: synching an extra field via pivot

php,mysql,laravel,pivot,sync
User model: public function positions() { return $this->belongsToMany('App\Position')->withPivot('company_id')->withTimestamps(); } Positions model: public function users() { return $this->belongsToMany('App\User')->withPivot('company_id')->withTimestamps(); } On form submission I have two arrays: $allPositionIds array:3 [ 0 => 98 1 => 99 2 => 100 ] $allCompanyIds array:3 [ 0 => 129 1 => 130 2 => 131...

Pivot Table Using Linq

c#,linq,group-by,pivot,pivot-table
I have a table like this .(VisitType is dynamic) PersonelId VisitDate VisitTypeId 1 2015-02-24 A 2 2015-02-23 S 2 2015-02-24 D 4 2015-02-22 S 2 2015-02-22 A 2 2015-02-22 B 3 2015-02-23 A 1 2015-02-23 A 1 2015-02-24 D 4 2015-02-24 S 4 2015-02-22 S 2 2015-02-22 S 3 2015-02-24...

Automaticlly attach to pivot table in Laravel 5

php,eloquent,pivot,relational-database,laravel-5
I currently have a Users to Groups Relationship (ManyToMany) with a pivot table group_user. I want the user to be able to create a group but once creating the group, how do I make it, that the creator becomes member of this group? Currently I have My Pivot Table (group_user):...

Reshaping & Selecting Pandas from Pivot

python,pandas,pivot,multicolumn
Problem I have the following data frame (note values are just to show format): >>> print df Country Public Private Date 2013-01-17 BE 3389 2013-01-17 DK 4532 681 2013-02-21 DE 2453 1752 2013-02-21 IE 5143 2013-02-21 ES 8633 353 2013-03-21 FR 262 2013-03-21 LT 358 I would like to pivot...

Pivot Return null based on a column values

sql,sql-server,sql-server-2012,pivot
Please consider this Table: Id Year Month Type Value ------------------------------------------ 1 2011 1 T1 1000 2 2012 1 T1 2000 3 2011 1 T2 5000 4 2012 1 T2 500 5 2011 1 T3 11000 6 2012 1 T3 800 I want to create this result : Id Year Month...

Group By and add columns

sql,postgresql,pivot,crosstab
I have a table named 'ward_councillors' and two columns 'ward' and 'councillor'. This table contains a list of all of the councillors and the ward they are responsible for. Each ward has three councillors and what i'd need to do is group by so that I have one distinct record...

Sql Pivot returning 0

sql-server,tsql,pivot
I have a sql query that is meant to count accounts that belong to a certain group. I am using a pivot for that. While the result set looks right, it returns all 0 counts my tsql code: select * from ( select id, accountnumber, group from dbo.dataTable )data pivot...

PIVOT Oracle - transform multiple row data to single row with multiple columns, no aggregate data

sql,oracle,oracle11g,pivot
I have a need to transfer the following data set: in which only the highlighted lines are the one of interest (Tag in ('LN','SN')) as I am only interested in SerialNumber and LotNumber of the products. I would like to convert the data set above into the following data set:...

SQL Multiple rows/records into one row with 3 columns

sql,sql-server-2008,tsql,pivot
I have a similar table to this dummy data, where there are 3 records for one individual. I would like to change that to one record with multiple columns. What is complicating it for me the most is I want the 3 most current Products based off of Date_Purchased Existing:...

Making row values into column values SQL PIVOT

sql,sql-server,pivot
SQL Returns the following results from table +-----+----+-----+----+----+ | PID | PY | QTY | TS | TM | +-----+----+-----+----+----+ | 99 | CT | 1 | 1 | 6 | | 99 | E | 3 | 1 | 5 | | 99 | GR | 4 | 1...

How can I pivot dataset in Google BigQuery?

sql,pivot,google-bigquery
I have a massive dataset with this schema: Customer INTEGER CategoryID INTEGER CategoryName STRING ProjectStage INTEGER NextStepID INTEGER NextStepName STRING NextStepIsAnchor BOOLEAN I heed to get the resulting set where each customer will be only on one row and his/her next steps will be in the columnts like this: Customer...

SQL Pivot Table Dynamic - exclude unused columns

sql-server,pivot
I have the following SQL code that creates a very useful pivot table: Use [A1_20132014] DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + Link_ID + ']', '[' + Link_ID + ']') FROM (SELECT DISTINCT Link_ID FROM A1) PV ORDER BY Link_ID DECLARE @query NVARCHAR(MAX) SET @query...

Pivot each group in Pandas

python,pandas,pivot
Using Pandas I've invoked groupby on my dataframe and obtained the following: >>>grouped = df.groupby(['cid']) for key, gr in grouped: print(key) print(gr) Out: cid price 121 12 121 10 121 9 I want to have each group pivoted like: cid price1 price2 price3 121 12 10 9 What is the...

Pivot SQL table (Rows into Columns)

sql,sql-server,table,pivot,pivot-table
I am currently returning data into a table in my SQL stored procedure. I am trying to pivot the rows into columns, and the columns into rows, but I am struggling to do so as a lot of the tutorials I am following to do this have laid out they're...

SQL transform two records into two columns

sql,oracle,split,pivot
I don´t know if the title is correct but basicaly I need this(result of query): r_e_s_id person_id 89074 161704 89074 161703 89095 161708 89095 161707 68651 129884 68651 129883 81512 161074 81512 161073 To be inserted/updated(in case that there will be r_e_s_id values in table) into table COMM_PROP which has...

Devexpress pivotgrid Customization CustomizationFormStyle

forms,grid,devexpress,pivot,customization
I show fields in customization form. I choose Excel2007 as CustomizationFormStyle but I would like to show just Filter area. I want Column, Row and Data area to be invisible. How can I manage this? pivotGridControl1.OptionsCustomization.CustomizationFormStyle = DevExpress.XtraPivotGrid.Customization.CustomizationFormStyle.Excel2007; pivotGridControl1.OptionsCustomization.CustomizationFormLayout = CustomizationFormLayout.BottomPanelOnly1by4; pivotGridControl1.OptionsCustomization.CustomizationFormAllowedLayouts =...

Dynamically Creating Columns for an Unknown # of Columns

sql-server-2008,tsql,pivot,dynamic-sql
what I'm hoping to accomplish is to dynamically create columns as needed within a stored procedure. My issues lies in the fact that a drive may no incentives, 1 incentive or multiple (2,3,4.etc) incentives assigned to the drive. Screen shot below for example data: I initially used the FOR XML...

How to convert SQL server result set Rows to Column with variable Comumn and rows

sql,sql-server,pivot
I am getting this data from into one of view, which internally collect from various sources, I would like to format this data in normalised way, so this can be easily used in SSRS for charting purpose. Raw Data ServerName TimeStamp CPU_PCT_Utilization Server_01 2015-04-16 16:23:04.000 2.781995773 Server_01 2015-04-16 16:28:04.000 2.804701567...

Laravel 5 pivot table project<->user

php,mysql,laravel,pivot
I find the pivot tables pretty complicated and I don't see what to do next or what I am doing wrong, I've found some tutorials but didn't help me in my needs. I have a projects and users with a many-to-many relation. One project hasMany users and One user hasMany...

SQL Matrix to array

sql,arrays,tsql,matrix,pivot
I am working with T-SQL and I have a table that looks like Matrix (8x8). My objective is to make that Matrix (table) into array using Pivot ... I have read forums and more stuff that i managed to find but i still can't make a code for it ......

TSQL Pivot with strings

sql,sql-server,string,tsql,pivot
My apologies, as I know this is a question that's been answered many times in various contexts. However, after an hour of trying and failing to adapt the examples I've found to my needs, I'm at the conclusion that I'm an idiot, and need help specific to my data... I...

T-SQL using how to use PIVOT function

sql,sql-server,pivot
I have the following table structure in SQL (using T-SQL): sqlfiddle: http://sqlfiddle.com/#!6/e5edc/1/0 The data would look something like this: Now I would like to transpose the structure so I get the following: columns [01_amount] to [12_amount] and columns [01_active] to [12_active] as rows instead of columns All rows of [initials]...

Rename all Pivoted Values in SQL

sql,sql-server,pivot
I have stored values in Fields table, Name column. Here are list of names which should go as column names, for It I use PIVOT. I need to add 1 to all selected names. It should be something like this: Before pivot: Column name: | Name | | Jeniffer |...

Pivot Spark Dataframe

scala,apache-spark,pivot,dataframes
I am starting to use Spark Dataframes and I need to be able to pivot the data to create multiple columns out of 1 column with multiple rows. There is built in functionality for that in Scalding and I believe in Pandas in python, but I can't find anything for...

Pivot Tables - using more than one column

sql-server,pivot
With Pivot Tables is it possible to base your columns on two values (in my example 'code' and 'val')? By the looks of it you can't, but that seems a bit of a limitation to me, so perhaps I've just misunderstood something. For example if my data table looks like...

Oracle Sql Query : How Pivot Function Sum() in Packages?

sql,plsql,oracle10g,sum,pivot
I use Oracle 10g and I query in Packages. How Pivot function sum() in Packages? Sql Query. WITH DATA_JOIN AS (SELECT S.START_DATE_GMT, S.NO_OF_CALL, S.RATE, T.TAX_RATE, T.EFF_DATE, T.EXP_DATE, S.COMPANY_CODE, T.COMPANY_CODE CC, S.SUB_EVENT_TYPE_ID, S.event_type_id, S.PMN_ORIGIN, S.PMN_ROUTE FROM IR_V_ISM_RATE_BY_TRANS S LEFT JOIN IR_PMN_TAX T ON S.COMPANY_CODE = T.COMPANY_CODE AND S.PMN_ROUTE = T.PMN_CODE WHERE...

SQL Pivot query based on “SUM of a column divided by SUM of another column”

sql-server,pivot
I am trying to figure out how to pivot my data based on the SUM of a particular column divided by the SUM of another Column". Here is where I've reached so far but the PIVOT query is giving me errors: USE MyDatabase SELECT [PropertyCode], [Market], [JANUARY 2015], [FEBRUARY 2015],...

Dynamic pivot with similar column names

sql,sql-server,sql-server-2008-r2,pivot
I'm looking for a way to pivot a varying amount of rows to columns in sql server 2008 R2. I created the data column RANK in the query because, ultimately I want the pivoted column names to be labeled the value in the RANK column. Then, if somehow I can...

SQL Server table manipulation

sql-server,table,structure,pivot,pivot-table
I am really new here. Currently working on SQL Server 2012. I have table A of the following structure (records are a subset of the full data table): CREATE TABLE [dbo].[TABLE_A]( [ASSET ID] [float] NULL, [TASK ID] [float] NULL, [IN_YEAR ] [float] NULL, [IN_WEEK] [float] NULL, [FLAG] [nvarchar](1) NULL )...

Clarification on SQL PIVOT [closed]

sql-server,pivot
Can someone tell me what is missing in the second Pivot example. It returns NULL http://sqlfiddle.com/#!3/2b405/2

SQL query design for getting a table with column depending on a column value in SQL Server

sql,sql-server,sql-server-2008,windows-7,pivot
I would like to do a SQL query in SQL Server to get a table: Table1: id t value 1 R 2412 1 Q 98797 2 R 132 2 Q 7589 I need to get table: id R_value Q_value 1 2412 98797 2 132 7589 I used case and when,...

Pandas: Difference between pivot and pivot_table. Why is only pivot_table working?

python,pandas,pivot
I have the following dataframe. df.head(30) struct_id resNum score_type_name score_value 0 4294967297 1 omega 0.064840 1 4294967297 1 fa_dun 2.185618 2 4294967297 1 fa_dun_dev 0.000027 3 4294967297 1 fa_dun_semi 2.185591 4 4294967297 1 ref -1.191180 5 4294967297 2 rama -0.795161 6 4294967297 2 omega 0.222345 7 4294967297 2 fa_dun 1.378923...

R: Pivoting using 'spread' function

r,data.frame,pivot,melt,tidyr
Continuing from my previous post, I am now having 1 more column of ID values that I need to use to pivot rows into columns. NUM <- c(1,2,3,1,2,3,1,2,3,1) ID <- c("DJ45","DJ45","DJ45","DJ46","DJ46","DJ46","DJ47","DJ47","DJ47","DJ48") Type <- c("A", "F", "C", "B", "D", "A", "E", "C", "F", "D") Points <- c(9.2,60.8,22.9,1012.7,18.7,11.1,67.2,63.1,16.7,58.4) df1 <- data.frame(ID,NUM,Type,Points) df1:...

Count distinct values subject to condition in Calculated Field

excel,if-statement,count,pivot,distinct
How can I create a Calculated Field in a PivotTable that will count distinct values of Field1, given that Field2 meets a particular condition? My limitations: Needs to be in a PivotTable Needs to be a Calculated Field The Calculated Field needs to be a unique count "formula", not simply...

MySQL Pivot data with fixed number of columns

mysql,pivot
The following is my SELECT statement which pivots my data nicely. My Data Looks like this: col_a | col_b | col_c | col_d | Score ------------------------------------- stuff | stuff | stuff | null | 5 stuff | stuff | stuff | title_a | 3 stuff | stuff | stuff |...

convert duplicate rows into new columns MySql

php,mysql,pivot
Basically I have a MySql table -------- ----------- -------- cv_id all_stages amount -------- ----------- -------- 12 S1 5 13 S4 2 13 S2 1 14 S1 3 14 S3 2 I have to write a query so that, based oncv_id, I need to have a single entry. And I need...

PIVOT Not Working on a DENSE_RANK Column

sql,sql-server,pivot
I'm writing a report which gives weekly totals for daily operational data. There will be 8 weeks across the top of the report as columns. Each row is a vehicle. Variables such as date range will be made dynamic eventually, this is a little more hard coded right now whilst...

Return NULL as Zero from my SQL Pivot query

sql-server,null,pivot
I'm using SQL Server 2014 and I have a Pivot Query that runs on my database and works fine. My problem is that the certain fields have NULL in them. I want the NULLS to be replaced by a Zero. My query stands as follows: (SELECT [PROPERTYCODE], [MARKET FINAL], [MARCH...

SQL Pivot trouble

sql,sql-server-2008,pivot
I have data which looks like this: +--------+-------------------------------+-------------+---------+-------------+----------------+--------------+-------------+--------------+--------------+-----------------+ | Type | Description | Issuer Code | Nominal | book_value | profit_or_loss | market_value | exposure | average_cost | market_price | unrealised_gain |...

pivot query where data which is being pivoted contains same text

sql,sql-server-2012,pivot
I'm pivoting data which contains name and phone numbers for some person records. The row which I'm pivoting contains questions which are: name, phone, name, phone. Yes, the questions are the same repeated text. My pivot function is like so SELECT pesonnel_id, Qasked, Qanswered FROM .... pivot (max(QAnswered) for QAsked...

Transform table structure of SQL query result (pivot)

mysql,pivot
I have a table coming from a query with the following content structure: +-----+-----------+-------+ |date | sensor_id | power | +-----+-----------+-------+ |date1| 1 | 0.51 | |date1| 2 | 0.52 | |date1| 3 | 0.53 | |date2| 1 | 0.61 | |date2| 2 | 0.61 | |date2| 3 | 0.63...

Python: pivot a pandas DataFrame when the desired index Series has duplicates

python,pandas,pivot,dataframes
I have a pandas DataFrame my_data that looks like event_id user_id attended 0 13 345 1 1 14 654 0 ... So event_id and user_id both have duplicates because there is an entry for each user and event combination. What I want to do is reshape this into a DataFrame...

Can I create dynamic pivot query from key value table with different data types?

sql-server,pivot,key-value,dynamic-sql
I have a key-value pairs table. I have created a script using dynamic sql that pivots the table correctly. However, the keys have different data types. Is there a way to cast the keys during or after the pivot dynamically? I can have the data types in the same key-value...

Add a column description to a new (not Classic) PivotTable

excel,pivot,pivot-table
I want to add a column description to a PivotTable. Is it possible with "new" (not old "Classic") PivotTable? Here is source table example and desired results: I can achieve the results with "Classic" settings but this has some feature I do not like so I would prefer to use...

Oracle SQL PIVOT : missing column and aggregated rows?

sql,oracle,pivot
I'm trying to get a table which shows monthly attendance details for students. What I have is a pivot which sums up the number of absences based on the student's course load. Say for instance I knew this student has 4 classes, all the time, regardless of school year or...

Pivot sample with dynamic columns

sql,sql-server,tsql,pivot
I've seen many pivot examples but I didn't find any that applies to my case. I hope someone can give me a hand. I have the these two tables Entities Performance +----------+--------+----------+ | idEntiry | idWeek | idResult | +----------+--------+----------+ | 1 | 1 | 1 | | 2 |...

Create query to display number of products sold for each day of the week

sql,sql-server,pivot
I have am trying to generate a table that looks like this in SQL Server: Name | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Total Product1 | 2 | 0 | 1 | 0 | 1 | 0 | 0 | 4 Product2...

Getting Total Cost Grouped by User

mysql,sql,database,join,pivot
I've been struggling with this query for two days now. I've got a user table with some values which has a relation with an order table (user can have multiple orders). This table has a relation with order_item (order can have multiple orderItems). Order_Item has a relation with invoice (order_item...

SQL Server 2005 - Dynamic PIVOT query

sql,sql-server,pivot
I require your kind help on a PIVOT query problem. The scenario is I have a voting system. Comprises of 3 tables. Elections, Candidates, Votes. The PIVOT relates to PIVOTing the Candidate names as Columns and having the VOTE data as the data. So far I have attemped this and...

Query with rows into columns

sql,sql-server,pivot
I have data in tbl1: item date amount a 1 10 a 2 20 a 3 30 a 4 40 b 1 20 b 2 30 b 3 40 b 4 50 c 1 30 c 2 40 c 3 50 c 4 60 But i need like below item...

SQL Pivoting data for sign in/sign out, by application

sql,sql-server,pivot
I have an audit log that stores events that occur on one or more connected applications in the whole system. DECLARE @startDate DATETIME; DECLARE @endDate DATETIME; DECLARE @staffID INT; SET @staffID = 4; SELECT @startDate = dbo.Date(COALESCE(@startDate, DATEADD(day, -1, GETUTCDATE()))), @endDate = dbo.EndOfDay(COALESCE(@endDate, GETUTCDATE())) SELECT l.RecordedOn, s.FirstName + ' '...

SQL (HSQLDB) query to create a pivot table equivalent in LibreOffice Base

sql,pivot,hsql
I'm working on a database in LibreOffice Base and trying to output the equivalent of a pivot table. Base uses HSQL which, I understand, doesn't support pivot, but the reports you can make with it are perfect for me so I want to stick with it. There are three columns...

How to reformat a dataset using the values of rows as new columns?

postgresql,pivot,crosstab,data-analysis
I have a dataset that looks like this: id | test_id ---+-------- 1 | a 1 | b 1 | u 2 | a 2 | u 3 | a 3 | b 3 | u And I would like to roll it up into a new table such that...

Join master table's data to a key/value table data in one SELECT

sql,sql-server,join,pivot,key-value
I have a table called Contacts that contains the columns Id FirstName LastName I have another table called ContactsExtra. It has the following columns: Id ContactId (FK refers to Contacts Id) PropertyId (FK refers to Properties Id) PropertyValue This is a Key/Value table that stores some extra contact properties. E.g....

sql pivot on multiple values

sql,sql-server,pivot
Given the following data table Id Code Date PIVOT VALUE1 VALUE2 1 WMAZ 2014-01-31 12:23:06.000 1 103 1 2 EEEE 2014-01-31 11:59:15.000 2 74 2 3 WMAZ 2014-01-31 11:59:10.000 1 3 3 4 WMAZ 2014-01-31 11:56:55.000 2 10 4 5 WMAZ 2014-01-31 11:56:14.000 2 96 5 6 EEEE 2014-01-31 11:55:26.000...

How to select SQL Server row values as columns (Dynamic Pivot)

sql,sql-server,pivot,pivot-table
I have one table where we are collecting status data from different sensors. For reporting purposes I would need to group information for each sensor (SGUID) to one row. Each sensor is sending reader (RGUID) and value for reader (VAL). Source table: Goal result: I have heard about pivot's but...

Pivot control header icon of un selected pivot in windows phone universal application

xaml,windows-phone-8.1,pivot
I have pivot control in my universal windows phone application. Defined like this <PivotItem.Header> <Grid> <Grid.ColumnDefinitions> <ColumnDefinition Width="Auto" /> <ColumnDefinition Width="*" /> </Grid.ColumnDefinitions> <Image Grid.Column="0" Margin="0 0 10 0" Width="40" Source="/Assets/Images/cher.png" /> <TextBlock x:Uid="cherTextBlock" Grid.Column="1" Style="{StaticResource PivotHeadingStyle}" /> </Grid> </PivotItem.Header> When I am on first pivot the text of second...

Aggregation in dynamic pivot table in SQL Server 2012

sql-server,sql-server-2012,pivot
I have a table in SQL Server 2012 which has 5 million rows. Table view is like this : CustomerID ProdID FavouriteProduct 1 A A 1 A A 1 A A 1 B A 1 A A 1 A A 1 A A 1 B A 2 A C 2...

Translate rows to column [duplicate]

sql,sql-server,tsql,pivot
This question already has an answer here: Efficiently convert rows to columns in sql server 1 answer I have a few items in the database in the form as below: ID|ColName|ColValue ------------------- 1 |A |1testa 1 |B |1testb 1 |C |1testc 1 |D |1testd 2 |A |2testa 2 |D...

SQL Join another lookup table in a pivot table

sql,sql-server,database,join,pivot
I have a table called Grades in my database for recording pupil grades as points for every year and term they attend. These points are converted to GCSE results using a lookup table. Although I can return the Points using a pivot table, I'm not sure how to return a...

Computed field on pivot table

excel,pivot,computed-field
here there is my table N tag 1 7-3 2 3-4 3 2-4 4 5-3 5 4-6 6 3-1 7 3-1 8 3-4 9 5-6 I need to create a Pivot Table (I do not know how many rows there will be) with the first value of Tag. Example :...

Need help to transpose rows to columns

sql,sql-server,sql-server-2012,pivot
I'm sorry to flat out ask for someone to write my code for me but I've been tearing my hair out for nearly an hour trying to get the pivot operator to work in SQL. I have the following results set: SCCY AccountedPremiumCurrent AccountedPremiumPrevious ---- ----------------------- ------------------------ CAD 99111.0000 NULL...

Displaying products sales for each day in SQL

sql,date,sql-server-2012,pivot
I have a problem I can't find a solution. I have a table "SELLS" with all sells of a shop and I want to display how many sales for each product each day of a period. For example : | DATE | NAME | QTY | |------------|----------|-----| | 2014-07-03 |...

How do I pivot results in SQL so that I can see both the count and the individual entries?

sql,oracle11g,count,pivot
I am trying to get a count from a table which has 4 columns. The 2 I have included in the pivot_data are 'ocode' and 'cname' the other 2 relate to customer details. I am trying to show how many individual 'ocode' (options) there are for each 'cname' (product) so...

Pivot table query using SQL Server 2008 R2

sql,sql-server,sql-server-2008-r2,pivot
I have the following table with two fields as shown below: I want to show the pivot table of the following data into verticle form. Table: test_10 create table test_10 ( col1 varchar(10), col2 varchar(10) ); Inserting records: insert into test_10 values('A','2015-01-01'),('A','2015-01-05'),('A','2015-01-10'),('A','2015-02-15'), ('B','2015-01-01'),('B','2015-01-05'),('B','2015-01-10'),('B','2015-02-15'), ('C','2015-02-02'),('C','2015-02-05'),('C','2015-02-08'),('C','2015-02-16'); Expected Result: ColX Jan Feb -------------------...

Add a total to SUM the pivoted columns in a cross-tab query

sql,oracle,pivot
I have made a cross-tab query that lists the total stock on each of the location for a particular item. WITH PIVOT_DATA AS ( SELECT A.ITEM, A.LOC, A.STOCK_ON_HAND, B.ITEM_DESC FROM ITEM_LOC_SOH A, ITEM_MASTER B WHERE A.ITEM = B.ITEM ) SELECT * FROM pivot_data PIVOT ( SUM( nvl(STOCK_ON_HAND,0) ) --<-- pivot_clause...

Rotate columns to rows for joined tables

sql,sql-server,join,pivot
I have two tables similar to shown below (just leaving out fields for simplicity). Table lead : id | fname | lname | email --------------------------------------------- 1 | John | Doe | [email protected] 2 | Mike | Johnson | [email protected] Table leadcustom : id | leadid | name | value -------------------------------------------------...

Format Excel sheets export by JIRA-(JIRA custom reports)

vba,pivot,jira,jira-plugin,visual-studio-macros
I need to generate custom report in jira with transition time tracking details. this jira plugin can generate the time tracking details. but I need to add these time tracking details to the jira default all field export report.without copy paste the values I need to find a way to...

Simple Pivot Table with Month and text data Dynamic SQL

sql,sql-server,tsql,pivot
How do you create a simple Pivot Table in Dynamic SQL with the following data: ID Month AssignmentMonth Designation 1 5 May Joe Blow 2 5 May Available 3 5 May Available 4 5 May Available 5 6 June Carry over 6 6 June Christopher Freeberg 7 6 June Ringo...

Dynamic Pivot Table - How to add the columns generated to another table?

sql,sql-server-2012,pivot
I have a dynamic pivot query which transposes rows to columns. At the moment I have all the data in one table but can separate it if two separate tables is easier. I only need some the column ChargeNames pivoted as such (with the related charges included) Is it possible...

Unpivot and Pivot does not return data

pivot,unpivot
I'm trying to return data as columns. I've written this unpivot and pivot query: `select StockItemCode, barcode, barcode2 from (select StockItemCode, col+cast(seq as varchar(20)) col, value from ( select (select min(StockItemCode) from RTLBarCode t2 where t.StockItemCode = t2.StockItemCode) StockItemCode, cast(BarCode as varchar(20)) barcode, row_number() over(partition by StockItemCode order by StockItemCode)...

Oracle pivoting unknown number of column before execution time

oracle,oracle11g,pivot
I have something like this: id cod 1 a 1 b 1 c 2 d 2 e 3 f 3 g and i need something like this: id cod 1 cod 2 cod 3 1 a b c 2 d e 3 f g you understand that there is no...

How to create mysql pivot table if row names are in separate table?

mysql,select,pivot,multiple-tables
I have 3 tables and I need to get a pivot table from it. Table - questions +------------+----------+ | idQuestion | question | +------------+----------+ | 1 | Q1 | +------------+----------+ | 2 | Q2 | +------------+----------+ Table - question_answer +------------+------------+----------+-------+ | idAnswer | idQuestion | answer | value | +------------+------------+----------+-------+...

Average Column-wise by using T-sql and Pivot

sql,asp.net,sql-server,tsql,pivot
I have developed Financial Year Data Fiscal year wise Till now i have achieved! But i couldn't get column-wise average My table Definition CREATE TABLE [dbo].[tblact] ( [Id] INT NOT NULL, [years] NVARCHAR (MAX) NULL, [months] NVARCHAR (MAX) NULL, [expenses] DECIMAL (18, 2) NULL, [closingbal] DECIMAL (18, 2) NULL, [monthorder]...

Pivot table (or other solution) with Inner join

sql,sql-server,pivot
I have this query: SELECT DISTINCT a.max_TEA_InicioTarefa, analista, ETS.ETS_Sigla, ATC.ATC_Id, ATC.ATC_Sigla, PAT.PAT_Sigla, a.SRV_Id, ContratoComunicado.CCM_Id, ContratoComunicado.CCM_Docto, ContratoComunicado.CCM_Emissao, ComunicadoTipo.CMT_Descr, TarefaEtapaAreaTecnica_1.TEA_Revisao, TarefaEtapaAreaTecnica_1.ETS_Id, TarefaEtapaAreaTecnica_1.TEA_FimTarefa, TarefaEtapaAreaTecnica_1.PAT_Id FROM dbo.Tarefa AS Tarefa_1 INNER JOIN ( SELECT MAX(dbo.TarefaEtapaAreaTecnica.TEA_InicioTarefa) AS max_TEA_InicioTarefa,...

Oracle SQL Developer: How to transpose rows to columns using PIVOT function

sql,oracle,pivot,oracle9i
I'm attempting to create a query to transpose rows into columns using the PIVOT function. This is the contact table I want to transpose into rows: PARTYID CONTACTTEXT CONTACTTYPECD ---------- ------------ ------------- 100 0354441010 1 100 0355551010 2 100 0428105789 3 100 [email protected] 4 My intended result: PARTYID PHONE FAX...

Convert multiple columns into rows

sql,oracle,pivot,unpivot
SELECT ID, PHONE1, PHONE2, PHONE3, EMAIL1, EMAIL2, EMAIL3, EMAIL4 FROM (SELECT ...) How do I pivot it into this? *ID | PHONE | EMAIL* SELECT DISTINCT ID, PHONE, EMAIL FROM ... I can do multiple selects and UNION them but I wonder if there is a better way of doing...

Not getting the correct SUM values in SQL Server 2012 when using a PIVOT

sql-server,sql-server-2012,pivot,distinct,aggregate-functions
I am trying to create a query that will pivot over some rows but will SUM some columns and then group them together. I've used the PIVOT function before but I am running into issues when my result set contains similar values. This is SQL Server 2012. Sample code: CREATE...

How to combine multiple rows of employee into single row in SQL Server

sql,sql-server,pivot
I'm having following table and below is expected results. Please let me known if there is a easy to way to get the expected results in SQL server. EmpNo Name Benefit StartDate Status -------------------------------------------- 0001 ABC Medical 01/01/2014 Active 0001 ABC Dental 02/02/2013 Inactive 0001 ABC Vision 03/03/2012 Active 0002...

mysql pivot table with concat AND pivot counting of selected rows

mysql,pivot,concat
I have the following mysql-table cars layout BRAND | TYPE | COLOR ----------------------- bmw | e30 | red bmw | e90 | blue bmw | e12 | red audi | a3 | green bmw | e90 | red audi | tt | blue bmw | e12 | blue audi |...

Swap row/column of pivot query

sql,sql-server,pivot,unpivot
I want to know how I can flip the rows/columns of this query: SELECT * FROM (SELECT YEAR(DATA_DOC) AS YEAR, DATENAME(MONTH, DATA_DOC) AS MONTH, SUM(IMP) AS TOTAL FROM MyTable WHERE YEAR(DATA_DOC) BETWEEN 2011 AND 2014 GROUP BY YEAR(DATA_DOC), DATENAME(MONTH, DATA_DOC) ) AS REPORT PIVOT ( SUM(TOTAL) FOR MONTH IN (January,....ecc...)...

Arithmetic operators against dynamic column in SQL Server 2008

sql-server-2008-r2,pivot,dynamic-pivot
I have query to create table and result below: -- tblPart PartCode[NVARCHAR(50)],UnitPrice[Decimal(18,2)] SELECT * INTO #tblPart FROM( SELECT 'A' PartCode, '10' UnitPrice UNION All SELECT 'B','11' UNION All SELECT 'C','38' UNION All SELECT 'D','20' UNION All SELECT 'E','12')part; -- tblPartCondition ConditionCode[NVARCHAR(50)],PercentagePrice[Decimal(18,2)] SELECT * INTO #tblPriceCondition FROM( SELECT 'Weekly' ConditionCode, '3'...

Why does this sql select statement lose the varchar length and how do I re-specify it?

sql,tsql,ssis,pivot,varchar
I have a select statement that aggregates some data using a pivot to list multiple rows of data for one ID in one row going across. Now although the issue I'm coming across isn't making anything fall over or fail, I'd like to know why the length of the varchar(75)...

How to write an SQL Pivot Query for this scenario?

sql-server,pivot
I am using SQL Server 2014 and I have the following query that runs fine: SELECT b.FOH_PMSCONFNUM, a.FOC_ACCOUNT, a.FOC_PROPERTY, a.FOC_TCODE, a.FOC_NETAMOUNT FROM P5FOLIOCHARGE a LEFT JOIN P5FOLIOHEADER b ON a.FOC_ACCOUNT = b.FOH_ACCOUNT where b.FOH_PMSCONFNUM = '1458' AND FOC_NETAMOUNT NOT LIKE '-%' It gives me the following output: FOH_PMSCONFNUM FOC_ACCOUNT FOC_PROPERTY...

display result as 1 row in transposed/pivoted result of sub query in SQL Compact edition

pivot,sql-server-ce-3.5
I have a table MonitorLocationDetails which looks like below lid LoclColumn LocDescription 1 MP1 MP-1 descr 2 MP2 MP-2 descr 3 MainGate Main Gate descr I should get the rows of LocDescription and transpose it like pivot in sql server other versions. PIVOT not available in sql compact versions. The...

How to use use pivot table

sql,sql-server,pivot
I am trying to display the results from my database table using the pivot function but it's not displaying them as I need them. The columns in my database are below: ScoreID, PupilID, Subject, Year, Score I would like to display a pivot table like this: ScoreID, PupilID, Subject, Year1,...

SQL select converting transaction rows to columns

tsql,sql-server-2008-r2,pivot,greatest-n-per-group
I have a table that lists all transactions as follows: ID Account Date Amount --------------------------- 1 2 02/01/2015 30 2 5 05/01/2015 25 3 2 05/01/2015 12 4 2 07/01/2015 42 5 5 10/012015 19 6 2 11/01/2015 58 7 3 15/01/2015 36 Would like to write a select statement...

Transpose unknown number of Columns

sql,sql-server,sql-server-2008,pivot
I'm looking for an efficient way to convert rows to columns using T-SQL. This the sample data: +----+--------------+----------------+ | ID | ColumnName | Value | +----+--------------+----------------+ | 1 | FirstName | Name 1 | | 2 | FirstName | Name 2 | | 3 | FirstName | Name 3 |...

Using the same ListView on two PivotItems without code duplication

xaml,listview,windows-phone-8.1,pivot,reusability
I'm working on a shopping Windows Phone 8.1 application, where I have a "Promotions" page. This page is a Pivot page with two PivotItems. The first Item shows the top 100 promotions, and the second Item shows all the promotions. To get the data I call a webservice. The data...

SQL Pivot Column that has multiple values for same column

sql,pivot
Trying to pivot table results that may have multiple rows with the same value I have data that looks like this so far. Nbr Person Test 33 Barry. Prim 33 Brian Sup 33 Burke RT 1st 33 Ray Add 33 Jake Add 33 Smith Add I'm trying to pivot it...

Fetching row data as column names and set as boolean if exists or not

php,sql,pivot
I have access to a database similar to this: users: id | name ====|======== 1 | Tom 2 | Dick 3 | Harry 4 | Sally exlusions: user_id | exclusion ========|============== 1 | French only 3 | No magazine 4 | English only 1 | No magazine Is it possible...

How to line up columns of a pivot table

sql,sql-server,database,pivot,pivot-table
I have a pivot table that is being created like this: date Services Uptime Services Downtime Centers Downtime Centers Uptime ----- --------- - ------------------ ---------------- --------------- 12/5/14 100.00% 0.00% 100.00% 100.00% 12/12/14 100.00% 0.00% 0.00% 0.00% 12/19/14 100.00% 0.00% 100.00% 0.00% 12/26/14 100.00% 0.00% 100.00% 0.00% I would like it...

SQL Pivot combined with a SUM/Count

sql,sql-server,pivot
Stackers, I've always struggled with understanding PIVOT's when written in SQL syntax... My data format: CONTID STCODE 8203 BA 8203 BA 8203 UY 8203 UY 8203 UY 8203 FA 8203 FA 8203 FA 8203 UY 8203 UY 8203 *LC 8203 *LC 8203 *LC 8203 UY The intended output: CONTID BA...

Calculating overlap between groups

sql,oracle,oracle11g,pivot
I have a table with two columns of interest, item_id and bucket_id. There are a fixed number of values for bucket_id and I'm okay with listing them out if I need to. Each item_id can appear multiple times, but each occurrence will have a separate bucket_id value. For example, the...

Dynamic pivot table - sql developer

oracle,pivot
I tried to create my own procedure for dynamic pivot but it isn't working. My procedure is created correctly but when I want to execute it - it isn't working. Firstly I had tried bluefeet's procedure and it was working. link to this question Dynamically pivoting a table Oracle My...