CODEWITHSUNDEEP
sqlsql-serverselectunpivot
JSheldrake
JSheldrake

Reputation: 9

Transpose table including headers

I have a query I want transposed including headers. Cutdown example below:

SELECT 'Records' AS [Entity], COUNT([ContactID]) AS [ContactID],
        COUNT([FirstName]) AS [FirstName],
        COUNT([LastName]) AS [LastName],COUNT([Title]) AS [Title] 
FROM [all].[Account]

Here is an example I created in Excel to demonstrate the required outcome:

Example

Upvotes: 0

Views: 90

Answers (2)

Danny Briskin
Danny Briskin

Reputation: 144

In general (ANSI SQL) the task is not trivial. It depends on the number of columns and values in the initial table/query. Previous response with UNION ALL solves your task pretty good.

But as long as you have SQL server RDBMS, there is a PIVOT/UNVPIVOT to transpose almost every table/query:

-- suppose you have a table 
CREATE TABLE Account ([ContactID] varchar(10), [FirstName] varchar(10)
, [LastName] varchar(10),[Title] varchar(10));

INSERT INTO Account VALUES 
('Aaa', 'Bbb', 'Ccc','Ddd'), ('Eee', 'Fff', 'Ggg','Hhh')
, ('Qqq', 'Www', 'Rrr','Ttt'),  ('Zzz', 'Xxx', 'Vvv','Nnn')
, ('Mmm', 'Sss', 'Uuu','Ooo');

The result will be

SELECT * FROM 
( SELECT 'Records' AS [EntityN], columnName as [Entity], columnValue
FROM [Account]
UNPIVOT
(
  columnValue
  FOR columnName IN (ContactID,FirstName, LastName, Title)
) unpivotQuery
) q
PIVOT
(
 COUNT(columnValue)
 FOR EntityN in (Records)
) pivotQuery;

Check SQL Fiddle to play around

Upvotes: 0

GMB
GMB

Reputation: 222432

If your database supports lateral joins and the values() row constructor, you can unpivot and aggregate as follows:

select x.entity, count(x.val) records
from account a
cross apply (values ('contactid', a.contactid), ('firstname', a.firstname), ('last_name', a.lastname)) as x(entity, val)
group by x.entity

Some databases use cross join lateral instead of cross apply.

A more portable approach (although less efficient) is union all:

select 'contactid' entity, count(contactid) from account
union all
select 'firstname', count(firstname) from account 
union all
select 'lastname', count(lastname) from account

Upvotes: 2

Related Questions