0

Example

Team | Person1 | Person2 | Person3

change to

Team | Person 1
Team | Person 2
Team | Person 3

I wasn't sure how to describe exactly what it was that I wanted to do, sorry if this is a duplicate.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3307170
  • 11
  • 3
  • 1
    You're looking for `UNPIVOT` plenty of good examples available. Here's one: http://www.mssqltips.com/sqlservertip/3000/use-sql-servers-unpivot-operator-to-help-normalize-output/ – Hart CO Jan 13 '15 at 22:07
  • 1
    [this](http://stackoverflow.com/questions/19055902/unpivot-with-column-name) question should show you what you need. – The_DemoCorgin Jan 13 '15 at 22:10

2 Answers2

1

Use UNPIVOT

For table dbo.Teams, using your example:

SELECT Team, Person
FROM
(
  SELECT Team, Person1, Person2, Person3
  FROM dbo.Teams
) as cp
UNPIVOT
(
  Person FOR Persons IN (Person1, Person2, Person3)
) AS up;

While using UNIONs works, it does not scale well, costing a lot more for each additional column you need to transpose.

NOTE: This is basically a clone of the solution to a near identical question at this article, provided in the initial question comments by user @Goat CO. See the link for additional information and expansion on this solution.

The_DemoCorgin
  • 744
  • 6
  • 19
0

I think you might try (besides the the solution user @GoatCO mentioned in the comments) the following query:

SELECT Team, [Person 1] As Person_Column
FROM _table
UNION ALL
SELECT Team, [Person 2] As Person_Column
FROM _table
UNION ALL
SELECT Team, [Person 3] As Person_Column
FROM _table