1

I have a table as shown below.My question is:How can I convert columns into rows? I'm using Microsoft SQL Server

   sip_RECno  user1     user2    user3     user4       

   1          ram       ravi     sam       raj

i need op like below

 user
 ram
 ravi
 sam
 raj

how to do it? thanks

carexcer
  • 1,407
  • 2
  • 15
  • 27
Happy
  • 1,105
  • 5
  • 17
  • 25
  • possible duplicate of [Efficiently convert rows to columns in sql server 2008](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server-2008) – Betlista Jan 22 '14 at 16:03
  • 2
    possibility of "converting columns into rows" being duplicate of "converting rows into columns" is quite little though. – Kuba Wyrostek Jan 22 '14 at 16:15
  • really? PIVOT vs. UNPIVOT - http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx – Betlista Jan 22 '14 at 18:18

2 Answers2

8

Your Data

DECLARE @TABLE TABLE 
(sip_RECno INT,user1 VARCHAR(10),user2 VARCHAR(10)
,user3 VARCHAR(10),user4 VARCHAR(10))      

INSERT INTO @TABLE VALUES
(1,'ram','ravi','sam','raj')

Query

;WITH CTE
AS
  (
  SELECT * FROM (
  SELECT user1 ,user2 ,user3 , user4 FROM @TABLE) T
  UNPIVOT ( Value FOR N IN (user1 ,user2 ,user3 , user4))P
  )
 SELECT Value AS Users
 FROM CTE

Result Set

╔═══════╗
║ Users ║
╠═══════╣
║ ram   ║
║ ravi  ║
║ sam   ║
║ raj   ║
╚═══════╝
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • how to do for dynamic columns ? – Happy Jan 22 '14 at 16:30
  • @Happy it will be another long answer to explain this have a look here scroll down to the middle of this page to see how dynamicaly pivot/unpivot data http://www.codeproject.com/Tips/516896/Calculate-aggregates-for-dynamic-columns-using-UNP – M.Ali Jan 22 '14 at 16:35
5

You can simply UNPIVOT()

select [user] from table_name unpivot 
      (
       [user]
       for [userid] in ([user1], [user2], [user3], [user4]) 
      )unpvt

Demo

Kiril Rusev
  • 745
  • 3
  • 9