SQL Server or Excel - transpose columns to rows

I have a table that looks like this:

District   Code    SubDistrict1   SubDistrict2   Subdistrict3
A             11         Apple                Orange            Pear
B             131      Banana               Wood              Desert
C             53         Apple               Salt                  Pepper


And I need it to look like this:

District   Code    Subdistrict
A              11        Apple
A              11       Orange
etc...
C            53        Apple
etc...

Normally this would be simple but I have thousands of rows with several hundred different codes. Is there a simple automated way to lay this out in SQL?
gwarcherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
for SQL, you probably can try:
Select District, Code, SubDistrict1 as SubDistrict
From yourTable
Where SubDistrict1 is not null

union

Select District, Code, SubDistrict2 as SubDistrict
From yourTable
Where SubDistrict2 is not null

union

Select District, Code, SubDistrict3 as SubDistrict
From yourTable
Where SubDistrict3 is not null

order by 1, 2, 3

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
Using CROSS APPLY with VALUES is my preferred method for this and only requires a single pass of the table
select
      district
    , code
    , ca.SubDistrict
from table1
cross apply (
        values 
             (SubDistrict1)
           , (SubDistrict2)
           , (SubDistrict3)
      ) ca (SubDistrict)
;

Open in new window

details
**MS SQL Server 2014 Schema Setup**:

    CREATE TABLE Table1
        ([District] varchar(1), [Code] int, [SubDistrict1] varchar(6), [SubDistrict2] varchar(6), [Subdistrict3] varchar(6))
    ;
        
    INSERT INTO Table1
        ([District], [Code], [SubDistrict1], [SubDistrict2], [Subdistrict3])
    VALUES
        ('A', 11, 'Apple', 'Orange', 'Pear'),
        ('B', 131, 'Banana', 'Wood', 'Desert'),
        ('C', 53, 'Apple', 'Salt', 'Pepper')
    ;
    
**Query 1**:

    select
          district
        , code
        , ca.SubDistrict
    from table1
    cross apply (
            values 
                 (SubDistrict1)
               , (SubDistrict2)
               , (SubDistrict3)
          ) ca (SubDistrict)
    

**[Results][2]**:
    | district | code | SubDistrict |
    |----------|------|-------------|
    |        A |   11 |       Apple |
    |        A |   11 |      Orange |
    |        A |   11 |        Pear |
    |        B |  131 |      Banana |
    |        B |  131 |        Wood |
    |        B |  131 |      Desert |
    |        C |   53 |       Apple |
    |        C |   53 |        Salt |
    |        C |   53 |      Pepper |

  [1]: http://sqlfiddle.com/#!6/b9613/1
  [2]: http://sqlfiddle.com/#!6/b9613/1/0

Open in new window

{+ edit}
The technique is based on reverse engineering the UNPIVOT command, see:
http://bradsruminations.blogspot.com.au/2010/02/spotlight-on-unpivot-part-1.html

personally I prefer the simple syntax of the cross apply t using UNPIVOT but it could also be used of course.
gwarcherAuthor Commented:
Both worked perfectly. Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.