how to convert rows to columns in SQL Server 2008

Question

I have a Table

F1      F2
-----------------
name    Happy
Company a
City    Gurgaon
name    Deepak
Company b
City    Delhi

I need the output as -

name    company   city
-----------------------    
Happy     a       Gurgaon
Deepak    b       Delhi

I am using the query

SELECT
   CASE WHEN F1='name'
    THEN F2 ELSE NULL END) AS name,
   CASE WHEN F1='Company'
     THEN F2 ELSE NULL END) AS Company,
   CASE WHEN F1='City'
     THEN F2 ELSE NULL END) AS city
FROM table1

but I am getting the output as :

name    company      city
-----------------------------
Happy       
          a 
                Gurgaon
Deepak      
          b 
                Delhi

Can someone help please? I do not want to use PIVOT. Can this be achieved using inner join

you can do it using pivot try the answer in given link http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — varsha, Dec 29 '14 at 12:21
possible duplicate of http://stackoverflow.com/questions/11311549/sql-move-data-from-rows-to-cols — varsha, Dec 29 '14 at 12:35
Do you have some sort of column or value that shows that `Happy`, `a` and `Gurgaon` all belong in the same row? Right now you aren't showing any way to associate the values into the same row. — Taryn, Dec 29 '14 at 12:36
ok..U are right.. I will try by adding a new column with values like - 1,1,1,2,2,2 — sam, Dec 29 '14 at 12:45

score 0 · Answer 1 · answered Jan 02 '15 at 04:54

Create a table to insert the elements with identity field

CREATE TABLE #TEMP(ID INT IDENTITY(1,1), F1 VARCHAR(20), F2 VARCHAR(20))

INSERT INTO #TEMP
SELECT * FROM
(
 SELECT 'name' F1,    'Happy'  F2
 UNION ALL
 SELECT 'Company', 'a'
 UNION ALL
 SELECT 'City',    'Gurgaon'
 UNION ALL
 SELECT 'name',    'Deepak'
 UNION ALL
 SELECT 'Company', 'b'
 UNION ALL
 SELECT 'City',    'Delhi'
)TAB

Since you don't have an Id for each set, we need to generate separate Id after every 3 records and pivot according to that Id.

SELECT name, Company,City
FROM 
(
       -- Generate seperate Id after every 3 records
       SELECT F1,F2,CAST((ID/3.5)AS INT)+1 RNO  
       FROM #TEMP
)P
PIVOT(MIN(F2)
      FOR F1 IN (name, Company,City)) AS PVTTable

RESULT

enter image description here

how to convert rows to columns in SQL Server 2008

1 Answers1