Select values from multiple columns into single column in SQL Server

Last Reply 2 months ago By pandeyism

Posted 2 months ago

I have a table like this:

Name1    Name2    Department1    Department2    Location1   Location2  
 ----------------------------------------------------------------------
 Jhon     Alex     IT             Marketing      London      Seattle
 Mark     Dan      Sales          R&D            Paris       Tokyo

 

How can I query these results in this format:

 Name        Department      Location
 ---------------------------------------
 Jhon        IT              London
 Alex        Marketing       Seattle
 Mark        Sales           Paris
 Dan         R&D             Tokyo
You are viewing reply posted by: pandeyism 2 months ago.
Posted 2 months ago

Hi chetan,

Refer below sample.

DECLARE @TestDemo AS TABLE(Name1 VARCHAR(10),Name2 VARCHAR(10),Department1 VARCHAR(10),Department2 VARCHAR(10),Location1 VARCHAR(10),Location2 VARCHAR(10))
INSERT INTO @TestDemo VALUES('Jhon','Alex','IT','Marketing','London','Seattle')
INSERT INTO @TestDemo VALUES('Mark','Dan','Sales','R&D','Paris','Tokyo')

SELECT Name1 'Name',Department1 'Department',Location1  'Location' FROM @TestDemo
UNION ALL
SELECT Name2 'Name',Department2 'Department',Location2  'Location' FROM @TestDemo

Output

Name Department Location
Jhon IT London
Mark Sales Paris
Alex Marketing Seattle
Dan R&D Tokyo