Pivot/aggregation with percentage columns possible in t-sql?

Struggling with this a bit in SQL Server, any ideas? Given this data (ID is an INT, the rest are BITs):

------------------------------------------
ID   Type1   Type2   AttrA   AttrB   AttrC
------------------------------------------
1    1       0       1       0       0
2    1       0       1       0       0
3    1       1       0       1       1
4    0       1       1       1       0
5    1       1       1       1       0

I would like to produce this report:

---------------------------------------------------------
Attr    NumOfType1   PctOfType1   NumOfType2   PctOfType2
---------------------------------------------------------
AttrA   3            75%          2            67%
AttrB   2            50%          3            100%
AttrC   1            25%          1            33%
Total   4            N/A          3            N/A

Thanks!

Jim

Answers


;WITH YourBaseTable AS
(
SELECT 1 ID, 1 Type1,0 Type2, 1 AttrA, 0 AttrB, 0 AttrC UNION ALL
SELECT 2 ID, 1 Type1,0 Type2, 1 AttrA, 0 AttrB, 0 AttrC  UNION ALL
SELECT 3,1,1,0,1,1 UNION ALL
SELECT 4,0,1,1,1,0 UNION ALL
SELECT 5,1,1,1,1,0 
), T AS
(
SELECT *, 1 AS Total
FROM YourBaseTable
)
SELECT 
Attr,
 COUNT(CASE WHEN VALUE = 1 AND Type1 = 1 THEN 1 END) NumOfType1, 
 CASE WHEN Attr <> 'Total' THEN 100 * CAST(COUNT(CASE WHEN VALUE = 1 AND Type1 = 1 THEN 1 END) AS FLOAT)/COUNT(CASE WHEN Type1 = 1 THEN 1 END) END PctOfType1, 
 COUNT(CASE WHEN VALUE = 1 AND Type2 = 1 THEN 1 END) NumOfType2,
 CASE WHEN Attr <> 'Total' THEN 100 * CAST(COUNT(CASE WHEN VALUE = 1 AND Type2 = 1 THEN 1 END) AS FLOAT)/COUNT(CASE WHEN Type2 = 1 THEN 1 END) END PctOfType2
FROM T
UNPIVOT
(VALUE FOR Attr IN (AttrA, AttrB,AttrC, Total)) AS unpvt
GROUP BY Attr

Need Your Help

Finding the index of a string in a tuple

python tuples

Tup = ('string1','string2','string3')

How do I get a multipage site from GitHub Pages' automatic generator?

github markdown documentation-generation github-pages

I am using the 'Automatic Generator' of GitHub Pages, and I see it only generates a single index.html and other web resources.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.