SQL Server columns as rows in result of query

Question

How to get table where some columns are queried and behaves as rows?

Source table

ID | Name | Funct    | Phone1 | Phone2 | Phone3
1  | John | boss     | 112233 | 114455 | 117788
2  | Jane | manager  | NULL   | NULL   | 221111
3  | Tony | merchant | 441100 | 442222 | NULL

Wanted result

ID | Name | Funct    | Phone  | Ord
1  | John | boss     | 112233 | 1
1  | John | boss     | 114455 | 2
1  | John | boss     | 117788 | 3
2  | Jane | manager  | 221111 | 3
3  | Tony | merchant | 441100 | 1
3  | Tony | merchant | 442222 | 2

Ord is a column where is the order number (Phone1...Phone3) of the original column

EDITED:

OK, UNION would be fine when phone numbers are in separed columns, but what if the source is following (all numbers in one column)?:

ID | Name | Funct    | Phones
1  | John | boss     | 112233,114455,117788
2  | Jane | manager  | 221111
3  | Tony | merchant | 441100,442222

Here I understand, that column Ord is a non-sense (so ignore it in this case), but how to split numbers to separed rows?

If you know that you have 3 phones for each customer than UNION ALL is a good option — Murtaza
– Murtaza, Commented Aug 21, 2014 at 11:58

Gordon Linoff · Accepted Answer · 2014-08-21 12:06:44Z

2

The easiest way is to use union all:

select id, name, funct, phone1 as phone, 1 as ord
from source
where phone1 is not null
union all
select id, name, funct, phone2 as phone, 2 as ord
from source
where phone2 is not null
union all
select id, name, funct, phone3 as phone, 3 as ord
from source
where phone3 is not null;

You can write this with a cross apply as:

select so.*
from source s cross apply
     (select s.id, s.name, s.funct, s.phone1 as phone, 1 as ord union all
      select s.id, s.name, s.funct, s.phone2 as phone, 2 as ord union all
      select s.id, s.name, s.funct, s.phone3 as phone, 3 as ord
     ) so
where phone is not null;

There are also methods using unpivot and cross join/case.

edited Aug 21, 2014 at 12:06

answered Aug 21, 2014 at 11:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

procma Over a year ago

OK, and is there another one solution to do that? With cross aplly or something like that?

Jithin Shaji · Accepted Answer · 2014-08-21 13:09:14Z

1

Please see the answer below,

Declare @table table
(ID int, Name varchar(100),Funct varchar(100),Phones varchar(400))

Insert into @table Values 
(1,'John','boss','112233,114455,117788'),
(2,'Jane','manager','221111' ),
(3,'Tony','merchant','441100,442222')

Select * from @table

Result:

enter image description here

Code:

Declare @tableDest table
([ID] int, [name] varchar(100),[Phones] varchar(400))

Declare @max_len int,
        @count int = 1

Set @max_len =  (Select max(Len(Phones) - len(Replace(Phones,',','')) + 1)
                From    @table)

While @count <= @max_len
begin
    Insert  into @tableDest
    Select  id,Name,
            SUBSTRING(Phones,1,charindex(',',Phones)-1)
    from    @table
    Where   charindex(',',Phones) > 0
    union   
    Select  id,Name,Phones
    from    @table
    Where   charindex(',',Phones) = 0

    Delete from @table
    Where   charindex(',',Phones) = 0

    Update  @table
    Set     Phones =  SUBSTRING(Phones,charindex(',',Phones)+1,len(Phones))
    Where   charindex(',',Phones) > 0

    Set     @count = @count + 1
End
------------------------------------------
Select  * 
from    @tableDest
Order By ID
------------------------------------------

Final Result:

enter image description here

answered Aug 21, 2014 at 13:09

Jithin Shaji

6,1015 gold badges27 silver badges47 bronze badges

1 Comment

procma Over a year ago

very nice Jithin, thanx for useful code, but I have asked for using it into a query (select, view...). How to use it inside a query? Using UDF...or...is there another way how to write the loop into a select without inserting to a variable table?

Rohin · Accepted Answer · 2014-08-21 12:06:11Z

0

       SELECT CONCAT(
      'CREATE TABLE New_Table (',GROUP_CONCAT(
                            DISTINCT CONCAT(Name, ' VARCHAR(50)')
                            SEPARATOR ','),');')
      FROM
      Previous_Table
      INTO @sql;
      PREPARE stmt FROM @sql;
      EXECUTE stmt;

This query generates a row in a table from the column values of another table. This may help you

answered Aug 21, 2014 at 12:06

Rohin

5263 gold badges8 silver badges20 bronze badges

Collectives™ on Stack Overflow

SQL Server columns as rows in result of query

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related