-1

I'm trying to use SQL to create a table with a reduced number of columns and an increased number of rows, from a table with many columns, by putting all columns in one column and all cells in another column.

My table:

Person Hobbies School Jobs
Mark Tennis University Doctor
Dave Chess Highschool Chef
Tim Travel University Sales

Desired output:

Person Column Value
Mark Hobbies Tennis
Mark School University
Mark Jobs Doctor
Dave Hobbies Chess
Dave School Highschool
Dave Jobs Chef
Tim Hobbies Travel
Tim School University
Tim Jobs Sales

How can this be done?

Thanks!

Shadow
  • 33,525
  • 10
  • 51
  • 64
userav
  • 7
  • 1
  • Check out this similar question: https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server – Missy Dec 16 '22 at 13:50

1 Answers1

1

You may need to select each field value separately, then apply a UNION ALL on the three obtained sets of records.

SELECT Person, 'Hobbies' AS Column_, Hobbies AS Value_ FROM tab
UNION ALL
SELECT Person, 'School' AS Column_, School AS Value_ FROM tab
UNION ALL
SELECT Person, 'Jobs' AS Column_, Jobs AS Value_ FROM tab
ORDER BY Person

Check the demo here.

lemon
  • 14,875
  • 6
  • 18
  • 38