[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

Transpose column to row SQL 2005

Current::
SELECT DISTINCT product_type, COUNT(*) AS Count, severity_code
FROM         dbo.probsummarym1
WHERE     (dept = 'it- operations') AND (product_type= 'other / to be determined')
GROUP BY product_type, severity_code
ORDER BY COUNT(*) DESC, product_type
OUTPUT:
Product Type                     Count         Severity_code
other / to be determined      310      5
other / to be determined      97        4
other / to be determined      26        3
other / to be determined      2         1

DESIRED OUTPUT IN Reporting Services table.
Product Type                        TotalCount         SC1     SC2      SC3      SC4        SC5
other / to be determined           435                  2          0          26         97          310

In reality I have 100 Product Types in the table I will need to perform this on. This is just one example.

How can I transpose a column to row as in the above example
0
rhservan
Asked:
rhservan
  • 2
1 Solution
 
JimBrandleyCommented:
Try this one.
SELECT a.product_type, (SELECT COUNT(*) FROM dbo.probsummarym1 b WHERE a.product_type = b.product_type) TotalCount,
       (SELECT COUNT(*) FROM dbo.probsummarym1 b WHERE a.product_type = b.product_type AND severity_code = 1) SC1,
       (SELECT COUNT(*) FROM dbo.probsummarym1 b WHERE a.product_type = b.product_type AND severity_code = 2) SC2,
       (SELECT COUNT(*) FROM dbo.probsummarym1 b WHERE a.product_type = b.product_type AND severity_code = 3) SC3,
       (SELECT COUNT(*) FROM dbo.probsummarym1 b WHERE a.product_type = b.product_type AND severity_code = 4) SC4,
       (SELECT COUNT(*) FROM dbo.probsummarym1 b WHERE a.product_type = b.product_type AND severity_code = 5) SC5
  FROM dbo.probsummarym1 a
 WHERE dept = 'it- operations'
ORDER BY a.product_type
0
 
kenhaleyCommented:
Another suggestion:  Use PIVOT (see books on-line topic "Using PIVOT and UNPIVOT").
0
 
rhservanAuthor Commented:
Excellent work!!

Ran perfectly the first time.
0
 
JimBrandleyCommented:
Thank you.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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