SQL Multiple Column Names to Single Column 'Type'

Question

So column names of a table I imported needs to be converted to a 'Type' column. I did it with UNION though I was reading that is not the proper way to do things RE: PIVOT and UNPIVOT and the table is much larger + different subject matter.

What is the best way to do this?

Current implementation:

SELECT DISTINCT MAKE, 'AUTOMOBILE' as TYPE,  AUTOMOBILE AS MODEL
FROM VEHICLE
UNION ALL
SELECT DISTINCT MAKE, 'MOTORCYCLE' as TYPE, MOTORCYLE AS MODEL
FROM VEHICLE

INPUT:

MAKE	AUTOMOBILE	MOTORCYCLE
BMW	340I	null
BMW	540I	null
BMW	null	M1000RR

DESIRED OUTPUT:

MAKE	TYPE	MODEL
BMW	AUTOMOBILE	340I
BMW	AUTOMOBILE	540I
BMW	MOTORCYCLE	M1000RR

Thanks

Is this not just a `CASE`? `Type = CASE WHEN AUTOMOBILE IS NOT NULL THEN ...` and `Model = ISNULL(AUTOMOBILE, MOTORCYCLE)` — Charlieface, Dec 11 '21 at 20:09

score 0 · Answer 1 · answered Dec 11 '21 at 19:59

0

Already answered here: Unpivot with column name

SELECT DISTINCT U.MAKE, U.TYPE, U.MODEL
FROM VEHICLE
UNPIVOT
(
     MODEL
     FOR TYPE IN (AUTOMOBILE, MOTORCYCLE)
) U
ORDER BY U.MAKE

answered Dec 11 '21 at 19:59

Mav

37
5

SQL Multiple Column Names to Single Column 'Type'

1 Answers1