how to convert row to column in sql

Question

0.00/5 (No votes)

See more:

Column Name                     Type                               Copies
        
ELECTRICAL MACHINES -II		           Issue	                               48

ELECTRICAL MACHINES -II		         Reference	                                2

how to convert it in to single row like

              Name                               Reference         Issue  

ELECTRICAL MACHINES -II                            2                       48

Posted 27-Feb-14 23:36pm

Member 10517120

Updated 27-Feb-14 23:41pm

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-02-27T23:44:00

Solution 1

That what you need is called PIVOT[^] ;)

SQL

SELECT [Name], [Reference], [Issue]
FROM (
    SELECT [Name], [Type], [Copies] 
    FROM MyTable
) AS DT
PIVOT(MAX(Copies) FOR [Type] IN ([Reference], [Issue])) AS PT

Posted 27-Feb-14 23:44pm

Maciej Los

Comments

Member 10517120 28-Feb-14 5:47am

tnk u . let me check

Maciej Los 28-Feb-14 5:52am

You're very welcome. If it was helpful, please mark this answer as a solution (green button).

Member 10517120 28-Feb-14 6:15am

select BookTitle,Library_Department_ID,SectorID,sum(NoofCopies) from tbl_bookmst where Status= 1 group by BookTitle,Library_Department_ID,SectorID

this is wt my query is .

I'm having one more column(bit) named IsReference which is 1 for Referencebook

and 0 for Issuebook

now i need to Convert this row to column With the name reference and Issue

How i need to Write the query

Maciej Los 28-Feb-14 6:24am

SELECT BookTitle, Library_Department_ID, SectorID, [Reference], [Issue]
FROM (
    SELECT BookTitle, Library_Department_ID, SectorID, NoofCopies, CASE WHEN [Status] = 1 THEN 'Reference' ELSE 'Issue' END
    FROM tbl_bookmst
    WHERE Status= 1
) AS DT
PIVOT(SUM(NoofCopies) FOR [Status] IN([Reference], [Issue])) AS PT

how to convert row to column in sql

1 solution

Solution 1

Add your solution here

Preview 0

how to convert row to column in sql

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us