-2

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 ?

Atzi
  • 457
  • 1
  • 6
  • 16

1 Answers1

2

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 |
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • I understood the question so much different – Lamak Sep 11 '14 at 20:50
  • 3
    @محمد رضا please explain something to me. How were you able to determine - within 47 seconds of this answer even appearing on the page - that it solved your problem? How were you able to even read it that quickly, never mind test that it solved the issue on *your* test data? – Aaron Bertrand Sep 11 '14 at 20:52
  • Yea it is a bit confusing, at one point I thought OP is saying that all this `a, b1, b2, b3` is in one column :) – M.Ali Sep 11 '14 at 20:53
  • good question @AaronBertrand lol, Anyway I think this SQL FIDDLE OUTPUT is really a cool feature. – M.Ali Sep 11 '14 at 20:54
  • I thought that the question says that he has 4 columns, `a, b1, b2, b3`, that's why I don't see how this solves his problem – Lamak Sep 11 '14 at 20:55
  • It did, @Lamak. It would be clearer if there was a 'COL_1_5' that holds the original b1-b2-b3 names. Here's the [fiddle](http://sqlfiddle.com/#!3/d41d8/38854) to show what I mean. – Ryan B. Sep 11 '14 at 22:07