I’ve been playing around with the PIVOT function for a while. I have a table that looks like this Every ClientID is repeated once for each month. Every month new rows are added to the table with a new period associated to the row. It’s an incrementing table. What I need to do is to PIVOT the table…
Tag: pivot
SQL Server : fastest way to change rows to columns
I’ve got an issue regarding two tables in my database that have the following columns: COMMENT table Composite key made up of (KEY, TYPE, NUMBER) RESULTS table An example of the COMMENT table would be this: For each KEY, there are only 3 different types A,B,C and one TYPE may have up to 0020 in the NUMB…
Postgres: Count multiple events for distinct dates
People of Stack Overflow! Thanks for taking the time to read this question. What I am trying to accomplish is to pivot some data all from just one table. The original table has multiple datetime entries of specific events (e.g. when the customer was added add_time and when the customer was lost lost_time). Th…
dynamic pivot SQL Query in DB2
I have table like below in DB2 where all these 3 columns are VARCHAR data type I need a dynamic PIVOT query which should select values for ABC_COL1 and ABC_COL2 in row format when FILE_NM filter is used as ABC.TXT The same query should select the values for XYZ_COL1, XYZ_COL2 and XYZ_COL3 in row format when F…
PL SQL Pivot: use different aggregates
I want to pivot sum(sales) per country as below. But the price column should be an average per shop. So, I would love to have this result: Desired Result: shop avg_price US CN 2 6 100 10 3 5 60 10 Current Result: shop price US CN 2 5 null 5 2 7 100 5 3 5 60 10 I
SQL – Adding columns
I am new to SQL and was wondering if someone can help me with this small request. I am trying to have my output like down below: But I am currently getting this output: Here is my SQL code: How can I get the columns to reflect MF, CH_A, CH_B, and CH_C with its respective value? Answer @Med2020 Your query
How to pivot given string by replacing special characters in SQL?
In an interview, I have been asked to pivot a string by replacing special characters. Given string was ‘AAA%BBB$CCC#DDD’ Expected output as shown in then image: How can we do this in SQL Server? Thanks in advance! Answer In SQL Server 2014?(I guess the operating system you are using is Windows Ser…
How to select a specific number of rows and convert them into named columns
I am hanging on this problem for hours. A very simple query: SQL Result: RoomId WaterMeterNumber 95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF SZ12800491 95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF 3375791 95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF 45332 95E5ACE0-FEE4-4D33-BC22-0DCF7B1155CF SK9649 And I want the query result to be…
Oracle sql pivoting values separated by commas
I have the below input ID LOTYP PROID LOCKR XXXXX 06 01 Y XXXXX 06 02 X XXXXX 06 02 S XXXXX 06 01 R XXXXX 02 01 Y XXXXX 02 02 X XXXXX 02 02 S XXXXX 02 01 R YYYYY 06 01 Y YYYYY 06 02 X YYYYY 06 02 S YYYYY 06 01 R YYYYY 02 01
Flatten the Data in BigQuery
I have dimensions.key_value of RECORD type i run the following query with following output. To unnest the data i have created the following view: My Output: There are two thing i need to verify is my query correct ? How i can make the query generic i.e if i don’t know all the key, there some other keys …