logo
down
shadow

How to create Pivot table using C#?


How to create Pivot table using C#?

By : user3855827
Date : January 02 2021, 06:48 AM
may help you . I suggest leaning a bit of SQL, a bit of C# and a bit about pivot table. Then write some code. Preferably, make it compile.
Once you know all these things, you'll probably be able to ask a more precise question, such as "Using the Xxx Pivot control, I try to read data from SQL server using this SQL query and try to populate the data, but I encounter this specific issue..."
code :


Share : facebook icon twitter icon
Create a calculated field using values to create a slicer in excel pivot table

Create a calculated field using values to create a slicer in excel pivot table


By : Moki
Date : March 29 2020, 07:55 AM
Hope this helps I have a data set where one column (job type) has 4 values in it (A,B,C,D). I am using that column as a slicer in a pivot table. However I would like to modify this slicer to only have two options (X,Y) where X=A+B and Y=C+D. I know I could create a new column in the raw data but I am trying to avoid this as the raw data source is already highly manipulated through several dozen high complexity mods. , Apologies if this isn't clear!
This is tested on Excel 2010.
Create Pivot table in SQL without Pivot Function

Create Pivot table in SQL without Pivot Function


By : user2449071
Date : March 29 2020, 07:55 AM
I hope this helps . You can use CASE statements with a GROUP BY to do the pivot "manually";
code :
SELECT "SalesDate", MAX("Country") "Country",
  MAX(CASE WHEN "SubChannel"='Search' 
           THEN "NetQuantity" END) "Search",
  MAX(CASE WHEN "SubChannel"='Unknown' 
           THEN "NetQuantity" END) "Unknown",
  MAX(CASE WHEN "SubChannel"='Web Property' 
           THEN "NetQuantity" END) "WebProperty",
  MAX(CASE WHEN "SubChannel"='TV Attributable' 
           THEN "NetQuantity" END) "TVAttributable",
  MAX(CASE WHEN "SubChannel"='Overall' 
           THEN "NetQuantity" END) "Overall"
FROM temp
GROUP BY "SalesDate";
Should I create another pivot table or just add another field on current pivot table that will be NULL for most records?

Should I create another pivot table or just add another field on current pivot table that will be NULL for most records?


By : Andrey
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I think you will be better off with a separate Character table, but belonging to Role and not a join table. Roles that are Actors would have this association, but the others would not.
This will allow you to be more flexible in the future and avoid null fields that will never be filled. Read here for more opinions on null: https://dba.stackexchange.com/q/5222
SQL create a pivot table without using the Oracle Pivot clause

SQL create a pivot table without using the Oracle Pivot clause


By : Solaris765
Date : March 29 2020, 07:55 AM
I wish this help you I'm trying to create a pivot table, but without using the oracle pivot clause. , Use a pivot query and aggregate only over the first names:
code :
SELECT
    first_name AS driver,
    SUM(CASE WHEN v.name = 'Big Dog'    THEN 1 ELSE 0 END) AS "Big Dog",
    SUM(CASE WHEN v.name = 'Classy Cat' THEN 1 ELSE 0 END) AS "Classy Cat",
    SUM(CASE WHEN v.name = 'Mad Max'    THEN 1 ELSE 0 END) AS "Max"
FROM employee e JOIN haul h
    ON e.employee_id = h.employee_id
JOIN vehicle v
    ON v.vehicle_id = h.vehicle_id
GROUP BY first_name
ORDER BY first_name
Create Pivot query in SQL Server like Excel pivot table

Create Pivot query in SQL Server like Excel pivot table


By : Nefeli Hal
Date : March 29 2020, 07:55 AM
like below fixes the issue I am trying to convert an Excel pivot to a SQL query so instead of extracting the data first from my SQL Server database then create the pivot table manually in Excel, I want to create the pivot query directly in SQL. , You can try to use CASE WHEN and SUM function to make it.
code :
SELECT Brand,
       SUM(CASE WHEN Location = 'Austria' THEN qty END) 'Austria_qty',
       SUM(CASE WHEN Location = 'Austria' THEN Price  END) 'Austria_totle',
       SUM(CASE WHEN Location = 'France' THEN qty  END) 'France_qty',
       SUM(CASE WHEN Location = 'France' THEN Price END)'France_totle',
       SUM(CASE WHEN Location = 'Germany' THEN qty END) 'Germany_qty',
       SUM(CASE WHEN Location = 'Germany' THEN Price END)'Germany_totle',
       SUM(CASE WHEN Location = 'Italy' THEN qty END) 'Italy_qty',
       SUM(CASE WHEN Location = 'Italy' THEN Price END) 'Italy_totle'
FROM T
GROUP BY Brand
|   Brand | Austria_qty |      Austria_totle | France_qty | France_totle | Germany_qty | Germany_totle | Italy_qty | Italy_totle |
|---------|-------------|--------------------|------------|--------------|-------------|---------------|-----------|-------------|
|   Apple |           1 |            1351.16 |          1 |         9.96 |           2 |       1583.85 |         1 |     1053.83 |
|  Huawei |           1 |             744.67 |     (null) |       (null) |           2 |     207704.86 |    (null) |      (null) |
|  Lenovo |           2 |            1184.21 |          2 |      1420.43 |           2 |       3454.91 |    (null) |      (null) |
|   Nokia |      (null) |             (null) |          1 |       796.03 |      (null) |        (null) |         1 |      538.41 |
| Samsung |      (null) |             (null) |          1 |      3327.14 |      (null) |        (null) |         1 |        9.09 |
;WITH CTE(Brand,Austria_qty,Austria_totle,France_qty,France_totle,Germany_qty,Germany_totle,Italy_qty,Italy_totle) 
AS (
  SELECT Brand,
         SUM(CASE WHEN Location = 'Austria' THEN qty END) 'Austria_qty',
         SUM(CASE WHEN Location = 'Austria' THEN Price  END) 'Austria_totle',
         SUM(CASE WHEN Location = 'France' THEN qty  END) 'France_qty',
         SUM(CASE WHEN Location = 'France' THEN Price END)'France_totle',
         SUM(CASE WHEN Location = 'Germany' THEN qty END) 'Germany_qty',
         SUM(CASE WHEN Location = 'Germany' THEN Price END)'Germany_totle',
         SUM(CASE WHEN Location = 'Italy' THEN qty END) 'Italy_qty',
         SUM(CASE WHEN Location = 'Italy' THEN Price END) 'Italy_totle'
  FROM T
  GROUP BY Brand
)

SELECT Brand,
    Austria_qty,
    Austria_totle,
    France_qty,
    France_totle,
    Germany_qty,
    Germany_totle,
    Italy_qty,
    Italy_totle 
FROM CTE
UNION ALL 
SELECT 'Totle',
    SUM(Austria_qty),
    SUM(Austria_totle),
    SUM(France_qty),
    SUM(France_totle),
    SUM(Germany_qty),
    SUM(Germany_totle),
    SUM(Italy_qty),
    SUM(Italy_totle) 
FROM CTE
SELECT tt.brand,
       SUM(tt.austria_qty) 'austria_qty',
       SUM(tt.austria_totle) 'austria_totle',
       SUM(tt.france_qty) 'austria_qty',
       SUM(tt.france_totle) 'austria_totle',
       SUM(tt.germany_qty) 'austria_qty',
       SUM(tt.germany_totle) 'austria_totle',
       SUM(tt.italy_qty) 'austria_qty',
       SUM(tt.italy_totle) 'austria_totle'
FROM T CROSS APPLY (
  VALUES (Brand
          ,(CASE WHEN [Location] = 'Austria' THEN [qty] END) 
          ,(CASE WHEN [Location] = 'Austria' THEN [Price] END) 
          ,(CASE WHEN [Location] = 'France' THEN [qty] END) 
          ,(CASE WHEN [Location] = 'France' THEN [Price] END) 
          ,(CASE WHEN [Location] = 'Germany' THEN [qty] END) 
          ,(CASE WHEN [Location] = 'Germany' THEN [Price] END) 
          ,(CASE WHEN [Location] = 'Italy' THEN [qty] END)
          ,(CASE WHEN [Location] = 'Italy' THEN [Price] END) 
         ),
         (
         'Totle'
          ,(CASE WHEN [Location] = 'Austria' THEN [qty] END) 
          ,(CASE WHEN [Location] = 'Austria' THEN [Price] END) 
          ,(CASE WHEN [Location] = 'France' THEN [qty] END) 
          ,(CASE WHEN [Location] = 'France' THEN [Price] END) 
          ,(CASE WHEN [Location] = 'Germany' THEN [qty] END) 
          ,(CASE WHEN [Location] = 'Germany' THEN [Price] END) 
          ,(CASE WHEN [Location] = 'Italy' THEN [qty] END)
          ,(CASE WHEN [Location] = 'Italy' THEN [Price] END) 
         )
) tt(brand, 
     austria_qty, 
     austria_totle, 
     france_qty,  
     france_totle, 
     germany_qty, 
     germany_totle, 
     italy_qty, 
     italy_totle)
GROUP BY tt.brand
|   Brand | Austria_qty |      Austria_totle | France_qty |       France_totle | Germany_qty | Germany_totle | Italy_qty |        Italy_totle |
|---------|-------------|--------------------|------------|--------------------|-------------|---------------|-----------|--------------------|
|   Apple |           1 |            1351.16 |          1 |               9.96 |           2 |       1583.85 |         1 |            1053.83 |
|  Huawei |           1 |             744.67 |     (null) |             (null) |           2 |     207704.86 |    (null) |             (null) |
|  Lenovo |           2 | 1184.2099999999998 |          2 |            1420.43 |           2 |       3454.91 |    (null) |             (null) |
|   Nokia |      (null) |             (null) |          1 |             796.03 |      (null) |        (null) |         1 |             538.41 |
| Samsung |      (null) |             (null) |          1 |            3327.14 |      (null) |        (null) |         1 |               9.09 |
|   Totle |           4 |            3280.04 |          5 | 5553.5599999999995 |           6 |     212743.62 |         3 | 1601.3299999999997 |
Related Posts Related Posts :
  • Looking for a .Net ORM
  • Cost of SortedDictionary.Count
  • All possible combinations of n items selected randomly from a set of x items (algorithm)
  • Displaying process percentage on DOS screen
  • C# Importing Large Volume of Data from CSV to Database
  • c# control location precision
  • Printing to Zebra printer gives faint and blurry images
  • How to avoid stepping into dll code file?
  • Programmaticaly prevent Vista desktop search (WDS) from indexing pst files placed on mapped network drives
  • Is there anyway to reduce IsolatedStorage capacity in Silverlight?
  • C#: How to print a unicode string to console?
  • Frequency Table with Random Values
  • My Visual Studio 2008 web application keeps throwing a .Net error when I first run it, but refreshing fixes it
  • TCP Connection Creation and Closing Event Hooking
  • Find changed properties of a class
  • C#: Object with custom marshaller not containing data after PInvoke call
  • Version Control System with API. Need to get metrics
  • Performance comparison of Dictionaries
  • How to get process name and title of the top window on Windows / C#
  • How to save the ListView contents to a text file?
  • Why not .NET-style delegates rather than closures in Java?
  • Get CLSID by PIA interface Type
  • Visual Studio 2008 C# - Recover solution from application manifest possible?
  • Is a good practice to initialize private data members from within ctor by calling the associated properties instead of t
  • Inserting into a bitstream
  • How do you write byte[] array using log4.net
  • Clickonce + .NET client profile 4 framework + offline
  • Show form that showDialog from mainForm won't block it, but closing mainForm will close it
  • Developing a GPS car tracking system
  • dynamic log4net appender name?
  • Visual studio formatting issue
  • IndexOutOfRangeException when a stream is a multiple of the buffer size
  • How to deal with calculated values with Dependency Properties on a custom WPF control
  • counting from textbox,asp.net
  • Threading best practice when using SFTP in C#
  • how to load PDF file in website page using C#
  • Parent child class relationship design pattern
  • MessageQueue.BeginReceive() null ref error - c#
  • A C# collection, which behaves like C++ set or priority_queue?
  • Animating WPF buttons from inside a style
  • WPF Grid : GridSplitter event
  • Server 2008 Task Scheduler Mapped Drive Access C#
  • Cast problem with LINQ
  • Combo box Item source = ObservableCollection & I need a '-None-' dummy entry at the top
  • Which GUI control is suitable for this purpose?
  • Trying to access App.config file for mail settings but fails to work
  • Invoke PowerShell script from c# in runspace and get line number where error occured
  • How to show html contents with a RichTextBox?
  • FTP monitoring and downloading of new files
  • MEF + Plug-In not updating
  • Efficient handling of super wide, but not so tall, bitmap?
  • .NET 3.0 Unit Testing getting System.MethodAccessException calling .NET 2.0
  • Web Services vs Persistent Sockets
  • How do I dynamically reference incremented properties in C#?
  • Windows Installer (C#) error code 2869
  • Exception "The operation is not valid for the state of the transaction" using TransactionScope
  • Tools for listing database accesses from .NET code
  • Excel VSTO set selection
  • Creating a subscription based website in ASP.NET
  • Merging Resources Dictionaries
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk