3 Replies Latest reply on Jun 29, 2016 4:30 AM by John Sobczak

    columns pivot from column to rows using DB

    Pradeep bukka

      Hi,

      How I can pivot columns data to Rows data using Calculations in Tableau. 

      if I am using Excel I can but with DB I can't . If any one knows plz help mee...

        • 1. Re: columns pivot from column to rows using DB
          Ashish Chaudhari

          Hi Pradeep,

           

          You can pivot data in tableau by doing following. Right click on the data source and select edit data source. Then follow the below steps.

           

          Input:

           

          Output:

           

          Let me know if this helps.

           

          -Ashish Chaudhari

          1 of 1 people found this helpful
          • 2. Re: columns pivot from column to rows using DB
            Pradeep bukka

            Hi Ashish,

             

            Thanks for replay

            I am connecting DB. I am not able to get pivot option.

             

            If I am connecting to excel what ever u showed. I also got same solution.

             

            --Pradeep Bukka

            • 3. Re: columns pivot from column to rows using DB
              John Sobczak

              If you convert your connection to Custom SQL and then modify it slightly you can achieve the same result you get in Pivot.  For example to pivot the sales and profit values in the sample superstore file, I converted to Custom SQL the connection to the sample superstore data and then modified by adding the red statements while also duplicating the entire select clause for each column you wish to pivot and combining them with a UNION ALL.  Be very careful though with the syntax as every comma, period, bracket, quote, etc needs to be exact.

               

              If you use the Custom SQL option you should run it as an extract rather than live connection as it will be slower on live connection.

               

              SELECT
              [Orders$].[Category] AS [Category],

                [Orders$].[City] AS [City],

                [Orders$].[Country] AS [Country],

                [Orders$].[Customer ID] AS [Customer ID],

                [Orders$].[Customer Name] AS [Customer Name],

                [Orders$].[Discount] AS [Discount],

                [Orders$].[Order Date] AS [Order Date],

                [Orders$].[Order ID] AS [Order ID],

                [Orders$].[Postal Code] AS [Postal Code],

                [Orders$].[Product ID] AS [Product ID],

                [Orders$].[Product Name] AS [Product Name],

                [Orders$].[Profit] AS [Profit],

                [Orders$].[Quantity] AS [Quantity],

                [Orders$].[Region] AS [Region],

                [Orders$].[Row ID] AS [Row ID],

                [Orders$].[Sales] AS [Sales],

                [Orders$].[Segment] AS [Segment],

                [Orders$].[Ship Date] AS [Ship Date],

                [Orders$].[Ship Mode] AS [Ship Mode],

                [Orders$].[State] AS [State],

                [Orders$].[Sub-Category] AS [Sub-Category],

               

              [Orders$].[Profit] AS [Amount],

              'Profit' AS [Type]

               

              FROM
              [Orders$]

               

              UNION ALL

               

              SELECT
              [Orders$].[Category] AS [Category],

                [Orders$].[City] AS [City],

                [Orders$].[Country] AS [Country],

                [Orders$].[Customer ID] AS [Customer ID],

                [Orders$].[Customer Name] AS [Customer Name],

                [Orders$].[Discount] AS [Discount],

                [Orders$].[Order Date] AS [Order Date],

                [Orders$].[Order ID] AS [Order ID],

                [Orders$].[Postal Code] AS [Postal Code],

                [Orders$].[Product ID] AS [Product ID],

                [Orders$].[Product Name] AS [Product Name],

                [Orders$].[Profit] AS [Profit],

                [Orders$].[Quantity] AS [Quantity],

                [Orders$].[Region] AS [Region],

                [Orders$].[Row ID] AS [Row ID],

                [Orders$].[Sales] AS [Sales],

                [Orders$].[Segment] AS [Segment],

                [Orders$].[Ship Date] AS [Ship Date],

                [Orders$].[Ship Mode] AS [Ship Mode],

                [Orders$].[State] AS [State],

                [Orders$].[Sub-Category] AS [Sub-Category],

               

              [Orders$].[SALES] AS [Amount],

              'Sales' AS [Type]

               

              FROM
              [Orders$]

              1 of 1 people found this helpful