Transposing SQL Table Columns to Rows with Count on Each Category

Question

I have a table with 12,000 rows of data. The table is comprised of 7 columns of data (PIDA, NIDA, SIDA, IIPA, RPRP, IORS, DDSN) each column with 4 entry types ("Supported", "Not Supported", "Uncatalogued", or "NULL" entries)

+--------------+-----------+--------------+-----------+
|     PIDA     |   NIDA    |     SIDA     |   IIPA    |
+--------------+-----------+--------------+-----------+
| Null         | Supported | Null         | Null      |
| Uncatalogued | Supported | Null         | Null      |
| Supported    | Supported | Uncatalogued | Supported |
| Supported    | Null      | Uncatalogued | Null      |
+--------------+-----------+--------------+-----------+

I would like to generate an output where each entry is counted for each column. Like column to row transpose.

+---------------+------+------+------+------+
|  Categories   | PIDA | NIDA | SIDA | IIPA |
+---------------+------+------+------+------+
| Supported     |   10 |   20 |   50 |    1 |
| Non Supported |   30 |   50 |   22 |    5 |
| Uncatalogued  |    5 |   10 |   22 |   22 |
| NULL          |   10 |   11 |   22 |   22 |
+---------------+------+------+------+------+

Not having any luck with inline select or case statements. I have a feeling a little bit of both would be needed to first count and then list each as row in the output

Thanks all,

Sounds like you need a `PIVOT`. Does [THIS](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) give you a starting place? — WAMLeslie, Apr 30 '21 at 17:07

score 1 · Accepted Answer · answered Apr 30 '21 at 17:17

1

One option is to UNPIVOT your data and then PIVOT the results

Example

Select *
 From  (
        Select B.* 
         From  YourTable A
         Cross Apply ( values (PIDA,'PIDA',1)
                             ,(NIDA,'NIDA',1)
                             ,(SIDA,'SIDA',1)
                             ,(IIPA,'IIPA',1)
                     ) B(Categories,Item,Value)
       ) src
 Pivot ( sum(Value) for Item in ([PIDA],[NIDA],[SIDA],[IIPA] ) ) pvt

Results (with small sample size)

Categories      PIDA    NIDA    SIDA    IIPA
NULL            1       1       2       3
Supported       2       3       NULL    1
Uncatalogued    1       NULL    2       NULL

answered Apr 30 '21 at 17:17

John Cappelletti

79,615
7
44
66

Absolutely, and Positively Brilliant!!!!!!! This saved me a weekend of searching the web. Thanks - it worked perfectly against my funky table. – NanoNet Apr 30 '21 at 17:47
@NanoNet Always happy to help – John Cappelletti Apr 30 '21 at 17:57
@NanoNet If you found this answer helpful, feel free to ACCEPT it. It's a small token of appreciation and indicates to other contributors the problem has been resolved. – John Cappelletti Apr 30 '21 at 17:59

Transposing SQL Table Columns to Rows with Count on Each Category

1 Answers1