1 2 Previous Next 26 Replies Latest reply on Mar 31, 2017 4:36 AM by Mahfooj Khan

    Pivoting the data

    Sneha K

      Hello,

       

      I am trying to write the custom query to pivot the my data. The size of data set is 139540.

      For pivoting the columns I am using the query like :

      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]

      I am getting 2 issues in this:

      1) UNION is causing problem as my columns are having different number of records and data type

      2)The size of the data set is increasing like anything.

       

      And I want to pivot the 6 columns. When I pivoted 4 columns, the record set is increased to 2800k. 2 more columns  I need to pivot. My machine is having 4 Gb RAM and 64 bit.

       

       

      Please suggest how to go about this requirement, I am feeling like I am trapped by size constraints and query issue.

        • 1. Re: Pivoting the data
          Mahfooj Khan

          Hi Sneha,

           

          While doing UNION the important thing to remember with a UNION is that the column order in both SELECT statements must match. The column names don't have to match, but each table must contain the same number of columns and their data types must be compatible. If the data types don't match, the engine sometimes chooses the most compatible for you. The results might work, but then again, they might not.

          By default, UNION sorts records by the values in the first column because UNION uses an implicit DISTINCT predicate to omit duplicate records. To include all records, including duplicates, use UNION ALL, which eliminates the implicit sort. If you know there are no duplicate records, but there are a lot of records, you can use UNION ALL to improve performance because the engine will skip the comparison that's necessary to sort (to find duplicates).

           

          Hope this help.

           

          Mahfooj

          1 of 1 people found this helpful
          • 2. Re: Pivoting the data
            Sneha K

            oh ok. In my case I have pivoted for drug. for that I have created drug_name and drug_type. After that I have frequency column which is numeric, so here its not creating the column as I mentioned. it just putting the records under the column FREQUENCY1.

             

            PFA my custom sql:

            Select *,

            'drug1' as Drug_type,

            [Query_new$].drug1 as Drug_name

            from [Query_new$]

            union all

            Select *,

            'drug2' as Drug_type,

            [Query_new$].drug2 as Drug_name

            from [Query_new$]

            union all

            Select *,

            'drug3' as Drug_type,

            [Query_new$].drug3 as Drug_name

            from [Query_new$]

            union all

            Select *,

            'drug4' as Drug_type,

            [Query_new$].drug4 as Drug_name

            from [Query_new$]

            union all

            Select *,

            'drug5' as Drug_type,

            [Query_new$].drug5 as Drug_name

            from [Query_new$]

            union

            Select *,

            'FREQUENCY1' as DrugFrequency_type,

            [Query_new$].FREQUENCY1 as Drug_frequency

            from [Query_new$]

            union all

            Select *,

            'FREQUENCY2' as DrugFrequency_type,

            [Query_new$].FREQUENCY2 as Drug_frequency

            from [Query_new$]

            union all

            Select *,

            'FREQUENCY3' as DrugFrequency_type,

            [Query_new$].FREQUENCY3 as Drug_frequency

            from [Query_new$]

            union all

            Select *,

            'FREQUENCY4' as DrugFrequency_type,

            [Query_new$].FREQUENCY4 as Drug_frequency

            from [Query_new$]

            union all

            Select *,

            'FREQUENCY5' as DrugFrequency_type,

            [Query_new$].FREQUENCY5 as Drug_frequency

            from [Query_new$]

             

            I am not sure what to do.

            • 3. Re: Pivoting the data
              Mahfooj Khan

              As you've mentioned Frequency is a numeric field then it wont allow you to UNION. Because as per your column orders in few of your sql there is a string field and in that field you can not append a numeric field. So, I've modified your query.

              You can try this,

               

              Select *,

              'drug1' as Drug_type,

              [Query_new$].drug1 as Drug_name,
              NULL as Drug_frequency

              from [Query_new$]

              union all

              Select *,

              'drug2' as Drug_type,

              [Query_new$].drug2 as Drug_name,
              NULL as Drug_frequency

              from [Query_new$]

              union all

              Select *,

              'drug3' as Drug_type,

              [Query_new$].drug3 as Drug_name,
              NULL as Drug_frequency

              from [Query_new$]

              union all

              Select *,

              'drug4' as Drug_type,

              [Query_new$].drug4 as Drug_name,
              NULL as Drug_frequency

              from [Query_new$]

              union all

              Select *,

              'drug5' as Drug_type,

              [Query_new$].drug5 as Drug_name,
              NULL as Drug_frequency

              from [Query_new$]

              union

              Select *,

              'FREQUENCY1' as DrugFrequency_type,
              NULL as Drug_name,

              [Query_new$].FREQUENCY1 as Drug_frequency

              from [Query_new$]

              union all

              Select *,

              'FREQUENCY2' as DrugFrequency_type,
              NULL as Drug_name,

              [Query_new$].FREQUENCY2 as Drug_frequency

              from [Query_new$]

              union all

              Select *,

              'FREQUENCY3' as DrugFrequency_type,
              NULL as Drug_name,

              [Query_new$].FREQUENCY3 as Drug_frequency

              from [Query_new$]

              union all

              Select *,

              'FREQUENCY4' as DrugFrequency_type,
              NULL as Drug_name,

              [Query_new$].FREQUENCY4 as Drug_frequency

              from [Query_new$]

              union all

              Select *,

              'FREQUENCY5' as DrugFrequency_type,
              NULL as Drug_name,

              [Query_new$].FREQUENCY5 as Drug_frequency

              from [Query_new$]

               

              Let me know if this help.

               

              Mahfooj

              • 4. Re: Pivoting the data
                Sneha K

                So you mean to say I have to mention as NULL as Drug name in every other column pivoting?

                As I said, I have to pivot multiple columns like Area, Area Frequency, Plan, plan frequency.

                • 5. Re: Pivoting the data
                  Mahfooj Khan

                  As I've mentioned earlier. Column orders and their data type should be same. Lets say for example you've a table called "Temp". Table structure is something like this

                  Column     Data Type

                  ID     NUMBER()

                  NAME     VARCHAR2()

                  AREA     VARCHAR2()

                  AREA_FREQ     NUMBER()

                  PLAN     VARCHAR2()

                  PLAN_FREQ     NUMBER()

                   

                  Now If you wants to pivot using UNION, then your SQL query should be like this. Consider the Correct one

                   

                  Incorrect:

                  select ID, NAME, AREA, AREA_FREQ from Temp

                  UNION ALL

                  select ID, NAME, PLAN_FREQ, PLAN from Temp

                  Above will not work because columns are not in order and data type mismatch.

                   

                  Correct:

                  select ID, NAME, AREA, AREA_FREQ from Temp

                  UNION ALL

                  select ID, NAME, PLAN, PLAN_FREQ from Temp

                   

                  See the column orders. I've kept as per their data type. That thing you've to maintain in your case. Hope you get my point.

                   

                  Mahfooj

                  • 6. Re: Pivoting the data
                    Sneha K

                    My columns are having different data types. So getting confuse about the columns order and its data type.

                    See my data is like this:

                     

                        

                    PRACTITIONER_IDFIRST_NAMELAST_NAMESTATECITYSPECIALTY_DESCRIPTIONNPIdrug1FREQUENCY1drug2FREQUENCY2drug3FREQUENCY3drug4FREQUENCY4drug5FREQUENCY5Area1AreaFREQUENCY1Area2AreaFREQUENCY2Area3AreaFREQUENCY3Area4AreaFREQUENCY4Area5AreaFREQUENCY5plan1planFREQUENCY1plan2planFREQUENCY2plan3planFREQUENCY3plan4planFREQUENCY4plan5planFREQUENCY5
                    22207564AMOXICILLIN191HCSC BLUE MEDICARE RX PDP221
                    • 7. Re: Pivoting the data
                      Sneha K

                      Hey Mahfooj,

                      How about making my number columns as text?

                      As first 2 columns Drug_type and Drug_name, if I make other columns like Drug frequency and Area frequency to text in excel. Still its not working as required.

                      • 8. Re: Pivoting the data
                        Mahfooj Khan

                        Changing format as text in excel wont help. Because once you connect the data, tableau will automatically convert it in numeric. You can try this, prefix your numeric field in excel with single quote (') and check. e;g; '1236, '4567

                        Or you have to cast/convert your field to char in custom sql query like we do in sql.

                         

                        If you share your data then I can try at my end.

                         

                        Mahfooj

                        • 9. Re: Pivoting the data
                          Chris McClellan

                          Mahfooj Khan wrote:

                           

                           

                          While doing UNION the important thing to remember with a UNION is that the column order in both SELECT statements must match. The column names don't have to match, but each table must contain the same number of columns and their data types must be compatible. If the data types don't match, the engine sometimes chooses the most compatible for you. The results might work, but then again, they might not.

                          Tableau doesn't union like that though.  If you're unioning in the database these rules apply, but if you can do the union in Tableau it creates new fields when the column names don't match.

                           

                          Try it on your database though, I only tested on Excel.

                          • 10. Re: Pivoting the data
                            Mahfooj Khan

                            Thanks Chris Sir for your valuable input.

                            Yeah you're correct I've explained how UNION works in database only. Apology I should mention explain how it works in Tableau. Requester wants to know how to pivot using Custom SQL in tableau so i felt its more of db like concept. That's why I've wrote this. I've written a SQL statement which I've tested in tableau using Custom SQL and it works. Here is my findings. In Custom SQL, Tableau only checks ordering and data type of the attributes. Correct me If I'm wrong.

                             

                            And I agree more than 100% with you here

                            I guess you were trying to explain this scenario

                             

                            Mahfooj

                            1 of 1 people found this helpful
                            • 11. Re: Pivoting the data
                              Sneha K

                              Hello Chris and Mahfooj,

                              Thank you for your valuable inputs.

                              To make the column data types same, i have to convert the numeric columns to text, so I did that in union all query for pivoting. The query is as follows:

                              Select *,

                              'drug1' as Drug_type,

                              [Sheet1$].drug1 as Drug_name

                              from [Sheet1$]union all

                              Select *,

                              'drug2' as Drug_type,

                              [Sheet1$].drug2 as Drug_name

                              from [Sheet1$]union all

                              Select *,

                              'drug3' as Drug_type,

                              [Sheet1$].drug3 as Drug_name

                              from [Sheet1$]union all

                              Select *,

                              'drug4' as Drug_type,

                              [Sheet1$].drug4 as Drug_name

                              from [Sheet1$]union all

                              Select *,

                              'drug5' as Drug_type,

                              [Sheet1$].drug5 as Drug_name

                              from [Sheet1$]union

                              Select *,

                              'FREQUENCY1' as DrugFrequency_type,

                              STR[Sheet1$].FREQUENCY1 as Drug_frequency

                              from [Sheet1$]union all

                              Select *,

                              'FREQUENCY2' as DrugFrequency_type,

                              STR[Sheet1$].FREQUENCY2 as Drug_frequency

                              from [Sheet1$]union all

                              Select *,

                              'FREQUENCY3' as DrugFrequency_type,

                              STR[Sheet1$].FREQUENCY3 as Drug_frequency

                              from [Sheet1$]union all

                              Select *,

                              'FREQUENCY4' as DrugFrequency_type,

                              STR[Sheet1$].FREQUENCY4 as Drug_frequency

                              from [Sheet1$]union all

                              Select *,

                              'FREQUENCY5' as DrugFrequency_type,

                              STR[Sheet1$].FREQUENCY5 as Drug_frequency

                              from [Sheet1$]

                              but its giving error as numboer of columns mismatch.

                              As I guess, its creating new column for converted columns.

                               

                              So to resolve this I am trying to write a Custom SQL for converting the columns first and then use that custom SQLto do pivoting. But I didn't find any way to use one custom SQL into other.

                              Is there a way to do this?

                              • 12. Re: Pivoting the data
                                Mahfooj Khan

                                Hi Sneha,

                                 

                                Could you please send me your data in excel? I'll try to write Custom SQL query for you.

                                 

                                Mahfooj

                                • 13. Re: Pivoting the data
                                  Mahfooj Khan

                                  Meanwhile you can try this

                                  STR([Sheet1$].FREQUENCY1) AS Drug_Frequency

                                  • 14. Re: Pivoting the data
                                    Sneha K

                                    oh that was typo error ,  I tried the above solution. About data, I cannot send you the data. i have to mask data. I will try and let you know about it, but is that STR function creating a new column?

                                    1 2 Previous Next