13 Replies Latest reply on Jan 11, 2018 11:02 AM by Hari Ankem

    Custom SQL to Pivot... Syntax Help, Please

    Chrissy Scott
    Chrissy Scott Jan 9, 2018 11:28 AM

      Hi 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

      1. Connect to your data.
      2. Double-click theNew Custom SQLoption in the left pane. For more information, seeConnect to a Custom SQL Query.
      3. 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"
      4. 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.
      5. Pivot Column Values 1-3: The columns whose values need to be pivoted into a single column.
      6. New Column Header: The name you give the new column that contains the new row values fromNew Value (from Column Header 1-3).
      7. New Values: The name give the new column that contains the original values fromPivot Column Values 1-3.
      8. Table: The table that you connected to.
      9. Click OK.

       

      And... I still can't get the syntax right.

       

      Can anyone help?

       

      Thanks,

      Chrissy

      • 362 Views
      • Categories: Data Sources & Connections
      • Tags:
        • 1. Re: Custom SQL to Pivot... Syntax Help, Please
          Hari Ankem
          Hari Ankem Jan 9, 2018 11:48 AM (in response to Chrissy Scott)

          Please try with the below query. The pivot field name is date_type and the pivot field value is date_value.

           

          SELECT [metric_definition].[sys_id] AS [sys_id],

          [metric_definition].[table] AS [table],

          [metric_instance].[definition] AS [definition],

          'Start Date' AS [date_type],

          [metric_instance].[start] AS [date_value],

          [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])

          UNION ALL

          SELECT [metric_definition].[sys_id] AS [sys_id],

          [metric_definition].[table] AS [table],

          [metric_instance].[definition] AS [definition],

          'End Date' AS [date_type],

          [metric_instance].[end] AS [date_value],

          [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])

          • 2. Re: Custom SQL to Pivot... Syntax Help, Please
            Chrissy Scott
            Chrissy Scott Jan 9, 2018 3:23 PM (in response to Hari Ankem)

            Hi Hari,

             

            I feel like this is really close, but I need a version of the start and end fields to also be kept intact. Is that possible? Basically, I need the data to pivot as you did above, but I also need an end column and a start column.

             

            I use those fields for some other calculated fields.

             

            Thanks,

            Chrissy

            • 3. Re: Custom SQL to Pivot... Syntax Help, Please
              Hari Ankem
              Hari Ankem Jan 9, 2018 3:46 PM (in response to Chrissy Scott)

              OK. Try this:

               

              SELECT [metric_definition].[sys_id] AS [sys_id],

              [metric_definition].[table] AS [table],

              [metric_instance].[definition] AS [definition],

              'Start Date' AS [date_type],

              [metric_instance].[start] AS [date_value],

              [metric_instance].[start] AS [start],

              [metric_instance].[end] AS [end],

              [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])

              UNION ALL

              SELECT [metric_definition].[sys_id] AS [sys_id],

              [metric_definition].[table] AS [table],

              [metric_instance].[definition] AS [definition],

              'End Date' AS [date_type],

              [metric_instance].[end] AS [date_value],

              [metric_instance].[start] AS [start],

              [metric_instance].[end] AS [end],

              [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])

              1 of 1 people found this helpful
              • 4. Re: Custom SQL to Pivot... Syntax Help, Please
                Chris McClellan
                Chris McClellan Ambassador Jan 9, 2018 4:02 PM (in response to Chrissy Scott)

                I'd be suggesting to dump your custom SQL and create the same query in Tableau natively in the data window.  It will make the pivot easier, and Tableau can fire the SQL that it wants rather than being forced to run the custom SQL all the time.

                1 of 1 people found this helpful
                • 5. Re: Custom SQL to Pivot... Syntax Help, Please
                  Chrissy Scott
                  Chrissy Scott Jan 10, 2018 7:53 AM (in response to Chris McClellan)

                  Hi Chris,

                   

                  When I create this data source, I am connecting with Microsoft SQL Server and joining three tables. Then I eliminate a bunch of unneeded fields. But, I don't see the option to pivot using this connection:

                   

                   

                  So, the first thing I tried was exporting the data to .csv. That allowed me to pivot, but, as far as I know, there's no way for me to get that .csv file to refresh automatically. So that option was out. That's how I got to this custom SQL place.

                   

                  Is there something I'm missing that would make a pivot possible using a method above?

                   

                  Thanks,

                  Chrissy

                  • 6. Re: Custom SQL to Pivot... Syntax Help, Please
                    Chrissy Scott
                    Chrissy Scott Jan 10, 2018 8:36 AM (in response to Hari Ankem)

                    Hi Hari,

                     

                    This seems to do the trick but for some reason I'm getting different results with this data source than I did with my other. I'm going to do some validation and I'll be back.

                     

                    Thanks!

                    • 7. Re: Custom SQL to Pivot... Syntax Help, Please
                      Chris McClellan
                      Chris McClellan Ambassador Jan 10, 2018 12:34 PM (in response to Chrissy Scott)

                      Chrissy Scott wrote:

                       

                      Hi Chris,

                       

                      When I create this data source, I am connecting with Microsoft SQL Server and joining three tables. Then I eliminate a bunch of unneeded fields. But, I don't see the option to pivot using this connection.

                       

                      Is there something I'm missing that would make a pivot possible using a method above

                       

                      SORRY !!! ....  I didn't realise that you can't pivot on a SQL Server connection

                      • 8. Re: Custom SQL to Pivot... Syntax Help, Please
                        Chris McClellan
                        Chris McClellan Ambassador Jan 10, 2018 12:36 PM (in response to Chrissy Scott)

                        The UNION ALL is probably going to double the data.  I think an extra condition or two would be needed on both statements.

                         

                        What are the "rules" does every record have a start date, but the end date is optional ?

                        • 9. Re: Custom SQL to Pivot... Syntax Help, Please
                          Chrissy Scott
                          Chrissy Scott Jan 10, 2018 1:34 PM (in response to Chris McClellan)

                          DARN! I was really hoping I had missed something glaringly easy! Haha

                           

                          Yes, pivoting the data makes it a great deal 'larger,' but I don't think I have any other options.

                           

                          The rules: Every record eventually gets an end date. It might be that the end date is null today and then gets filled in so it's there tomorrow after the morning refresh. Make sense?

                           

                          The new problem I'm having is that I actually need to pivot the data off of two calculated fields! So the madness continues.

                           

                          Any ideas?

                          • 10. Re: Custom SQL to Pivot... Syntax Help, Please
                            Gerardo Varela
                            Gerardo Varela Jan 10, 2018 1:52 PM (in response to Chrissy Scott) 
                            SELECT 
[metric_definition].[sys_id] AS [sys_id],
[metric_definition].[table] AS [table],
[metric_instance].[definition] AS [definition],
'Start Date' AS [date_type],
[metric_instance].[start] AS [date_value],
[metric_instance].[start] AS [start],
[metric_instance].[end] AS [end],
[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])
UNION ALL
SELECT 
[metric_definition].[sys_id] AS [sys_id],
NULL AS [table],
[metric_instance].[definition] AS [definition],
'End Date' AS [date_type],
[metric_instance].[end] AS [date_value],
[metric_instance].[start] AS [start],
[metric_instance].[end] AS [end],
NULL AS [calculation_complete],
NULL AS [value],
[metric_instance].[id] AS [id],
NULL AS [dv_definition],
NULL AS [number (incident)],
NULL AS [opened_at],
[incident].[sys_id] AS [sys_id (incident)],
NULL AS [dv_u_cause_code],
NULL 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])

                            That should work without duplicating your values. I think (minus any typos on my part).

                             

                            Regards,

                            Gerardo

                            • 11. Re: Custom SQL to Pivot... Syntax Help, Please
                              Chrissy Scott
                              Chrissy Scott Jan 11, 2018 9:34 AM (in response to Gerardo Varela)

                              Hi Gerardo,

                               

                              I gave this a go but the resulting viz was drastically different than my original one. I will play with it some more, tho. Thanks for your help.

                               

                              Chrissy

                              • 12. Re: Custom SQL to Pivot... Syntax Help, Please
                                Chrissy Scott
                                Chrissy Scott Jan 11, 2018 9:35 AM (in response to Hari Ankem)

                                Thanks again, Hari. This did the trick!