如何在sql中将行转换为列 [英] how to transpose row to column in a sql

问题描述

 选择 BookTitle，Library_Department_ID，SectorID，sum（NoofCopies）来自 tbl_bookmst 其中状态=  1   group  < span class =code-keyword> by  BookTitle，Library_Department_ID，SectorID 

这是我的查询是。







我还有一个列（位）命名参考书的IsReference为1



和0本的期刊



现在我需要将此行转换为列具有名称参考和问题



如何编写查询

解决方案

请阅读我对这个问题的评论。

  SELECT  BookTitle，Library_Department_ID，SectorID ，[参考]，[问题] 
  FROM （
  SELECT  BookTitle，Library_Department_ID，SectorID，NoofCopies， CASE   WHEN  [Status] =  1  那么 ' 参考'  ELSE  ' 问题'  END  
  FROM  tbl_bookmst 
  WHERE  Status =  1  
） AS  DT 
 PIVOT（SUM（NoofCopies） FOR  [Status]  IN （[参考]，[问题]）） AS  PT 

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

解决方案

Please, read my comment to the question.

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

这篇关于如何在sql中将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！