Custom SQL to Pivot... Syntax Help, Please
Chrissy Scott Jan 9, 2018 11:28 AMHi All,
I have the following custom SQL data source:
SELECT
[metric_definition].[sys_id] AS [sys_id],
[metric_definition].[table] AS [table],
[metric_instance].[end] AS [end],
[metric_instance].[definition] AS [definition],
[metric_instance].[start] AS [start],
[metric_instance].[calculation_complete] AS [calculation_complete],
[metric_instance].[value] AS [value],
[metric_instance].[id] AS [id],
[metric_instance].[dv_definition] AS [dv_definition],
[incident].[number] AS [number (incident)],
[incident].[opened_at] AS [opened_at],
[incident].[sys_id] AS [sys_id (incident)],
[incident].[dv_u_cause_code] AS [dv_u_cause_code],
[incident].[dv_u_technical_service] AS [dv_u_technical_service]
FROM [dbo].[metric_definition] [metric_definition]
INNER JOIN [dbo].[metric_instance] [metric_instance] ON ([metric_definition].[sys_id] = [metric_instance].[definition])
INNER JOIN [dbo].[incident] [incident] ON ([metric_instance].[id] = [incident].[sys_id])
I need to pivot the data on: [metric_instance].[end] and [metric_instance].[start]
So that I get Start and End as Pivot Field Names and the actual date/times as Pivot Field Values
I found this really good tutorial in Tableau's online help:
To pivot data using custom SQL
- Connect to your data.
- Double-click theNew Custom SQLoption in the left pane. For more information, seeConnect to a Custom SQL Query.
- In theEdit Custom SQLdialog box, copy and paste the following custom SQL query and replace the contents with information about your table:Select [Static Column]
, 'New Value (from Column Header 1)' as [New Column Header]
, [Pivot Column Values 1] as [New Values]
From [Table]
Union ALL
Select [Static Column]
, 'New Value (from Column Header 2' as [New Column Header]
, [Pivot Column Values 2] as [New Values]
From [Table]
Union ALL
Select [Static Column]
, 'New Value (from Column Header 3' as [New Column Header]
, [Pivot Column Values 3] as [New Values]
From [Table]
style="margin-top:9px" - New Value (from Column Header 1-3): New names that you give to the original column headers, which are used as row values in the pivot.
- Pivot Column Values 1-3: The columns whose values need to be pivoted into a single column.
- New Column Header: The name you give the new column that contains the new row values fromNew Value (from Column Header 1-3).
- New Values: The name give the new column that contains the original values fromPivot Column Values 1-3.
- Table: The table that you connected to.
- Click OK.
And... I still can't get the syntax right.
Can anyone help?
Thanks,
Chrissy