How to Transpose SQL Queries in Bold BI

Bold BI is a powerful business intelligence tool that allows you to connect to various data sources, including SQL databases. One of the features of Bold BI is the ability to transpose SQL queries. This means you can alter the structure of your data by converting columns into rows, and vice versa. This can be particularly useful when you want to change the layout of your data for better analysis or visualization.

Transposing SQL Queries in Bold BI

1. Refer to the Connecting Bold BI to SQL data source to successfully connect to it.
2. After dragging and dropping the required table in the data design view page, switch to code the view mode in Bold BI as follows.
   
   
3. After switching to the code view mode, you can find the query designer page in which you can alter the query to transpose the below highlighted column to rows.
   
   
4. Alter the input query with PIVOT(), to transpose the data columns and rows using the code view mode as follows.
   Example query:

SELECT * FROM (SELECT
 [Patient_details_db].[Date] AS [Date],
 [Patient_details_db].[PatientID] AS [PatientID],
 [Patient_details_db].[HeartRate] AS [HeartRate],
 [Patient_details_db].[BloodPressure] AS [BloodPressure],
 [Patient_details_db].[BodyTemperature] AS [BodyTemperature],
 [Patient_details_db].[SleepHours] AS [SleepHours],
 [Patient_details_db].[DeepSleepHours] AS [DeepSleepHours],
 [Patient_details_db].[DistanceWalked] AS [DistanceWalked],
 [Patient_details_db].[TargetDistance] AS [TargetDistance]
FROM [dbo].[Patient_details_db] AS [Patient_details_db]) [Sheet]
PIVOT(
SUM([HeartRate])
FOR [PatientID] IN (
  [PATIENT_1],
  [PATIENT_2],
  [PATIENT_3],
  [PATIENT_4],
  [PATIENT_5])
) AS pivot_table

5. Click on the Run button to execute the altered query.
   
   
6. Click on the Update button and you can find the result where the row is changed to a column.
   
   

Conclusion

In conclusion, while Bold BI has some limitations when it comes to SQL queries, there are workarounds that allow you to achieve the same results. By using views, stored procedures, and expressions, you can perform complex analyses and create powerful dashboards and reports. Please refer here for more details

References

• Bold BI Documentation
• SQL Server Views
• SQL Server Stored Procedures
• How to transform excel rows to columns & use in dashboard?