26.7.08

Rows to Columns in SQL Server

In this article, Lets see how to convert your table rows into column wise. We can achieve it using pivot operation in SQL Server 2005. I achieved the same with some other logics.

Code Snippet
create table VenkatTable (id int, question varchar(20),answer varchar(20))
insert into VenkatTable values(1,'Name','Manoj')
insert into VenkatTable values(1,'Qualification','BE')
insert into VenkatTable values(1,'Nationality','Indian')
insert into VenkatTable values(2,'Name','Venkat')
insert into VenkatTable values(2,'Qualification','BE')
insert into VenkatTable values(3,'Qualification','BE')
select * from VenkatTable



The above queries will create a table as below,



I need a result set as below screen shot. It can be achieved using pivot operator in SQL Server 2005. I have tried with some other logic to achieve the same. Lets see how to achieve the below output.



Code Snippet

SELECT id,
[Name]= (SELECT answer FROM VenkatTable WHERE question = 'Name' and id=m.id),
[Qualification]= (SELECT answer FROM VenkatTable WHERE question = 'Qualification' and id=m.id) ,
[Nationality]= (SELECT answer FROM VenkatTable WHERE question = 'Nationality' and id=m.id)
FROM VenkatTable m
GROUP BY id

Happy Learning!!!
Regards,
Venkatesan Prabu .J
at

6 comments:

  1. AnonymousSeptember 12, 2008 at 3:51 AM

    Very neat, very helpful, thank you

    ReplyDelete
  2. Name: Prabhat NathNovember 25, 2008 at 7:40 AM

    Hi Very Nice post. Thanks for sharing...

    I am using this for my case:

    My Case:

    Tab1
    Col1 Col2
    --------------
    string1 123
    string2 456
    string3 124
    string1 101
    string4 250
    string3 100
    string4 876

    I Want output like:

    String1 String2 String4
    -----------------------
    123 456 876

    When I apply your Logic I get NULLs with 4 rows, While I need only ONE row.

    My Query looks like:

    SELECT
    [String1]= (SELECT MAX(Col2) FROM Tab1 WHERE Col1 = 'String1' AND Tab1.Col1=A.Col1)
    ,[String1]= (SELECT MAX(Col2) FROM Tab1 WHERE Col1 = 'String2' AND Tab1.Col1=A.Col1)
    ,[String1]= (SELECT MAX(Col2) FROM Tab1 WHERE Col1 = 'String4' AND Tab1.Col1=A.Col1)
    FROM Tab1 A
    GROUP BY Col1

    Can you please guide me where I am doing wrong.

    ReplyDelete
  3. AnonymousDecember 4, 2008 at 12:43 AM

    remove group by

    ReplyDelete
  4. khenOctober 5, 2010 at 2:13 AM

    I'm getting the error for the following code, but I don't know why.

    SELECT CompanyName, [September]=(select count(AutoNo) where tbl201Files.dateStarted <= '12/31/2010' and tbl201Files.eoc >= '12/1/2010' group by companyname)
    FROM tbl201Files

    i want all the months to be the column then count the autono by each month.
    tnx

    ReplyDelete
  5. udham SinghNovember 25, 2011 at 1:22 AM

    Hi very nice post. I want my data in some another format

    id officetype address
    44 marketing xyz
    44 works abc
    44 factory mnl
    44 marketing abc
    44 works jkk
    55 marketing aaa
    55 works nnn
    55 works aaa

    can u provide me query for to convert rows into columns
    like

    44 marketing works factory
    44 marketing works null
    55 marketing works null
    55 null works null

    ReplyDelete
  6. udham SinghNovember 25, 2011 at 1:27 AM

    currently my data is below format
    id office address
    1 marketing abc
    1 factory aaab
    1 works axxd
    1 marketing abc
    1 factory asdf
    2 marketing abc
    2 factory abc
    2 marketing asdf

    please provide me query for
    given format data

    1 marketing works factory
    1 marketing works null
    2 marketing factory null
    2 marketing factory null

    ReplyDelete