如何在sql server 2008中显示Pivot结果 [英] How to display Pivot result in sql server 2008

问题描述

表：

  CREATE  表 PUBLISHER_BATCH 
（
 PUBLISHER_ID  INT ，
 NAME_PUBLISHER  VARCHAR （< span class =code-digit> 50 ），
 NO_RECORDS_IMPORTED  INT ，
 NO_RECORDS_EXPORTED  INT ，
 NO_RECORDS_DELETED  INT ，
状态 INT ， 
已锁定 INT ，
  SOURCE   INT  
）
 
 
  INSERT   INTO  PUBLISHER_BATCH  VALUES （ 1 ，'  Publisher1'， 18 ，  10 ， 3 ，'  2'， 1 ， 2 ）
  INSERT   INTO  PUBLISHER_BATCH  VALUES （ 2 ，'  Publisher2'， 28 ， 12 ， 5 ，'  2'， 1 ， 2 ）
  INSERT   INTO  PUBLISHER_BATCH  VALUES （ 3 ，'  Publisher3 '， 25 ， 0 ， 0 ，'  1'， 0 ， 1 ）

但我的要求显示结果如下：

 PUBLISHER_NAME OnStock Imported已删除删除总额
发布者1 0 18 10 3 31 
发布者2 0 27 11 5 43 
发布者3 25 0 0 0 25 
总计25 45 21 8 99 

OnStock：LOCKED = 0且Status = 1的所有记录的数量



我无法制作来自sql查询的OnStock值。我是系统中的新手..

任何人都可以帮我在Sql server 2008中生成这个结果集和

以及我将如何获得两行中的总数（极右行和极低行）



先谢谢。

解决方案

试试这个：

  DECLARE   @ PUBLISHER_BATCH   TABLE  
（PUBLISHER_ID  INT ，NAME_PUBLISHER  VARCHAR （  50 ），NO_RECORDS_IMPORTED  INT ，
 NO_RECORDS_EXPORTED  INT ，NO_RECORDS_DELETED  INT ，STATUS  INT ，已锁定 INT ，
  SOURCE   INT ）
 
  INSERT   INTO   @ PUBLISHER_BATCH   VALUES （ 1 ，'  Publisher1'， 18 ， 10 ， 3 ，'  2'， 1 ， 2 ）
  INSERT   INTO   @ PUBLISHER_BATCH   VALUES （ 2 ，'  Publisher2'，  27 ， 11 ， 5 ，'  2'， 1 ， 2 ）
 < span class =code-keyword> INSERT   INTO   @ PUBLISHER_BATCH   VALUES （ 3 ，'  Publisher3'， 25 ， 0 ， 0 ，'  1'， 0 ， 1 ）
 
  SELECT  NAME_PUBLISHER，OnStock，NO_RECORDS_IMPORTED， NO_RECORDS_EXPORTED，NO_RECORDS_DELETED，NO_RECORDS_IMPORTED + NO_RECORDS_EXPORTED + NO_RECORDS_DELETED  AS  TOTAL 
  FROM （
 < span class =code-keyword> SELECT  T1.NAME_PUBLISHER， COALESCE （T2.OnStock， 0 ） AS  OnStock，T1 .NO_RECORDS_IMPORTED，T1.NO_RECORDS_EXPORTED，T1.NO_RECORDS_DELETED 
  FROM   @ PUBLISHER_BATCH   AS  T1 
  LEFT   JOIN （
  SELECT  NAME_PUBLISHER，COUNT（*） AS  OnStock 
  FROM   @ PUBLISHER_BATCH  
  WHERE  LOCKED = 0  AND  STATUS = 1 
  GROUP   BY  NAME_PUBLISHER 
） AS  T2  ON  T1.NAME_PUBLISHER = T2.NAME_PUBLISHER 
 Ù NION   ALL  
  SELECT  '  总计'  AS  NAME_PUBLISHER，（ SELECT  COUNT（*） FROM   @ PUBLISHER_BATCH   WHERE  LOCKED =  0   AND  STATUS = 1） AS  OnStock，SUM（NO_RECORDS_IMPORTED） AS  NO_RECORDS_IMPORTED，SUM（NO_RECORDS_EXPORTED） AS  NO_RECORDS_EXPORTED，
 SUM（NO_RECORDS_DELETED） AS  NO_RECORDS_DELETED 
  FROM   @PUBLISHER_BATCH  
） AS  T 

结果：

Name_Pub .. OnS Imp Exp Del Total
Publisher1 0 18 10 3 31
Publisher2 0 27 11 5 43
Publisher3 1 25 0 0 25
总计1 70 21 8 99

请参阅我关于透视的文章： [ ^ ]

Table :

CREATE TABLE PUBLISHER_BATCH
(
  PUBLISHER_ID INT,
  NAME_PUBLISHER VARCHAR(50),
  NO_RECORDS_IMPORTED INT,
  NO_RECORDS_EXPORTED INT,
  NO_RECORDS_DELETED INT,
  STATUS INT,
  LOCKED INT,
  SOURCE INT
)


INSERT INTO PUBLISHER_BATCH VALUES(1,'Publisher1',18,10,3,' 2',1,2)
INSERT INTO PUBLISHER_BATCH VALUES(2,'Publisher2',28,12,5,' 2',1,2)
INSERT INTO PUBLISHER_BATCH VALUES(3,'Publisher3',25,0,0,' 1',0,1)

but my requirment to display result is like below :

PUBLISHER_NAME  OnStock   Imported   Exported   Deleted  Total
Publisher1         0         18         10          3       31
Publisher2         0         27         11          5       43
Publisher3        25          0          0          0       25
Total             25         45         21          8       99

OnStock : Count of all the records where LOCKED=0 and Status=1

I am not able to make the "OnStock" value from the sql query. I am very new in the system..
Can anyone please help me to generate this Result set in Sql server 2008 and
and how i will get the total in both rows (extremely right row and extremely below row)

Thanks in Advance.

解决方案

Try this:

DECLARE @PUBLISHER_BATCH TABLE 
(  PUBLISHER_ID INT,   NAME_PUBLISHER VARCHAR(50),   NO_RECORDS_IMPORTED INT,
  NO_RECORDS_EXPORTED INT,   NO_RECORDS_DELETED INT,   STATUS INT,   LOCKED INT,
  SOURCE INT )
 
INSERT INTO @PUBLISHER_BATCH VALUES(1,'Publisher1',18,10,3,' 2',1,2)
INSERT INTO @PUBLISHER_BATCH VALUES(2,'Publisher2',27,11,5,' 2',1,2)
INSERT INTO @PUBLISHER_BATCH VALUES(3,'Publisher3',25,0,0,' 1',0,1)
 
SELECT NAME_PUBLISHER, OnStock, NO_RECORDS_IMPORTED, NO_RECORDS_EXPORTED, NO_RECORDS_DELETED, NO_RECORDS_IMPORTED + NO_RECORDS_EXPORTED + NO_RECORDS_DELETED AS TOTAL
FROM (
	SELECT T1.NAME_PUBLISHER, COALESCE(T2.OnStock,0) AS OnStock, T1.NO_RECORDS_IMPORTED, T1.NO_RECORDS_EXPORTED, T1.NO_RECORDS_DELETED
	FROM @PUBLISHER_BATCH AS T1 
	LEFT JOIN (
		SELECT NAME_PUBLISHER, COUNT(*) AS OnStock
		FROM @PUBLISHER_BATCH
		WHERE LOCKED =0 AND STATUS =1
		GROUP BY NAME_PUBLISHER 
		) AS T2 ON T1.NAME_PUBLISHER = T2.NAME_PUBLISHER
	UNION ALL
	SELECT 'Total' AS NAME_PUBLISHER, (SELECT COUNT(*) FROM @PUBLISHER_BATCH WHERE LOCKED = 0 AND STATUS =1) AS OnStock, SUM(NO_RECORDS_IMPORTED) AS NO_RECORDS_IMPORTED, SUM(NO_RECORDS_EXPORTED) AS NO_RECORDS_EXPORTED,
			SUM(NO_RECORDS_DELETED) AS NO_RECORDS_DELETED 
	FROM @PUBLISHER_BATCH  
) AS T

Result:

Name_Pub..	OnS	Imp	Exp	Del	Total
Publisher1	0	18	10	3	31
Publisher2	0	27	11	5	43
Publisher3	1	25	0	0	25
Total		1	70	21	8	99

See my article about pivoting: here[^]

这篇关于如何在sql server 2008中显示Pivot结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！