T-SQL: Rows to Columns With Count

Question

Let me draw up the table first (there are dozens of columns and dozens of values under Code in reality)

Code | Pat   | Col1 | Col2 | Col3
---------------------------------
ABC  | 001   |      | XX   | Q1
ABC  | 002   | xx   | xx   | Q1
ABC  | 003   | xx   | xxx  | Q1
DEF  | 004   | xx   | xx   | Q1
DEF  | 005   | xx   | xx   | Q1
DEF  | 006   | xx   | xxx  | Q1

The resulting table need to look like

ABC | DEF
---------
2   | 3
3   | 3

Let me try and explain. For each 'Code' column, I would need to count the number of entries in Col1 to ColX where the cell is not null/empty.

So in example above, Code ABC has a count of 2 in Col1 and a count of 3 in Col2 Similarly for DEF, both have a count of 3

I've tried lots of things but got to the point where I'm now looking at a blank page again!

ALTERNATIVELY

Code | Col1 | Col2
--------------------
ABC  | 2    | 3   
DEF  | 3    | 3

Please advise

There's 81 of them in total and it's likely to go up or down — pee2pee, May 21 '15 at 09:58
http://stackoverflow.com/questions/13415540/sql-server-pivot-table-with-counts-and-sums — artm, May 21 '15 at 11:53

flo · Answer 1 · 2015-05-21T10:15:03.120

0

The alternative solution can be reached by using GROUP BY and summing up a calculated number:

SELECT 
    [Code], 
    SUM(CASE WHEN ISNULL(Col1, '') = '' THEN  0 ELSE 1 END) as [Col1], 
    SUM(CASE WHEN ISNULL(Col2, '') = '' THEN  0 ELSE 1 END) as [Col2], 
    ...
FROM T 
GROUP by [Code]

edited May 21 '15 at 10:15

answered May 21 '15 at 10:10

flo

9,713
6
25
41

Sorry, forgot to tag that we are on 2008 R2 so this doesn't work – pee2pee May 21 '15 at 10:13
moved from `IIF` to `CASE` – flo May 21 '15 at 10:15

T-SQL: Rows to Columns With Count

1 Answers1