Issue in Convert row into column

Question

I have a table- EmployeeDetails. In which we have two columns :- ColumnName and Details. Below is the table:-

ColumnNames     Details
-----------     -------
Sno             1
Firstname       Daya
Lastname        Sharma
EmailId         <email1>@gmail.com
sno             2
Firstname       Kriti 
Lastname        joshi
EmailId         <email2>@yahoo.com

Now I need to convert firstname, lastname, emailed and sno in columns. How we can do this?

And I want to convert into Column Name as

Sno  FirstName  Lastname  Emailid
1    Daya       Sharma    <email1>@gmail.com
2    Kriti      Joshi     <email2>@yahoo.com

What DBMS are you using? SQL Server, MySQL, PostgreSQL or something else? — jpw, Nov 11 '13 at 09:33
You can try with PIVOT like this : http://stackoverflow.com/questions/9117955/making-row-values-into-column-values-sql-pivot — Upendra Chaudhari, Nov 11 '13 at 09:34
You need to use SQL Transpose command. Look this: http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — L.Grillo, Nov 11 '13 at 09:35
and of many many others: http://stackoverflow.com/search?q=pivot+sql — alko, Nov 11 '13 at 09:35
@VANDANA PANDEY : should not use original mail ids , just a suggestion , might cause the spam flow — Hussain Akhtar Wahid 'Ghouri', Nov 11 '13 at 09:36
@UpendraChaudhari I have used pivot but not working. B'coz of aggregate function which I have to use in pivot it result only one record... Please try from ur end nd then let me know... — VANDANA PANDEY, Nov 11 '13 at 09:41
You **have to have** at least one more column (let's call it `EmployeeID`) to be able to properly pivot your data. Otherwise there is no way to tell which last name belongs to which first name, son, emailid etc. — peterm, Nov 11 '13 at 09:45
@Vijaykumar This query is gud but resulting only one record as you have used 'max' aggregate function. — VANDANA PANDEY, Nov 11 '13 at 09:49
@peterm K in place of Sno if i'l take EmployeeID then is there any other way.. — VANDANA PANDEY, Nov 11 '13 at 09:52

score 0 · Answer 1 · answered Nov 11 '13 at 09:40

0

select sno,firstname,lastname,Emailid
from
(
select columnnames,details from table
)d

pivot
(
max(details)
for columnnames in([sno],[firstname],[lastname],[emailid])
)piv;

answered Nov 11 '13 at 09:40

vhadalgi

7,027
6
38
67

Please always provide explanations. Code only answers are being frowned upon. – cfi Nov 11 '13 at 09:58

score 0 · Answer 2 · edited May 23 '17 at 12:19

0

You may refer below link for the answer. Same question has been posted at below link :

Efficiently convert rows to columns in sql server

edited May 23 '17 at 12:19

Community

1
1

answered Nov 11 '13 at 09:53

SpiderCode

10,062
2
22
42

Hiii I have seen that example. They used max in it which resulting one record. But I want all records. – VANDANA PANDEY Nov 11 '13 at 10:25

score 0 · Answer 3 · answered Nov 11 '13 at 10:19

You have to have at least one more column (let's call it EmployeeID) to be able to properly pivot your data. Otherwise there is no way to tell which last name belongs to which first name, Son, emailid etc

If you were to have such a column then PIVOT would work as expected

SELECT Sno, Firstname, Lastname, Emailid
  FROM
(
  SELECT EmployeeId, ColumnNames, Details 
    FROM EmployeeDetails
) s
PIVOT
(
  MAX(Details)
  FOR ColumnNames IN([Sno],[Firstname],[Lastname],[Emailid])
) p;

Output:

| SNO | FIRSTNAME | LASTNAME |            EMAILID |
|-----|-----------|----------|--------------------|
|   1 |      Daya |   Sharma | @gmail.com |
|   2 |     Kriti |    joshi | @yahoo.com |

Here is SQLFiddle demo

Issue in Convert row into column

3 Answers3