Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

How to get percentage in Pivot SQL

Dear Experts,

How can I pivot this raw data with a percentage result?
Raw data:

29852.6              A
6532.2              B
58955.96      C

Result:
A                 B           C                      D
29852.6      6532.2      58955.96      0
31.31%      6.85%      61.84%      0.00%


Thanks.
0
JimiJ13
Asked:
JimiJ13
  • 4
  • 3
1 Solution
 
PortletPaulCommented:
where did the column D come from?

please if supplying sample data and expected results it is necessary to make the data consistent with reality.

It will save you time too if you provide real table names and column names. e.g.
SomeTable
SomeNumber SomeCode
29852.6       A
6532.2        B
58955.96      C

Open in new window

0
 
PortletPaulCommented:
sorry, more...

the good news, it can probably be done - with tedium.

the bad news, you cannot mix "data types" in a single column

the "%" symbol in the second row makes that row text, not numbers. hence if that row is text, then all columns must also be text. This makes it hugely more tedious to produce the output.

Is this a report (e.g. Crystal Reports)? or a screen (e.g. html)?
If it is anything like these then those tools should be used - NOT SQL!

SQL is NOT a "report writer"

-----
OR

make it easier:
a: more columns, not 2 rows
b: don't include the "%" symbol
0
 
PortletPaulCommented:
| LABEL |     A |    B |     C | D |
|-------|-------|------|-------|---|
|   Sum | 29853 | 6532 | 58956 | 0 |
|     % |  31.3 |  6.9 |  61.8 | 0 |

Open in new window

It ain't pretty:
SELECT
      'Sum' AS Label
    , [A]
    , [B]
    , [C]
    , [D]
FROM (
            SELECT
                  SomeCode
                , SomeNumber * 1.0 as SomeNumber
            FROM SomeTable
      ) AS SourceTable
      PIVOT
      (
      SUM(SomeNumber)
      FOR SomeCode IN ([A], [B], [C], [D])
      ) AS PivotTable

UNION ALL

SELECT
      '%' AS Label
    , [A]
    , [B]
    , [C]
    , [D]
FROM (
            SELECT
                  SomeCode
                , SomeNumber * 100.0
                / sum(SomeNumber) over() as pct
            FROM SomeTable
      ) AS SourceTable
      PIVOT
      (
      MAX(pct)
      FOR SomeCode IN ([A], [B], [C], [D])
      ) AS PivotTable
;



CREATE TABLE SomeTable
	([SomeNumber] numeric, [SomeCode] varchar(1))
;
	
INSERT INTO SomeTable
	([SomeNumber], [SomeCode])
VALUES
	(29852.6, 'A'),
	(6532.2, 'B'),
	(58955.96, 'C'),
    (0,'D')
;

http://sqlfiddle.com/#!3/199a8/9

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
JimiJ13I T ConsultantAuthor Commented:
The D column is an invisible column when empty.

Thanks for the suggestions. What  I did was to add a subtotal column in query and made a dynamic  %  computation on another column per item  in Telerik Reporting that almost double the page width.  

Sorry, I cannot give the actual table as part of  agreement with client.

Great help and input anyway!
0
 
PortletPaulCommented:
SQL is really only good for the "base data", after then ALL "presentation" should be performed in the reporting tool.
0
 
JimiJ13I T ConsultantAuthor Commented:
Yes, I know but there are requirements that you can only produce a tidy result by doing some dirty work in SQL.
Anyway what you have done is great, and I highly appreciated it. My client will be well pleased for it.  

Thanks.
0
 
JimiJ13I T ConsultantAuthor Commented:
Great!
Not so pretty but it delivers beautiful result in the report.

Thanks.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now