Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

A pivot (or crosstab) converts row data into columnar data, and vice versa for an unpivot. Not all databases support explicit PIVOT syntax but the functionality can be implemented, often using decision logic (CASE statements, etc), aggregate functions, or an extension.

0
votes
1answer
60 views

Transposing repeated rows of datetime into columns in SQL 17

I am working with a dataset of inpatient data in SQL version 17. The fields contain anonymized ID, admission date, discharge date and other relevant fields. Since some of the patients are readmitted, ...
-1
votes
1answer
43 views

How to get one column and select other column as horizontally

Given data of the form... +-------------+--------------+ | Employee_ID | Candidate_ID | +-------------+--------------+ | 1000 | 1 | | 1000 | 2 | | 1000 | 5 ...
-3
votes
1answer
49 views

How to display multiple columns as rows + columns [closed]

I have this table and data: CREATE TABLE #t1 ( [Year] int, Student int, Test char(2), Score int, Achievement varchar(32) ); INSERT #t1 VALUES (2014,1,'MA',697, '...
1
vote
0answers
16 views

Dynamic rows and columns with Pivot and calculated value

I'm trying to build a pivot request but i don't undestand how it's applicable for my database. The value I need has to be calculate (A percentage) for each column and row. I'm using SQL Server 2012. ...
0
votes
1answer
30 views

How to pivot tables view/results from row to columns

I have e-mail aliases tables that looks like this: +---------------------------------+--------------------------+-------------------------+-------------+---------------------+---------------------+---...
0
votes
1answer
20 views

Sort pivot result

I just want to ask how to sort the result of a PIVOT in SQL Server. When I use ORDER BY there is an error: "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, ...
0
votes
0answers
12 views

Recursively enumerate Active Directory forest AND dynamically pivot results [migrated]

I'm trying to get a list of all the AD groups along with their sub groups. It should be recursively populated and then displayed with the following column style Parent_Group, Child_Group_Level1, ...
0
votes
0answers
24 views

How to separate values from column into two columns? [duplicate]

I have to separate values into two columns Prod_id Param Val 1 201 444 1 202 222 1 203 12 1 204 53 1 205 24 How to make ...
0
votes
2answers
38 views

Divide data in single column to multiple columns

I have source data like Name Is_Trget a Yes s No d Yes f Yes a Yes S No from this source data expected output is Name Is_Yes Is_No a 2 0 s 0 2 d 1 ...
8
votes
2answers
198 views

How to create recurrent weekdays as columns in a pivot?

I am newcomer to programming and databases and I would be grateful for some help on the following scenario. I use PHP with SQL Server. I am building an employee attendance system and I would like to ...
1
vote
2answers
63 views

Create 2D truth table from “cross product” of two tables via PostgreSQL view or function

I have a working Excel-based method for creating a truth table from two vectors exported from a PostgreSQL database. The process takes about 4 hours to complete due to a large number of VLOOKUP and ...
0
votes
1answer
81 views

Oracle convert multiple rows to column using pivot

I have a table that contains the values +-------------+--------------+---------------------+-------------+-------------+--------------+-------+---------+-----------+---------------+--------------+ |...
0
votes
0answers
23 views

How do I PIVOT this schema in MySQL?

I have a relation in a MySQL database with the following schema Data(x: INTEGER, y: INTEGER, value: INTEGER) The combination of (x, y) is unique and spans from 0 to 100. Is it possible to combine ...
0
votes
1answer
41 views

SQL query to convert Column to Row using Pivot/Unpivot [duplicate]

My Select Table. I tried Pivot query to get my expected result but I was struct to generate the expected result. WITH PersonOptions(ID,[Year], [Text], Value1, Value2) AS ( SELECT 'A',...
2
votes
1answer
77 views

How to combine a parent with all children in the same row

SQL Statement Question: I have two tables PARENT and STUDENTS. PARENT Table ParentID | FName | LName | StudentID ---------+-------+-------+---------- 1 | John | Doe | 11 1 | ...
0
votes
2answers
52 views

How can I pivot this data using a cte or the pivot syntax?

Let's say I have these two tables in an MSSQL-Database: Is there any way to create a query that result in a table like this? There is no case where there are multiple Leader/Assistant in the TEAMS-...
2
votes
1answer
15 views

How can I pivot a table then join multiple tables and change values based on information between tables

The database I'm using has multiple tables storing different information types...it's very convoluted. I'm trying to join this information into one table, with all of the details for a single ...
0
votes
0answers
36 views

Pivot and Unpivot with just two colums [duplicate]

I need help with SQL PIVOT or UNPIVOT Example SELECT Manufacturer, CarType FROM Vehicles Current returned data looks like this: I would like to get: I want to pivot or unpivot the table to ...
0
votes
4answers
75 views

Can Group By and Count on MySQL be optimized?

I have a 100 million row table on MySQL. I need to count rows for certain ranges and I have the proper indices for filtering rows. Let's say a SELECT statement returns 20000 results, but all I need is ...
0
votes
2answers
44 views

how to change this dynamic rows to columns

I have following columns (it is dynamic, more rows in table with same ID).Type can be duplicate. +--------------+---------+------------ | ID | TYPE | VALUE | +--------------+---------...
2
votes
1answer
107 views

Firebird query pivot

I have a Firebird sql query that returns 3 fields: Surname, Name and date as (YYYY-MM-DD HH:mm:ss) select Surname, Name, "WHEN" from users left join attendant on (users.id = attendant.userid); This ...
0
votes
1answer
37 views

How to find value for specific dates for a process stored in table

I have a table called as tab where we store all the process running on machine in below format proc_name start_date Duration proc1 24-Jul-18 0:0 proc2 24-Jul-18 0:0 ...
1
vote
1answer
707 views

Mysql Pivot rows into dynamic columns with unknown number of columns

I have an output of a query as follows: bid | code | amount | ----------------------------- 2915 | BF | -10700.00 | 2915 | YQ | -300.00 | 2915 | YR | 0.00 | 2915 | YM ...
3
votes
0answers
224 views

Query to split data in a column into multiple columns using id in another column (pivot table; crosstab)

Database engine: Server version: 10.2.16-MariaDB-10.2.16+maria~xenial mariadb.org binary distribution I have a database table defined as follows: CREATE TABLE `disktemp` ( `sample_time` datetime ...
1
vote
2answers
40 views

Pivot values to columns

I'm trying to create a view that groups score data for each week and displays it for each user but I don't know which commands to use. Sample data: // scorecards table week | user | score | 1 ...
1
vote
0answers
34 views

need help with multiple column aggregate using pivot in mssql

below is the temporary table for class assessments. this table contain list of class assessment which include the assessment code, date of assessment, total item and passing percentage. create table #...
0
votes
0answers
20 views

crosstab query : count a number occurence in a row

I have a crosstab query that shows 1 and 0 in the value area. Function 1 Function 2 ..Function n project 1 1 0 1 project 2 0 0 1 .. project ...
-1
votes
1answer
25 views

How to do looping and use case when logic from dynamicaly variable in mysql

I'm new in mysql Before I have question and have the answer from someone. But I need a improve again from the query. This is my sampling data on mysql db table test With this query on below from ...
2
votes
1answer
35 views

PIVOT with SQL Server

I have SQL Server table data returning the below data. (Green text is a note on the value of each cell in 'Outcome' column) I want to PIVOT that query result so that it uses the latest 'ReceivedTime' ...
1
vote
1answer
136 views

Default values for crosstab results

I'm trying to write my first crosstab query in Postgres. Some of the results have no entries and so the rows that are returned have null entries. How do I set a default value for those entries? Here ...
0
votes
1answer
38 views

How to pivot a column showing its content as new columns

I'm working with MySQL 5.6 and I'm having headaches with this issue. I've been looking in several groups the result, but I think this isn't very common. I have a table like the following one: direc ...
2
votes
1answer
52 views

Find time difference in different rows with a condition [closed]

I have a table showing statuses (working, not working) for several devices (ordered by device desc, datetime desc): rownum device datetime status ---------------------------------...
0
votes
1answer
31 views

Query multiple tables

I store time stamps in four different tables and I like to query mysql to give me total amount of timestamps for a given day. SELECT time_stamp AS Tid, Count(time_stamp) as Tot FROM (select ...
1
vote
1answer
148 views

Join tables and pivot

tbl_orders order_id, qty 123, 2 124, 1 125, 3 126, 1 127, 1 128, 1 129, 4 tbl_score order_id, name, created 123, product 1, 2018-06-01 124, product 1, 2018-06-01 125, product 1, 2018-06-02 126, ...
1
vote
0answers
263 views

How to transposing rows into columns in MySQL?

I have three table (student's_info,subject and marks): this is table student's_info: ---------------- |s_id | s_name | ---------------- | 1 | Ali | | 2 | Jon | --------------- This is ...
3
votes
2answers
57 views

Merge multiple rows into fewest number of rows of distinct values

In SQL Server does anyone know of a nice way to merge/flatten multiple rows of data into the fewest possible rows containing only the distinct non null values. I.e. A dataset like this: As this: ...
0
votes
1answer
29 views

Show Rows as Columns in SQL Server [duplicate]

I havent worked with Pivot. Not sure if pivot is the right thing to use. Is it possible to show this data: ------------------- | Name | Code | ------------------- |Test A | 1 | |Test B | ...
3
votes
1answer
409 views

Pivot with rollup in sql server

Is it possible to use a cursor to create a dynamic table and then use those columns to aggregate data in SQL server 2008? For example take the following table. CREATE TABLE Billing ( BillingId ...
0
votes
0answers
11 views

get an array with a crosstab as multiple join

I have a ad_id from action_states u1 join with the table ad_media on u1.ad_id=ad_media.ad_id so basically as a return result i must have a set of array ad_media_id , it keeps giving me the results ...
-1
votes
1answer
78 views

Selecting roles from multiple rows into a single row

I have a table like this: As you can see studentId: 43 has two different GIDs (these are roles, with a max of 3). I have to select the record like:
0
votes
1answer
398 views

monthly report in mysql

can any body help me to edit this code to met requirements of charts pages i'm preparing for my project ? code : SELECT section, DATE_FORMAT( date_adm, '%Y-%m' ) AS datetime, COUNT( section ) AS ...
0
votes
1answer
127 views

Add a New Column with ALIASES on 'date' by automation based on search results

I am trying to create a new column based on date search result. I have written a code in the link SQL fiddle as I am looking for date automation on each column based on the date format. the date ...
1
vote
1answer
98 views

How to optimize a fetch into a crosstab

I have a schema like: [ad_id] . [name] . [valueofname] 1 . name . "brian" 1 . age . "23" 2 . job . "IT" 2 . name . "Jack" The row name contains ...
4
votes
2answers
486 views

Dynamic dates Pivot

Sample Data from StartKPA table: I wrote the following query: SELECT * FROM ( SELECT BookInventoryDate, EndKpa,CASE WHEN (EndKPA*100)>80 THEN '>80' WHEN (EndKPA*100) ...
1
vote
1answer
203 views

Transpose Rows to column

Can anyone Please help me to transpose below result +------+------------+-------+ | SVR | dates | count | +------+------------+-------+ | SRV1 | 03/25/2018 | 1 | | SRV1 | 04/06/2018 | 59 ...
5
votes
1answer
522 views

How to Pivot in T-SQL

I have a question I can't solve. My query would be: SELECT f.Title, p.StartOfShowing FROM PlayDates p INNER JOIN PlayDateSections ps ON p.PlayDateId = ps.PlayDateId INNER JOIN ...
0
votes
1answer
122 views

Convert rows into columns using Pivot in SQL

I have this temp table named #DynamicDB, with below data. Date Actual_Prod Forecast_Prod MRB_Parts 01-04-18 1 2 2 02-04-18 3 2 NULL ...
0
votes
1answer
249 views

PIVOT, Row_Number() returning multiple rows

I'm trying to pivot a list of emails onto a single row, but for some reason my primary email address is getting a row of its own, so Im ending up with two rows per contact. CREATE TABLE #tmp ( ID ...
4
votes
2answers
55 views

Add column for subsequent values for a record

I have a table structured as follows: CREATE TABLE t ( HHID varchar2(1), INDIV_ID int, PRIM varchar2(1) ); insert into t values ('A', 10, 'Y'); insert into t values ('A',...
0
votes
1answer
41 views

How to get 0 in the null value fields

select * from ( select type,SUM(ISNULL(amount,0)) AS amount,month(date) as date from tbl_cashmaster where date between '2016/12/31' and '2017/12/31' group by type,month(date) ) src ...