SQL Server convert row values to columns

Question

I have an SQL 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

Fahmi · Accepted Answer · 2019-05-15 04:57:06Z

5

Use cross apply

DEMO

select name,department,location
from t
cross apply
(
  values(name1,department1,location1),(name2,department2,location2)
)cc (name, department,location)

OUTPUT:

name    department  location
Jhon    IT           London
Alex    Marketing    Seattle
Mark    Sales        Paris
Dan     R&D T        Tokyo

edited May 15, 2019 at 4:57

answered May 15, 2019 at 4:43

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2019-05-15 04:45:05Z

4

You could try to use SQL Server's UNPIVOT operator, but honestly a plain union query might even perform better:

SELECT Name1 AS Name, Department1 AS Department, Location1 AS Location FROM yourTable
UNION ALL
SELECT Name2, Department2, Location2 FROM yourTable;

Regarding your expected ordering, there is no sort of id column in your original table which maintains to which name pair each record belongs. So, what I have written above might be the best we can do here.

answered May 15, 2019 at 4:45

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

chetan kambli · Accepted Answer · 2019-05-15 06:26:57Z

0

Try This:

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

answered May 15, 2019 at 6:26

chetan kambli

8141 gold badge8 silver badges24 bronze badges

Collectives™ on Stack Overflow

SQL Server convert row values to columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related