IT Nota

  • Home
  • How To
  • .NET
  • WordPress
  • Contact
Home » How To » How to Show Field Values as Columns in SQL Server

How to Show Field Values as Columns in SQL Server

One of the most asked questions by users when they pull data using T-SQL is whether or not there’s a way to present the values of a field they’re interested in as columns instead. The answer is of course it’s “Yes.” You can achieve this by using PIVOT relational operator if you use MSSQL 2005 or later.

Without PIVOT

USE [MyDB];

--WITHOUT PIVOT
SELECT CostCenter, [Type], COUNT(AssetTag) as Total FROM tblInventory
WHERE [Type] = 'Desktop'
GROUP BY CostCenter, [Type]
UNION
SELECT CostCenter, [Type], COUNT(AssetTag) as Total FROM tblInventory
WHERE [Type] = 'Laptop'
GROUP BY CostCenter, [Type]
ORDER BY CostCenter;

With PIVOT

USE [MyDB];

--WITH PIVOT
SELECT CostCenter, Desktop, Laptop FROM (
SELECT CostCenter, [Type], COUNT(AssetTag) as Total FROM tblInventory
WHERE [Type] = 'Desktop'
GROUP BY CostCenter, [Type] 
UNION
SELECT CostCenter, [Type], COUNT(AssetTag) as Total FROM tblInventory
WHERE [Type] = 'Laptop'
GROUP BY CostCenter, [Type] 
) t1
PIVOT
(SUM(Total) FOR [Type] in ([Desktop], [Laptop])) t2
ORDER BY CostCenter;

Further Reading

Using PIVOT and UNPIVOT
Microsoft SQL Server 2014 Unleashed

September 22, 2015 Filed Under: How To Tagged With: SQL, SQL Server

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • .NET
  • Coding
  • Database
  • How To
  • Internet
  • Multimedia
  • Photography
  • Resources
  • Review
  • WordPress
  • Writing

Recent Posts

  • How to Use Custom Color in SSMS Using Redgate SQL Prompt
  • How to Install Python on Windows Server
  • How to Enable Secure HttpOnly Cookies in IIS
  • How to Create iCloud Mail Email Address
  • How to Import IIS Log to PostgreSQL
WP Engine Power your Brand with Managed WordPress

Recent Posts

  • How to Use Custom Color in SSMS Using Redgate SQL Prompt
  • How to Install Python on Windows Server
  • How to Enable Secure HttpOnly Cookies in IIS
  • How to Create iCloud Mail Email Address
  • How to Import IIS Log to PostgreSQL
  • RSS

Tags

.NET Core Access Adobe AdSense Amazon ASP.NET Cdonts Dll Classic ASP Code Editor Connect-It Copywriting ETL Genesis Framework Git Google HP Asset Manager HTML HTML5 Hugo IIS Information Security Internet Internet Information Services iOS Linux macOS Microsoft Microsoft SQL Server MVC Nikon Oracle PHP Simple Mail Transfer Protocol Smtp Server Social Media SQL SQL Server SSIS SSMS SSRS Windows Windows 8 Windows 10 Windows 2012 Windows Server

Copyright © 2011-2019 IT Nota. All rights reserved. Terms of Use | Privacy Policy | Disclosure