I need your help! Let's say I have this table: Instance|Date |MetricID|Value |--| --- | --- |---|---| |Marc | 09/14/21|1|5 |Marc |09/14/21|2|2 |Marc |09/14/21|3|1 |John | 09/14/21|1|10 |John |09/14/21|2|1 |John |09/14/21|3|1 |Marc | 09/15/21|1|15 |Marc |09/15/21|2|0 |Marc |09/15/21|3|1 |John |09/15/21|1|10 |John |09/15/21|2|1 |John |09/15/21|3|0 And I want this: Instance|LatestDateMetric1 |LatestDateMetric2|LatestDateMetric3 |--| --- | --- |---|---| |Marc | 09/15/21|09/14/21|09/15/21 |John |09/15/21|09/15/21|09/14/21 I tried this code, It looks a bit like I want except It takes the value even if it is null and the result is by line not column. SELECT "Instance", "MetricID", MAX("Date") as "LatestDate" FROM "API_Metric2" GROUP BY "Instance", "MetricID" This is the result I got: Instance|MetricID|LatestDate |--| --- | --- |---|---| |Marc |1|09/15/21|1 |Marc |2|09/15/21|2 |Marc |3|09/15/21|3 |John |1| 09/15/21|1 |John |2|09/15/21|2| |John |3|09/15/21|3 And I also tried this: SELECT "Instance", CASE WHEN "MetricID"=1 AND "Value" NOT NULL THEN MAX("Date") ELSE 0 END) AS "LatestDateMetric1", CASE WHEN "MetricID"=2 AND "Value" NOT NULL THEN MAX("Date") ELSE 0 END) AS "LatestDateMetric2", CASE WHEN "MetricID"=3 AND "Value" NOT NULL THEN MAX("Date") ELSE 0 END) AS "LatestDateMetric3" FROM "StackExemple" GROUP BY "Instance", "Date", "MetricID" But I get this error message: > Parse error at line 2, column 66. Encountered: "Value" ***Edit***: I also got this code which seems to be working but It's not taking the null values into account. It only display 09/15/21 as the LatestDate for all metrics. SELECT "InstanceName", MAX(CASE WHEN "MetricID" = 4 THEN "Date" END) as "LatestProjectCreated", MAX(CASE WHEN "MetricID" = 5 THEN "Date" END) as "LatestActionCreated", MAX(CASE WHEN "MetricID" = 8 THEN "Date" END) as "LatestUserCreated" FROM "API_InstanceMetric" GROUP BY "InstanceName"; ***Edit2***: The issue persists even with adding the "Value IS NOT NULL" as below SELECT "Instance", MAX(CASE WHEN "MetricID" = 1 AND "Value" IS NOT NULL THEN "Date" END) as "LatestProjectCreated", MAX(CASE WHEN "MetricID" = 2 AND "Value" IS NOT NULL THEN "Date" END) as "LatestActionCreated", MAX(CASE WHEN "MetricID" = 3 AND "Value" IS NOT NULL THEN "Date" END) as "LatestUserCreated" FROM "StackExemple" GROUP BY "Instance";

I have a mysql database with this structur: | Name | 2019 | 2020 | 2021 | 2022 | ... | | --- | --- | --- | --- | --- | --- | | Name1 | 124 | 98 | 34.5 | NULL | | | Name2 | 102 | NULL | 34 | NULL | | | Name3 | 34 | 56 | 97 | 123 | | | Name4 | NULL | NULL | 34.5 | NULL | | | ... | | | | | ... | Every year comes new lines and a column is added. I want to transpose this table to the structur: | Year | Name1 | Name2 | Name3 | Name4 | ... | | --- | --- | --- | --- | --- | --- | | 2019 | 124 | 102 | 34 | NULL | ... | | 2020 | 98 | NULL | 56 | NULL | ... | | 2021 | 34.5 | 34 | 97 | 34.5 | ... | | 2022 | NULL | NULL | 123 | NULL | ... | | ... | | | | | ... | The number of columns and rows are dynamic. There are many threads with a similar problem (partially transpose with pivot). But I found no solution for this problem. Thanks for help.

I am working with 2 tables and need help to produce an output by converting rows to columns, and i need to sum the value first be grouping Here is the fiddle: https://www.db-fiddle.com/f/kmQjRvvensRTfYsSELxMF2/1 Here is the table: CREATE TABLE teacher ( TeacherId INT, BranchId VARCHAR(5)); INSERT INTO teacher VALUES ("1121","A"), ("1132","A"), ("1141","A"), ("2120","B"), ("2122","B"), ("2123","B"); CREATE TABLE activities ( ID INT, TeacherID INT, Hours INT); INSERT INTO activities VALUES (1,1121,2), (2,1121,1), (3,1132,1), (4,1141,NULL), (5,2120,NULL), (6,2122,NULL), (7,2123,2), (7,2123,2); My SQL: SELECT totalhours hours , branchid , COUNT(*) total FROM ( SELECT COALESCE(y.hr,0) totalhours , x.branchid , x.teacherid FROM teacher x JOIN ( SELECT teacherid , SUM(hours) hr FROM activities GROUP BY teacherid ORDER BY hr ASC ) y ON x.teacherid = y.teacherid ) a GROUP BY hours , branchid ORDER BY hours , branchid; Output: +---------------+-------------------+--------------------+ | hours | branchid | total | +---------------+-------------------+--------------------+ | 0 | A | 1 | | 0 | B | 2 | | 1 | A | 1 | | 3 | A | 1 | | 4 | B | 1 | +---------------+-------------------+--------------------+ Explanation: Table teacher consist teacher id and branch id, while table activities consist of id, foreign key teacher id, and hours. Hours indicate duration of each activities made by teacher. Teacher can do more than one activities or may not do any activities. Teachers who not doing any activity will be set to null. The objective of queries is to produce a table that consist of summary of teachers activity by branch and group by hours. In the expected output table, 'Hours' is a fixed value to indicate hours from in ascending order starting from 0 to 12. It will still display value even there are no hours value for A and B. A and B columns are branch. The value indicates total number of teachers who are doing activities. So, for row 0, there are 1 teacher for branch A and 2 teachers for branch B who are not doing activities. Expected output: +-----------+------------+------------+ | Hours | A | B | +-----------+------------+------------+ | 0 | 1 | 2 | | 1 | 1 | 0 | | 2 | 0 | 0 | | 3 | 1 | 0 | | 4 | 0 | 1 | +-----------+------------+------------+

I write some pivot query in oracle but I have some problem with order data : Select * FROM ( SELECT ve_nazwisko PH, odbiorca, CONCAT(CONCAT(nazwa1,' '),nazwa2) FIRMA, segment2017, segment2018, symbol_kraju, nazwa_kraju, netto, zysk, CONCAT(CONCAT(miesiac,' '),ROK) Data FROM v_eksport_arkusz_4_6 WHERE ROK >= '2017' ) PIVOT ( SUM(netto) as netto, SUM(zysk) as zysk FOR Data IN('01 Sty 2017','02 Lut 2017','03 Mar 2017','04 Kwi 2017','05 Maj 2017','06 Cze 2017','07 Lip 2017','08 Sie 2017','09 Wrz 2017','10 Paź 2017','11 Lis 2017','12 Gru 2017', '01 Sty 2018','02 Lut 2018','03 Mar 2018','04 Kwi 2018','05 Maj 2018','06 Cze 2018','07 Lip 2018','08 Sie 2018','09 Wrz 2018','10 Paź 2018','11 Lis 2018','12 Gru 2018', '01 Sty 2019','02 Lut 2019','03 Mar 2019','04 Kwi 2019','05 Maj 2019','06 Cze 2019','07 Lip 2019','08 Sie 2019','09 Wrz 2019','10 Paź 2019','11 Lis 2019','12 Gru 2019' )) And displaying data they are not something I expected. Now displaing this: - January 2017 - Netto,January 2017 Zysk,February 2017 Netto,February 2017 Zysk and something like this to 2019 I want display pivoting data in the following order : - January 2017 Netto,February 2017 Netto,March 2018 Netto and to last 2017 months next January 2017 Zysk ,February 2017 Zysk,March 2018 Zysk and to last 2017 And this data loop to 2019 Somthing like in pivot table in Excel :

I have a table called 'price' with the following rows idRequest | idPair | idExchangePlatform | lastPrice 1 1 1 95 1 1 2 100 1 1 3 40 2 1 1 80 2 1 2 250 2 1 3 10 3 1 1 110 3 1 2 300 3 1 3 60 1 2 2 800 1 2 3 1300 2 2 2 1110 2 2 3 1950 i need to make a SELECT QUERY WHERE idRequest and exchangePair match with the same table values but different idExchangePlatform For example for this set of values: idRequest | idPair | idExchangePlatform | lastPrice 1 1 1 95 1 1 2 100 1 1 3 40 i need to get the following output: idRequest | idPair | idEP_a | idEP_b | lastPrice_sum 1 1 1 2 195 1 1 1 3 135 1 1 2 3 140 i'm using this query but it duplicates the result because is matching the idExchangePlatform in twisted order. select p1.idRequest, p1.idPair, p1.idExchangePlatform AS idEP_a, p2.idExchangePlatform AS idEP_b, (p1.lastPrice + p2.lastPrice) AS lastPrice_sum from price as p1 JOIN price AS p2 ON p1.idRequest = p2.idRequest AND p1.idPair = p2.idPair AND p1.idExchangePlatform p2.idExchangePatform; This is the output i get from this query: idRequest | idPair | idEP_a | idEP_b | lastPrice_sum 1 1 1 2 195 1 1 1 3 135 1 1 2 3 140 1 1 2 1 195 1 1 3 1 135 1 1 3 2 140 Is there a way to prevent this with some CLAUSE?

I have read about pivot function but I am not sure if If it helps me in this situation. Essentially I have to produce my vertical data set horizontally. For instance the first table shows the vertical data set and ideally i would like to convert that into how the second table looks. Any help would be appreciated!

I have the following table : | Column1 | Column2 | Column3 | Column4 | Account | | -------- | ------- | ------- | ------- | ------- | | One | Two | Three | Data1 | Acc1 | | One | Two | Three | Data2 | Acc2 | | One | Two | Three | Data3 | Acc3 | | Five | Six | Seven | Data3 | Acc4 | | Five | Six | Seven | Data1 | Acc1 | And I would like to make a query to obtain the following result : | Column 1 | Column2 | Column3 | Data1 | Data2 | Data3 | | -------- | ------- | ------- | ----- | ----- | ----- | | One | Two | Three | Acc1 | Acc2 | Acc3 | | Five | Six | Seven | Acc1 | NULL | Acc4 | I tried something like :

select Column1, Column2, Column3,
case
    when Column4 = 'Data1' then Account
end as Data1,
case
    when Column4 = 'Data2' then Account
end as Data2,
case
    when Column4 = 'Data3' then Account
end as Data3
from mytable group by Column1, Column2, Column3

But instead I got the following : | Column 1 | Column2 | Column3 | Data1 | Data2 | Data3 | | -------- | ------- | ------- | ----- | ----- | ----- | | One | Two | Three | Acc1 | NULL | NULL | | Five | Six | Seven | NULL | NULL | Acc4 | It seems that group by stops at the first distinct line, therefore it stops reading lines with One, Two Three once it found one, and of course same with Five, Six, Seven, therefore it only fills the Data columns according to the first match. How can I get to my desired behaviour ? Thanks in advance.

What's wrong with this Dynamic Pivot table query? I'm staring myself blind on this.

mysql> SET @sql = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   GROUP_CONCAT(DISTINCT
    ->     CONCAT(
    ->       'IF(q.label = ''',
    ->       label,
    ->       ''', 1, 2) AS ',
    ->       label
    ->    )
    ->   ) INTO @sql
    -> FROM question;
Query OK, 1 row affected (0.00 sec)

mysql> SET @sql = CONCAT('SELECT a.id, ', @sql, ', q.id
    '>       FROM answer a
    '>       INNER JOIN question q ON questionId = q.id
    '>       GROUP BY a.id');
Query OK, 0 rows affected (0.01 sec)

mysql> PREPARE stmt FROM @sql;
ERROR 1064 (42000): You have an error in your SQL syntax ... near ' q.id

Why does the first one pass but the second one blows up? Adding PREPARE stmt FROM @sql; on the GROUP_CONCAT query gives me this: You have an error ... near 'IF(q.label = 'Q1', 1, 2) AS Q1,IF(q.label = 'Q2', 1, 2) AS Q2,IF(q.label = '', 1' at line 1 I'm not expecting the label to be empty on the last if, but I don't see how that would blow up the last query. The query without the dynamic part returns this:

+----+-------+----+
| id | label | id |
+----+-------+----+
|  1 | Q1    |  1 |
|  2 | Q2    |  1 |
|  3 | Q1    |  1 |
|  4 | Q2    |  1 |
+----+-------+----+

I am looking at moving a large Excel spreadsheet to an SQL database - most likely MySQL. The excel spreadsheet contains 12 worksheets, with one of them being the Master, and the other 11 set up to gather information into separate comparitive tables and charts based on the data in the Master worksheet. The master worksheet is a pivot table containing 90 columns and over 3000 rows. In the past, User's have tried to access this file at different times to edit it as it is on a network drive. Obviously, this not an ideal solution and I would like to set up this spreadsheet on a database. From my brief research so far, I have seen that phpMyAdmin is a popular solution to export an excel worksheet, saved as a .csv file to a MySQL database. The solution would need to leave the data very accessible to a handful of users who have good excel skills but almost no programming knowledge. I am wondering if the best solution is to move the entire spreadsheet to a database - ie. Convert the Master worksheet to a .csv file and export it using phpMyAdmin (Is it easy to set-up pivot tables again once this data is exported?), and then on MySQL, create the tables and charts that are currently on our other worksheets so that users can view and add/take from them using phpMyAdmin, or is there a better solution that would save me having to create the other 11 worksheets on MySQL? Or could the MySQL add-in for Excel be used in some way to generate a local copy of the data and other worksheets on everyone's local drive? My knowledge of databases is elementary to say the least, so any help at all would be greatly appreciated.

I have a database with the structure:

Region    | OpenServicesID | DFC
K00001    | 1400           | 4
K00002    | 1300           | 3
K00001    | 1200           | 4
K00001    | 1100           | 4
K00002    | 1000           | 4

I have to present the data table in the following format:

Region | OpenServicesID | DFC | OpenServicesID | DFC | OpenServicesID|DFC
K00001 | 1400           | 4   | 1200           | 4   | 1100          | 4
K00002 | 1300           | 3   | 1000           | 4   | Null          |Null

I have a table with the following fields: p1 p2 s1 s2 where p1 -> player1, p2 -> player2, s1 -> game1, s2 -> game2 The number of players can vary from 3 - 10. The table have the results between them. For example let's suppose that we have 4 players: TIM, ROB, NICK, GEORGE and the matches between them: p1 p2 s1 s2 TIM ROB 4 0 NICK GEORGE 4 0 TIM NICK 0 4 ROB GEORGE 4 0 TIM GEORGE 4 0 ROB NICK 4 0 What I want to produce with the query is something like this: TIM ROB NICK GEORGE TIM X 4-0 0-4 4-0 ROB 0-4 X 4-0 4-0 NICK 0-4 4-0 X 4-0 GEORGE 0-4 0-4 0-4 X Is it possible somehow in MySQL? Thanks in advance.

I have a table that contains the values +-------------+--------------+---------------------+-------------+-------------+--------------+-------+---------+-----------+---------------+--------------+ | ID | TYPE | LINE_1_ADDR | LINE_2_ADDR | LINE_3_ADDR | CITY_NAM | STATE | COUNTRY | EFFEC_DT | ROW_UPDATE_DT | ADDR_ROLE_CD | +-------------+--------------+---------------------+-------------+-------------+--------------+-------+---------+-----------+---------------+--------------+ | 10114753088 | SBS CUSTOMER | 2095 CAPP HARLIN RD | Apt #225 | | SIMPSONVILLE | MN | USA | 27-Jun-04 | 20-Mar-15 | BILL | | 10114753088 | SBS CUSTOMER | 304 S MAGNOLIA ST | | P.NO:516 | Toronto | ON | CAN | 27-Jun-04 | 21-Mar-15 | PHYSICAL | | 10114753088 | SBS CUSTOMER | 3013 Xenium ln | | | LAKEVILLE | MN | USA | 27-Jun-04 | 20-Mar-15 | BILL | | 10114753089 | SBS CUSTOMER | 123 hancock ST | | apt 214 | quincy | MA | USA | 27-Jun-04 | 21-Mar-15 | PHYSICAL | +-------------+--------------+---------------------+-------------+-------------+--------------+-------+---------+-----------+---------------+--------------+ I would like to have the data represented as: +-------------+--------------+---------------------+-----------+-----------+-------------------+-----------+-----------+----------------+-----------+-----------+--------------+---------+-----------+---------+---------+---------+-----------+-----------+-----------+------------+------------+------------+---------------+---------------+---------------+----------------+----------------+----------------+ | ID | TYPE | ADDR_L1_1 | ADDR_L1_2 | ADDR_L1_3 | ADDR_L2_1 | ADDR_L2_2 | ADDR_L2_3 | ADDR_L3_1 | ADDR_L3_2 | ADDR_L3_3 | City_1 | City_2 | City_3 | State_1 | State_2 | State_3 | Country_1 | Country_2 | Country_3 | EFFEC_DT_1 | EFFEC_DT_2 | EFFEC_DT_3 | INACTIVE_DT_1 | INACTIVE_DT_2 | INACTIVE_DT_3 | ADDR_ROLE_CD_1 | ADDR_ROLE_CD_2 | ADDR_ROLE_CD_3 | +-------------+--------------+---------------------+-----------+-----------+-------------------+-----------+-----------+----------------+-----------+-----------+--------------+---------+-----------+---------+---------+---------+-----------+-----------+-----------+------------+------------+------------+---------------+---------------+---------------+----------------+----------------+----------------+ | 10114753088 | SBS CUSTOMER | 2095 CAPP HARLIN RD | Apt #225 | | 304 S MAGNOLIA ST | | P.NO:516 | 3013 Xenium ln | | | SIMPSONVILLE | Toronto | LAKEVILLE | MN | ON | MN | USA | CAN | USA | 27-Jun-04 | 27-Jun-04 | 27-Jun-04 | 20-Mar-15 | 21-Mar-15 | 20-Mar-15 | BILL | PHYSICAL | BILL | | 10114753089 | SBS CUSTOMER | 123 hancock st | Apt #214 | | | | | | | | quincy | | | MA | | | USA | | | 27-Jun-04 | | | 21-Jun-04 | | | PHYSICAL | | | +-------------+--------------+---------------------+-----------+-----------+-------------------+-----------+-----------+----------------+-----------+-----------+--------------+---------+-----------+---------+---------+---------+-----------+-----------+-----------+------------+------------+------------+---------------+---------------+---------------+----------------+----------------+----------------+ I am trying to use pivot function but able to get exact logic how to get this. Can anyone provide some inputs.

I have a table with below structure: create table test_table (queuename number, duration_sum number, rating_sum number, rating_avg number, rating_cnt number ) Here is the sample data: insert into test_table (queuename, duration_sum, rating_sum, rating_avg,rating_cnt) values (1000,50,40,60,70); insert into test_table (queuename, duration_sum, rating_sum, rating_avg,rating_cnt) values (1010,12,40,25,34); insert into test_table (queuename, duration_sum, rating_sum, rating_avg,rating_cnt) values (2000,50,34,60,23); insert into test_table (queuename, duration_sum, rating_sum, rating_avg,rating_cnt) values (3000,90,40,60,67); commit; What I need is below result: queuename 1000 1010 2000 3000 duration_sum 50 12 50 90 rating_sum 40 40 34 40 rating_avg 60 25 60 60 rating_cnt 70 34 23 67 Im trying to use pivot for this and this is what I have written so far: select * from ( select queuename, 'duration_sum' as measure, duration_sum as value from test_table union all select queuename, 'rating_sum' as measure, rating_sum as value from test_table union all select queuename, 'rating_avg' as measure, rating_avg as value from test_table union all select queuename, 'rating_cnt' as measure, rating_cnt as value from test_table ) pivot ( max(value) for queuename in (1000 as "1000", 1010 as "1010", 2000 as "2000", 3000 as "3000") ) order by measure; I this a better approach for pivoting the result? I guess there might be a better approach for using pivot statement rather than using union. I should say that 'queuename' is unique. Thanks in advance.

have a table of ideas that stores responses from evaluators and stores as one row per evalautor For ex : Evalautor 1 evaluated idea A then in table record gets added as IDEA A, COMMENTS , EVALUATOR ID as single idea have 4 evaluator response and i need to show data in this format IDEA ID ||. EVALUATOR 1 || EVALUATOR 2|| EVALUATOR 3 || EVALUATOR 4 1 -- Comments -- Comments -- Comments -- Comments Need assistance in creating logic to get information in format given above. Query i am running : SELECT * from Toy_ideas GROUP BY evaluator id but getting results as Ideaid -- comments -- evaluator id

I got this great query that shows the latest backup for each existing database .

SELECT
    M.name,
    [Recovery Model] = 
        M.recovery_model_desc,
    [State] = 
        M.state_desc,
    [Last Full Backup] = 
        FORMAT(ISNULL(M.D, '19000101'), 'dd-MM-yyyy hh:mm'),
    [Last Differential Backup] = 
        FORMAT(ISNULL(M.I, '19000101'), 'dd-MM-yyyy hh:mm'),
    [Last log Backup] = 
        FORMAT(ISNULL(M.L, '19000101'), 'dd-MM-yyyy hh:mm')
FROM
(
    SELECT
        db.name,
        db.state_desc,
        db.recovery_model_desc,
        a.type,
        a.backup_finish_date
    FROM master.sys.databases AS db
    LEFT OUTER JOIN msdb.dbo.backupset AS a
      ON a.database_name = db.name
) AS Sourcetable
PIVOT
(
    MAX(backup_finish_date)
    FOR type IN
    (
        D,
        I,
        L
    )
) AS M --ostRecentBackup
WHERE name NOT IN
(
    N'master',
    N'msdb',
    N'model',
    N'tempdb'
);

I noticed it uses pivoting - what if I had to add the location too? that would be multiple columns . Either by pivoting using multiple columns , or unpivoting , how could I add the location of each backup type to the above query? basically where is the latest full backup, where is the latest diff and the latest log (if any). this is the script I use to see the location of the backup:

SELECT   
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.TYPE,
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupset.backup_size * 1.024 /1024/1024 as [Backup size in MB],
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
           ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
--WHERE msdb.dbo.backupset.database_name = 'PivCRM_Prod_Online_ED'
--AND TYPE = 'D'  -- L D and I
ORDER BY msdb.dbo.backupset.backup_start_date DESC

the question is: how would I add the location of the backup (each type) to the first query?

I've got a simple parent-child tree relationship that was built recursively | Parent | Child | | -------- |-------------- | | A | B | | B | E | | B | C | | E | NULL | | C | NULL | | A | F | | F | NULL | this table comes from a recursive CTE:

with tree(parent, child) as (...)
select * from tree

I want to pivot it into | Parent| Child1 | Child2 | | -------- |-----|--------- | | A | B | E | | A | B | C | | A | F | | I've achieved this with a series of hardcoded joins, is something like this possible with somehow a dynamic pivot up to the Nth Child? The current code is something like this:

with tree(parent, child) as (...)
select 
   P.parent, P.child as child1, C2.child as child2, C3.child as child3 
from tree as P
left join tree as C2
 on P.child = C2.parent
left join tree as C3
 on C2.child = C3.parent

i have multiple rows and want to make dynamic column i tried below code with case it work fine but i need the column to be dynamic based on the count of max Code | EventId | Code | Date| | -------- | -------------- | -------- | | 1| A12|12/12/2020| | 1| A13|12/12/2020| | 2| A12|12/12/2020| | 2| A13|12/12/2020| | 3| A13|12/12/2020| i want it like below table | EventId | Code1 | Date1| Code2 | Date2| | -------- | -------------- | -------- | -------------- | -------- | | 1 | A12|12/12/2020| A13| 12/12/2020| | 2 | A12|12/12/2020| A13|12/11/2020| | 3 | A13|12/12/2021|

WITH Ranked AS (
    SELECT
        EventID,
        Date,
        Code 
        rn = ROW_NUMBER() OVER (PARTITION BY  EventID ORDER BY Date)
    FROM TableEvent
)
SELECT
    EventID,
    MAX(CASE WHEN rn = 1 THEN Date END) AS Date1,
    MAX(CASE WHEN rn = 1 THEN Code END) AS Code1, 
    MAX(CASE WHEN rn = 2 THEN Date END) AS Date2,
    MAX(CASE WHEN rn = 2 THEN Code END) AS Code2, 
FROM Ranked
GROUP BY EventID;

I'd like some help with regards to creating a view in PostgreSQL using the crosstab function. I've been trying to create a view as follows:

create or replace view csv_data as with answers as(
select * from crosstab('select a.column1, q.column2, a.column3 from answer a 
								join question q on a.question_id = q.id order by 1,2')
as final_result (column1 bpchar, question_1 varchar(500),  question_2 varchar(500), question_3 varchar(500)))
select q.name as "Column1", a.question_1 as "Question1", a.question_2 as "Question_2", a.question_3 as "Question_3"
							from answers a join evaluation e on e.id = a.eid
							join questionnaire q on q.id = e.qid
							where q.active = true
						    order by q.name;

The query above has been slightly modified in order to be in this post, but it does not change the main point in question. The point being, while trying to perform a select on the view with another user, whose privileges are restricted to a SELECT on such view, I was denied access to the table answer inside the crosstab query. What bothers me is that, the ownership chaining is woking perfectly well in all other cases, only in the query within the crosstab function it does not chain privileges and tries to perform a select using the executing user privileges instead of the view owner. I'd like to know whether it is possible to solve this issue while still using the crosstab. If not, is there any other way?

I have this PIVOT table below. I have to dynamically create column headings based on the itemid value (1 to 50) in each order. then horizontally list them. in this example, it works, but it splits the rows. what can I do to change that and group them? I feel I missing one thing here and can't figure it out. If I comment out the ProductDesc pieces, it behaves correctly with just item1.item_id and item2.item_id which displays the SKU horizontally. Now I need item1.item_name and item2.item_name to display next to the item_id's horizontally. In this example, I achieve that, but lines are split. any help would be greatly appreciated CREATE TABLE #table1 (orderid nvarchar(256), itemid varchar(10),productid nvarchar(256), ProductDesc varchar(max)) insert into #table1 values ('001323232','1','ABC30013', 'LOVESEAT 3PC'), ('001323232','2','DFE30013', 'SOFA DINING SET') --select * from #table1 Select* From ( Select 'HG-KKSHGSRMN1XHG'as measurement_id , 'purchase' as event_name , 'https://www.test.com/ ' as [event_param.page_hostname], '1665100800' as timestamp_micros, 'offline' as [event_param.campaign_name], 'In-Store Sales' as [event_param.campaign_source] , 'item'+cast(Itemid as varchar) + '.item_id' as ItemId_ProductId , OrderId, ProductId as ProductId, -- ItemId as ItemId ProductDesc as ProductDesc, -- concat('item',row_number() over (partition by Orderid order by productid)) as ItemId_ProductId, -- 'item'+cast(Itemid as varchar) + '.item_name' as ItemId_ProductDesc 'item'+cast(Itemid as varchar) + '.item_name' as ItemId_ProductDesc -- ROW_NUMBER() OVER(ORDER BY cast(itemid as int) ASC) AS ItemId_ProductDesc FROM #table1 ) src PIVOT ( MAX(ProductId) For ItemId_ProductId in ([item1.item_id], [item2.item_id] ) ) mypivot PIVOT ( MAX(ProductDesc) For ItemId_ProductDesc in ([item1.item_name], [item2.item_name] ) ) mypivot2

I have a table in which new entries come in every second. The table is just like this: | value1 |data1| Value2| data2| |-|-|-|-| | 1 | Er | 2 | Er| | 3 | Er | 4 | Er| Now insert these value in other table as |Value | data| |-|-| |1| Er| |2| Er| |3| Er| |4| Er| And when new data insert into 1 table insert that data into second one in each execution of query but not previous one.