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

    Custom SQL to Pivot... Syntax Help, Please

    Chrissy Scott

      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

        • 1. Re: Custom SQL to Pivot... Syntax Help, Please
          Hari Ankem

          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

            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

              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

                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

                  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

                    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

                      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

                        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

                          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
                            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

                              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

                                Thanks again, Hari. This did the trick!