CODEWITHSUNDEEP
sqlsql-server
Abhijit sarkar
Abhijit sarkar

Reputation: 27

Append extra column value to previous column

SQL Server problem: I want to append extra column value to previous column set:

Name1 Roll1   Address1 Name2    Roll2 Address2      Namen  Rolln Addressn 
-------------------------------------------------------------------------
A       1      abc     B         2      bca          C      3      bbv

Result set should be like this:

   Name Roll Address
   ------------------
    A     1     abc
    B     2     bca 
    C     3     bbv

Upvotes: 0

Views: 103

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want to unpivot the data. In SQL Server, I would recommend APPLY:

select v.*
from t cross apply
     (values (name1, roll1, address1),
             (name2, roll2, address2),
             (name3, roll3, address3)
     ) v(name, roll, address);

This has two advantages over a union all approach. First, it scans the original table only once. This is not much of a performance gain for a table, but if the "table" is really a view or a complex query, that can be a big win.

Second, the logic is all in one place. For just selecting columns, this isn't a big deal. But if you wanted to filter out null values of name, then you can just add the condition once: where v.name is not null.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

A union query is one option here:

SELECT Name1 AS Name, Roll1 AS Roll, Address1 AS Address FROM yourTable
UNION ALL
SELECT Name2, Roll2, Address2 FROM yourTable
UNION ALL
SELECT Name3, Roll3, Address3 FROM yourTable;

Upvotes: 1

Related Questions