Breaking one any row in a query into multiple rows

Question

I have a table with these columns : a, b1, b2, b3

and I want to get query from this table like this :

_______________
| col1 | col2 |
|-------------|
|   a  |  b1  |
|   a  |  b2  |
|   a  |  b3  |
|_____________|

is it posible with on SELECT command ?

Answer 1

Doing UNION ALL is one way, but you can also use UNPIVOT query see below,

Anyway I think UNPIVOT will be more efficient than doing union all multiple times.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Query 1:

DECLARE @TABLE TABLE(col1 VARCHAR(5), col2 VARCHAR(5)
             , Col3 VARCHAR(5), Col4 VARCHAR(5))
INSERT INTO @TABLE VALUES 
   ( 'a' , 'B1', 'B2', 'B3'),
   ( 'd' , 'E1', 'E2', 'E3')


SELECT col1 
      ,Vals AS Col2
FROM @TABLE t 
UNPIVOT (Vals FOR N IN (col2,col3,col4) ) up

Results:

| COL1 | COL2 |
|------|------|
|    a |   B1 |
|    a |   B2 |
|    a |   B3 |
|    d |   E1 |
|    d |   E2 |
|    d |   E3 |