SQL query to change column names to single column value

Question

I'm using SQL Server. Not sure what version, but it's still supported. I have a table that has 3 columns that I'm interested in. I need to change the column names in a 'hardware' column with the counts for each. Right now I have the following:

SELECT 
    COUNT(a.EAMacBook13) AS 'MacBook13', 
    COUNT(a.EAMacBook16) AS 'MacBook16', 
    COUNT(a.EAStandLaptop) AS 'StandLaptop' 
FROM 
    Employee AS e, EmpAttributes AS a 
WHERE 
    a.EAEmpID = e.EmpID AND e.EmpProjID = 1

where instead I need something like:

Is this possible?

Answer 1

You can do it with a simple union all:

SELECT 'MacBook13' "Hardware", Count(a.EAMacBook13) "Count"
FROM Employee as e, EmpAttributes as a WHERE a.EAEmpID = e.EmpID and e.EmpProjID = 1
union all 
SELECT 'MacBook16' "Hardware", Count(a.EAMacBook16)  "Count"
FROM Employee as e, EmpAttributes as a WHERE a.EAEmpID = e.EmpID and e.EmpProjID = 1
union all 
SELECT 'StandLaptop' "Hardware", Count(a.EAStandLaptop)  "Count"
FROM Employee as e, EmpAttributes as a WHERE a.EAEmpID = e.EmpID and e.EmpProjID = 1

Depending on your RDBMS, you might have access to UNPIVOT which would make this less writing.

You could also use a CTE so that (if your RDBMS supports materializing them) you do less work:

with cte as (
  SELECT Count(a.EAMacBook13) as EAMacBook13, Count(a.EAMacBook16) as EAMacBook16, Count(a.EAStandLaptop) as EAStandLaptop
  FROM Employee as e, EmpAttributes as a WHERE a.EAEmpID = e.EmpID and e.EmpProjID = 1
)
SELECT 'MacBook13' "Hardware", EAMacBook13 "Count"
FROM  cte
union all 
SELECT 'MacBook16' "Hardware", MacBook16 "Count"
FROM cte
union all 
SELECT 'StandLaptop' "Hardware", StandLaptop "Count"
FROM cte

You will have to list out the possible Hardware values unless you use some degree of dynamic SQL.

Answer 2

The base idea is to unpivot and count. If your database supports lateral joins (aka cross apply) and values() you can do:

select x.hardware, count(x.val) cnt
from employee as e
inner join empattributes as ea on ea.eaempid = e.empid 
cross join lateral (values 
    ('EAMacBook13', ea.EAMacBook13), 
    ('EAMacBook16', ea.EAMacBook16), 
    ('EAStandLaptop', ea.EAStandLaptop)
) x(hardware, val)
where e.empprojid = 1
group by x.hardware

There are different syntaxes available for lateral joins, depending on your database. Some databases use cross apply instead of cross join lateral.

values() is also not supported everywhere. In Oracle, for example, you would phrase the lateral join as:

cross apply (
    select 'EAMacBook13' as hardware, EAMacBook13 as val from dual
    union all select 'EAMacBook16', EAMacBook16 from dual
    union all select 'EAStandLaptop', EAStandLaptop from dual
) x(hardware)

If none of that is available, then we can fallback on union all:

select x.hardware, count(x.val) cnt
from employee as e
inner join (
    select eaempid, 'EAMacBook13' as hardware, EAMacBook13 as value from empattributes 
    union all select eaempid, 'EAMacBook16', EAMacBook16 from empattributes 
    union all select eaempid, 'EAStandLaptop', EAStandLaptop from empattributes 
) x on x.eaempid = e.empid 
where e.empprojid = 1
group by x.hardware

Answer 3

One option would be using Dynamic Unpivot. First create a table with the current query :

SELECT a.EAMacBook13, a.EAMacBook16, a.EAStandLaptop
  INTO tab
  FROM Employee AS e 
  JOIN EmpAttributes AS a 
    ON a.EAEmpID = e.EmpID
 WHERE e.EmpProjID = 1

and then use information_schema.columns :

DECLARE @cols  AS NVARCHAR(MAX),  @query AS NVARCHAR(MAX)

SET  @query = 
    (SELECT STRING_AGG(CONCAT('SELECT ''',SUBSTRING(column_name,3,LEN(column_name)),''' AS Hardware, 
            COUNT(',column_name,') AS Count FROM tab '),' UNION ALL ')
       FROM information_schema.columns
      WHERE table_name = 'tab');

EXEC sp_executesql @query;

Demo