Dynamic Pivot in SQL Query without DECLARE
I have a custom/dynamic SQL to PIVOT column values. As number of Columns are not fixed it could increase or decrease we cannot have normal PIVOT . Instead we need to identify columns dynamically and prepare a dynamic SQL as well. COLUMN_NAME and COLUMN_VALUE needs to be PIVOTED number of rows in these 02 columns may increase decrease on each day run. Posting dummy data as main datacannot be posted. This query works great on database but since our Reporting Tool like Tableau etc. cannot support advance SQL like dynamic or DECLARE keywords Hence is there a way to do the same thing without DECLARE like getting Columns list (comma separated) in CTE with STRING_AGG or something Kindly help me writing PIVOT which pivots columns dynamically without DECLARE USE [DBOOO]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO CREATE TABLE [SA0067].[UNPIVOT] ( [RECORD_ID] int NULL, [METRIC_NAME] varchar(255) NULL, [NAME] varchar(255) NULL, [COLUMN_Name] nvarchar(4000) NULL, [COLUMN_VALUE] nvarchar(255) NULL) ) ; GO ----INSERT statement INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (1,'ABC','AJ','ID',1); INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (2,'ABC','AK','ID',6); INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (3,'XYZ','RJ','ID',4); INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (4,'XYZ','JK','ID',5); INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (5,'XYZ','JJJ','ID',11); INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (6,'REC','MJ','ID',42; INSERT INTO [SA0067].[UNPIVOT] ([RECORD_ID],[METRIC_NAME],[NAME],[COLUMN_Name],[COLUMN_VALUE] ) VALUES (7,'REC','SG','ID',33); --- DYNAMIC SQL thats needs to be re-written DECLARE @COLUMNNAMES NVARCHAR(MAX)='' DECLARE @SQL NVARCHAR(MAX)='' ;WITH COL_NM AS (SELECT DISTINCT '['+COLUMN_NAME+'],' AS CN FROM DBOOO.[SA0067].[UNPIVOT]) SELECT @COLUMNNAMES +=CN FROM COL_NM SET @COLUMNNAMES =LEFT(@COLUMNNAMES,LEN(@COLUMNNAMES)-1) SET @SQL ='SELECT * FROM ( SELECT RECORD_ID,METRIC_NAME,NAME,COLUMN_Name,COLUMN_Value FROm DBOOO.[SA0067].[UNPIVOT] ) t PIVOT ( MAX(COLUMN_Value) FOR COLUMN_Name IN ('+@COLUMNNAMES+') ) AS PT' EXECUTE sp_executesql @SQL
Problem sorting Pivot Table according to date
Hi, Attached my Excel Raw Data and resulted Pivot Table. I cannot find a way to sort pivot table row according to date. Have tried to change cell format to date and sorting after that but no luck. 🙂 My customer wants date in a format as shown in column B. Any help is appreciated, thanks!
Excel could not get the source data for the PivotTable report from the disk
I have a spreadsheet with couple of pivots (around 7 pivots) and have a slicer connecting to all pivots on top. But when I open the excel the first time and try to click on something in the slicer, then I get the error - "Excel could not get the source data for the PivotTable report from the disk". If I click again I get the second error -"This PivotTable Report is invalid. Try refreshing the data (On the Options tab, click Refresh.)". If I do refresh all the data, the issue is fixed and the slicer works fine. But the error occurs every time I open the sheet. Any tips on how to fix this? Thanks!
Set width for `PivotItem` in Office UI Fabric
I am using Pivot and PivotItem from Office UI Fabric to display my content in tabs. Currently when the tab renders, all the tabs are left aligned. I need to display the tabs with equal width so that they occupy the 100% width of the page. Below is the code for Pivot. <Pivot linkFormat={PivotLinkFormat.tabs} linkSize={PivotLinkSize.large} styles={pivotStyles}> <PivotItem headerText="Foo"> <Label>Pivot #1</Label> </PivotItem> <PivotItem headerText="Bar"> <Label>Pivot #2</Label> </PivotItem> <PivotItem headerText="Bas"> <Label>Pivot #3</Label> </PivotItem> <PivotItem headerText="Biz"> <Label>Pivot #4</Label> </PivotItem> </Pivot> Below is the code that I could figure out to add styles to Pivot. But we do not have styles attribute for PivotItem. const pivotStyles:IPivotStyles = { link: {}, linkContent: {}, linkIsSelected: {}, text: {}, icon: {}, count: {}, root: { //background: DefaultPalette.greenDark } }; How can I apply style to PivotItem so that I can add width to it?
Hiding useless sub rows in pivot hierarchy without changing the calculation
Hello, I have the following issue with excel pivot: When I have a table with projects, sub projects and sub sub projects: and create a pivot table, it looks like this: When i try to hide the "(empty)" rows, pivot removes the whole row, so all that's left is the row that doesn't have any "(empty)" values in "Main Project", "Sub Project" or "Sub Sub Project": But what I actually want is the following: I don't want to filter data, I just want to hide hierarchy levels, when they are not needed. I am using SQL Server data and VBA with the copyfromrecordset method. I would prefer a simple solution without VBA or SQL Server but if there is a way with VBA or SQL Server to fix this hierarchy problem in pivot tables, it would be perfectly fine too. Thank you very much for your help and best regards.Pivot table displaying percentage
How can I get the following output using pivot table or any other way: I am able to get Group and total but unable to get the %. The % scenario is, if 2012 has all X then it is 100%. Likewise following years have their respective X, Y and Z percentage which has to be calculated and display as in the above picture.
Combine Data from Multiple Tables to 1 Pivot When Having Duplicate Values
See attached file. I have a table (Table1) with data of number of resources required every month per Product / per Domain / per Team. So a single product can appear many time in the 'product'column. Creating a Pivot for this Table1 is easy so I can see how much effort is required for each product or how much effort invests every Team along the year. I have many products & many department. Due to internal limitations, I have to divide Table1 so each department will get the same table structure but will fill in different required effort per month. So....I haveTable2 for Software andTable3 for Mechanics department and can have more tables for otherdepartment. I couldn't do it with the Pivot Data Model since 'Product' column contains duplicate values. So - How do I combine all the tables to 1 pivot or to 1 table and from there creating the pivot?
Problems using Pivot tables in Excel 16.25 on Mac
Hello folks - been trying to use an Excel file with a pivot table created on a Windows machine - the pivot table doesn't work - can't edit, refresh, use filters (slice/dice) or anything. After looking online I joined the Office Insider program and tried to pull down the 16.6 version of Excel as suggested - however all I got thus far is version 16.25 - Doesn't look like there even IS a version 16.6 according to MSFT release notes for Excel. (But there is a video on YT showing how to pull down that version which supposedly does work with Pivot tables...) This is Office 365 subscription as installed from IBM app store (and updated via Office Insider) on a MacBook PRO. Has anyone encountered this and have a solution for it? Thanks!Excel/ Power Query/ Pivot as a web part in SharePoint?
Hello, I think my question can pertain to Power Query, Pivot, and BI, but I am hoping to get a better understanding of which route is most efficient, accessible, and if there are any things that need to be considered when adding a component to SharePoint. I am not a coder, so without training, that is not a viable option for me :). If I am in the wrong place, please let me know. I've also posted this request in another excel forum, but thought that this may reach a wider audience - apologies if you're seeing this twice. Thank you! Scenario: I have a site collection in SharePoint and want to roll-up tasks from sub-sites to the the Parent. The owner of the Parent needs to be able to easily pull information from the data (point and click to filter for what is needed). After a few considerations, I thought why not export all excel files from SharePoint, import the tables into Power Query, combine all tables, and then make it fancy and easy to interact with either in Pivot, PowerBI, or even PowerApps or Flow and add it to the home page in SharePoint as a web part. Maybe even a mini dashboard with slicers?? I've completed the steps through combine all tables in Power Query. But then thought, how will I best ensure that the data refreshes (and can this even happen), how best can I add this as a web part to SharePoint - there may be restrictions in who can view or how info can be shared in SharePoint with PowerBI, and are there any nuances that I need to consider in adding this data to SharePoint. All insights are greatly appreciated. Thank you in advance. Kerry
