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 have two table in different database host host. activity_table in **Host A** and presence_table in **Host B**. presence_table used by binary app where i don't have access to the source code but i have full access to the database. the presence_table table have this following structure | column | type | | -------- | -------------- | | id | integer | | finger_id | varchar | | time_in | timestamp | | time_out | timestamp | | ... | ...| **Example record** | id | finger_id | time_in | time_out| |-|-|-|-| |1|12a766d|2022-08-01 12:32:01|2022-08-01 20:00:03| The activity_table is used by app which we are developing with this following structure | column | type | | -------- | -------------- | | id | integer | | employee_id | integer | | description | text | | activity_time | datetime | | created_at | timestamp | | updated_at | timestamp | **Example Record** |id|employee_id|description|activity_time|created_at|updated_at| |-|-|-|-|-|-| |1|21|Enter the office|2022-08-01 12:32:01|2022-08-01 12:32:02|| |2|21|Exit from office|2022-08-01 20:00:03|2022-08-01 12:32:02|| and of course i have employee_finger_table to pair employee with finger. My question is how to automatically sync the presence_table to activity_table so when there is new record or updated record in presence_table synced to activity_table based on above example ? is there any tools or idea to address this problem ?

Please I need help on how I can transform the data that appears in the attached photo. Maybe Unpivot SQL?

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.

Table name: employment_by_industry https://dbfiddle.uk/hkwDS2DS For each row, I want to select the column name that has the maximum value: ------------- What is the most succinct way to do that in PostgreSQL, with the column names only being listed once in the SQL query? For example, this is how it would be done in Oracle:

select objectid, max(col_name) keep (dense_rank first order by col_val desc) max_col_name
from employment_by_industry
unpivot (
  col_val
  for col_name in (
    agr_forest_fish, mining_quarry, mfg, electric, water_sew  --cols only listed once
  )
)
group by objected

https://dbfiddle.uk/DeaRoikf

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.

How-to convert rows (for group) into columns. Here is the source table that have: **update: added Id and IdGestion columns**

Id	IdGestion	PeticionId	Field		Value
48	17		    16		    Type		1
49	17		    16		    SubType		1
50	17		    16		    Units		1
51	18		    16		    Type		1
52	18		    16		    SubType		2
53	18		    16		    Units		2
54	19		    16		    Type		3
55	19		    16		    SubType		6
56	19		    16		    Units		1

How-to get the Pivot table (target):

RequestId	Type    SubType Units
16			1		1		1
16			1		2		2
16			3		6		1

I try using PIVOT, and ROWNumber(), but get data wrong.

select g.PeticionId,  Tipo,SubTipo,Unidades from [GestionSaliente] g
inner join (
SELECT PeticionId,  Tipo,SubTipo,Unidades
FROM
    (select  gs.PeticionId, Campo, Valor 
	  , ROW_NUMBER() OVER (PARTITION BY gs.IdGestion ORDER BY gsd.Id) as rn
	   FROM [GestionSaliente]  gs
		inner join [GestionSalienteDato] gsd
		on gs.Id = gsd.GestionSalienteId --where  PeticionId = 16 
		
    ) AS source
    PIVOT
    (
	 MIN(Valor)
     FOR Campo IN (Tipo,SubTipo,Unidades)
    ) as pvt

) a
on a.PeticionId = g.PeticionId

UPDATED: It works now, I think

select g.PeticionId,  Tipo,SubTipo,Unidades from [GestionSaliente] g
inner join (


SELECT PeticionId,  Tipo,SubTipo,Unidades
FROM
    (select  gs.Id IdGroup, gs.PeticionId, Campo, Valor 
     FROM [GestionSaliente]  gs
     inner join [GestionSalienteDato] gsd on gs.Id = gsd.GestionSalienteId
    ) AS source
    PIVOT
    (
     MIN(Valor)
     FOR Campo IN (Tipo,SubTipo,Unidades, Id, Peticion)
    ) as pvt
where PeticionId = 16

***FOR Campo IN (Tipo,SubTipo,Unidades, Id or IdGroup, Peticion)***

I'm redesigning an attendance system used in my company. Originally, an attendance sheet was rendered on a grid that was getting week-by-week data pivoted. I've opted for a *scheduler* control by DevExpress. A sign-in and a sign-out are to be represented as two 'appointments' now. The scheduler requires a very specific data source for appointments. Here is my attendance table: The DutyIn_Manual is what the employee reports and the DutyIn is the actual time captured. DutyIn_Manual is to be shown on the Scheduler. Similarly, DutyOut_Manual goes into view. Now, I need to split one row into two: One for Duty In and one for Duty Out.

I would be grateful for your assistance in helping me get solution for my project weather query problem. I have arrived at the query below and is working: "); $first_row = true; while ($row = mysqli_fetch_assoc($result)) { if ($first_row) { $first_row = false; // Output header row from keys. echo ''; foreach($row as $key => $field) { echo '' . htmlspecialchars($key) . ''; } echo ''; } echo ''; foreach($row as $key => $field) { echo '' . htmlspecialchars($field) . ''; } echo ''; } echo(""); ?> The output has a long row of heading columns and I want to UNPIVOT the result of above query: R.weatherID R.Days R.locationID R.windDirection R1.weatherID R1.Days R1.locationID R1.windDirection R2.weatherID R2.Days R1.locationID R2.windDirection and get the short output format below: R.weatherID R.Days R.locationID R.windDirection R1.weatherID R1.Days R1.locationID R1.windDirection R2.weatherID R2.Days R1.locationID R2.windDirection Thank you very much for your time. EDIT: -- Changed R2.Days = (R2.Days + 1) - 5 to -- R1.Days = (R2.Days + 1) - 5 -- implies is there a similar pattern five days from this record -- -- Table structure for table R -- CREATE TABLE IF NOT EXISTS R ( W_id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT, weatherID VARCHAR(6) NOT NULL, Days INT(6) NOT NULL, locationID VARCHAR(8) NOT NULL, windDirection VARCHAR(6) NOT NULL, PRIMARY KEY (W_id) ); -- -- Index for table R -- ALTER TABLE R ADD UNIQUE KEY uDW (Days,W_id);

I have a **SQL Server** query that gets the sales data for a **44** store market chain, it includes 4 values for each store **(Sales, COGS, GP and GP Margin)**, I declared a **CTE** and joined it **44 times** for each store to get the **4** values as columns for each store as shown below: Query: Query1 but when I try to use PIVOT function with dynamic SQL it returns multiple nulls as shown below: Query:Query2 table ##tbl1 includes the data set that I want to pivot: I used the following query: (the three dots represent the rest of the columns to be pivoted) select * from ##tbl1 pivot (sum(total_sales) for s in ([50001 Sales],[50002 Sales],...)) as pv_tb pivot (sum(Margin) for m in ([50001 margin],[50002 margin],...)) as pv_tb1 pivot (sum(total_profit) for p in ([50001 profit],[50002 profit],...)) as pv_tb2 pivot (sum(total_cost) for c in ([50001 cost],[50002 cost],...)) as pv_tb3 I also use dynamic SQL to pass the column names inside PIVOT() without having to write each one of them individually, but for the sake of troubleshooting I just showed you how the query would look like. I can't get around using CROSS APPLY and UNPIVOT with this **132** column result, it's very hard to maintain. Can anyone help me find an easier way than this?

I have columns like role1, role2, role3, etc. They are all booleans. I would like to create a view on this table, that has a roles column of type text[]. If the columns were TRUE, FALSE, TRUE, the view would contain ["role1", "role3"]. Is there any good way to do this that doesn't explode into a ton of CASE WHEN´s? To clarify, I'd be fine with O(n) with CASE WHEN, but not O(2^n) which is what it currently seems to need. :)

I'm needing to unpivot the below table so that the output looks as shown in below image. Does this require me to perform an UNPIVOT twice on the dataset or can I accomplish my expected output by using UNPIVOT once and specifying all the available Month and Value columns? Should my script look something like the following to accomplish what I need? Select ID, Name, Age, Gender,Month,Value FROM (Select ID, Name, Age, Gender,Month1,Month2,Month3,Month4,Value1,Value2,Value3,Value4 FROM MyTable ) as cp UNPIVOT ( Month FOR Months IN (Month1, Month2, Month3,Month4), Value for Values IN (Value1,Value2,Value3,Value4) ) AS up;

**In this code I am converting the subjects(columns) English , Hindi , Sanskrith , into rows** DECLARE @colsUnpivot AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @colsUnpivot =stuff( (select ',' + quotename (C.name) from sys.columns c where c.object_id = OBJECT_ID('dbo.result2') for xml path(''), TYPE) .value('.', 'NVARCHAR(MAX)'), 1, 8, '' ); print @colsunpivot select @query = 'select Name, Subject,Marks from result2 unpivot ( marks for subject in (' + @colsunpivot + ')) as tab' exec sp_executesql @query; *Please explain what does* **for xml path(''), TYPE) .value('.', 'NVARCHAR(MAX)') do here .**

I was wondering if it is possible to change the output of this:

User           Database            Select  Insert  Update  Delete  Create  References  Alter   Drop    
-------------  ------------------  ------  ------  ------  ------  ------  ----------  ------  --------
mysql.session  performance_schema  1       0       0       0       0       0           0       0       
mysql.sys      sys                 0       0       0       0       0       0           0       0

into this:

Users				Privileges		performance_schema		sys
-----				----------		------------------		---
mysql.session		Select					1		
mysql.session		Insert					0		
mysql.session		Update					0		
mysql.session		Delete					0		
mysql.session		Create					0		
mysql.session		References				0		
mysql.session		Alter					0		
mysql.session		Drop					0		
mysql.sys			Select									0
mysql.sys			Insert									0
mysql.sys			Update									0
mysql.sys			Delete									0
mysql.sys			Create									0
mysql.sys			References								0
mysql.sys			Alter									0
mysql.sys			Drop									0

The query I used is this:

SELECT 
  DISTINCT
  USER "User",
  db "Database",
  IF(Select_priv = 'Y', '1 ', '0') AS "Select",
  IF(Insert_priv = 'Y', '1 ', '0') AS "Insert",
  IF(Update_priv = 'Y', '1', '0') AS "Update",
  IF(Delete_priv = 'Y', '1', '0') AS "Delete",
  IF(Create_priv = 'Y', '1', '0') AS "Create",
  IF(References_priv = 'Y', '1', '0') AS "References",
  IF(Alter_priv = 'Y', '1', '0') AS "Alter",
  IF(Drop_priv = 'Y', '1', '0') AS "Drop"
 FROM
	mysql.db
 ORDER BY
	USER, Db;

Any help would be really appreciated. Thanks in advance!!

I have a csv file, maybe 1000 or more rows, and echo row’s data is related to two table in mysql. Example,

table book(id, title, …)
table scene(id, book_id, short_content, position…)

And the csv file look like:

title,short_content1,position1,short_content2,position2,…
...

I want to extract data from it and save it to mysql. Also in the mysql maybe already have the same record, so I don’t want save the same data to mysql. My solution is: - First, fetch all the data related to the file, a big data set labeled BS. - Second, for each row in file compare to the BS and create the book, and store the book_id for his scene. - Finally, batch create the scene. This is my poor solution, and if the csv file has 1000 rows, then the solution will take a large memory space and 1000 + 1 times mysql calls. I see Bulk database update/insert from CSV file and Update Oracle database from CSV post. But I still confuse how to deal with csv file which related to multiple tables. Can anyone give me a better solution. Thanks.

I've been stuck on the following for quite a while, it seems like it should be relatively simple but I can't quite get my head round it.

I have a set of data which basically looks like the following:

-sql
+---------+--------+
| EventId | Field  |
+---------+--------+
|       1 | apple  |
|       1 | paper  |
|       1 | boy    |
|       2 | banana |
|       2 | cat    |
|       3 | girl   |
+---------+--------+

I would like to pivot this to look like this:

-sql
+-------+--------+------+
|   1   |   2    |  3   |
+-------+--------+------+
| apple | banana | girl |
| paper | cat    |      |
| boy   |        |      |
+-------+--------+------+

I am trying to fiddle around PIVOT, but I can't get the result I want. Am I looking at or using the wrong command? Is PIVOT the correct relational operator for this? This is the code I have been working on so far:

-sql
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', ' + QUOTENAME(EventID)
  FROM (
			select distinct
				EventId
			from Events
		) AS x;

SET @sql = 
N'select  ' + 
STUFF(@columns, 1, 2, '') +  ' 

into ##tempMap from
(
	select
		ev.EventID,ev.FieldName
	from Events ev
) as a
pivot 
(
      max(FieldName) for EventId in ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) as pivottable;';

EXEC sp_executesql @sql

select * from ##tempMap
drop table ##tempMap

The code above returns the following results because of the MAX aggregate function.

-sql
+-------+-----+------+
|   1   |  2  |  3   |
+-------+-----+------+
| paper | cat | girl |
+-------+-----+------+

I am pretty new to this and have been struggling over one issue. I have a result set which for each Opportunity in a table (id as primary key), provides a date for a first cashflow (DFCFW) as a column and the 10 following columns (CFW1, CFW2, ...., CFW10) being the 10 possible cashflows for each of the 10 following years, expected as anniversary dates of the first cashflow. I would like to create a view which displays, for all the opportunities, three columns: opportunity.id, date of the cashflow, cashflow; there should be 10 records for each opportunity. Any suggestion how to achieve this ? Thank you so much Fred --- To add some clarity: What I have today looks like this: What I am looking for is the following (dates are in European format):

I have data in the database in the below format And I want output data in this format

BudgetCategoryId | CostCenterDepartmentId | CostCenter | AccountNo | Amount    | Timestamp
714		         | 135	                  |  12010194  |       110 | 102000.00 | 01-01-2020

Basically **timestamp** in the desired result is '01-' + + I tried transposing, pivot, unpivot but unfortunately unable to get the desired result. Column Names: Id | BudgetCategoryId | CostCenterDepartmentId | Year | AccountNo | CostCenter | Month1 | Month2 | Month3 | Month4 | Month5 | Month6 | Month7 | Month8 | Month9 | Month10 | Month11 | Month12 | Budget PS: I am very new in SQL (RDBMS)

I sometimes have to unpivot tables, that means, converting all columns into rows. Generally that becomes a table with 2 columns (just to simplify things) - the first is the column name and the second is the column value. . In the example below I found out manually by looking at the column types in the original table, that the best column to store the value is nvarchar(256). **Questions:** How could I find this *"the best value to use as the values column type is nvarchar(256)"* out dynamically? I know I could use sql_variant for the values column type ? In this case can I ignore the collate database_default? That would be an expensive overkill - and I want to avoid that: > sql_variant can have a maximum length of 8016 bytes. This includes > both the base type information and the base type value. The maximum > length of the actual base type value is 8,000 bytes. --============================================================================= -- Unpivot the table - SUCCESSFUL --============================================================================= IF OBJECT_ID('TEMPDB.DBO.#UNPIVOT') IS NOT NULL DROP TABLE #UNPIVOT CREATE TABLE #UNPIVOT( [ColumnNames] [nvarchar](128) NULL, [AllCol] [nvarchar](256) NULL ) INSERT INTO #UNPIVOT([ColumnNames],[AllCol] ) SELECT ColumnNames ,AllCol --INTO TABLEBACKUPS.DBO.UNPIVOT_ FROM ( select [host_platform] = CAST (host_platform collate database_default as [nvarchar](256)), [host_distribution] = CAST (host_distribution collate database_default as [nvarchar](256)), [host_release] = CAST (host_release collate database_default as [nvarchar](256)), [host_service_pack_level] = CAST ([host_service_pack_level] collate database_default as [nvarchar](256)), [host_sku] = CAST ([host_sku] as [nvarchar](256)), [os_language_version] = CAST ([os_language_version] as [nvarchar](256)), [host_architecture] = CAST ([host_architecture] collate database_default as [nvarchar](256)) from sys.dm_os_host_info ) Radhe UNPIVOT ( AllCol FOR ColumnNames IN ( host_platform ,host_distribution ,host_release ,host_service_pack_level ,host_sku ,os_language_version ,host_architecture ) ) unpvt SELECT [ColumnNames] ,[AllCol] FROM #UNPIVOT