如何在MS SQL中透视表 [英] How to Pivot a Table in MS SQL

问题描述

 选择 Floorno，RoomNo 来自楼层

 First_Floor 101 
 First_Floor 102 
 Second_Floor 201 
 Second_Floor 202 
 Third_Floor 301 
 Third_Floor 302 

*************************** ***********



我想把这张桌子显示为

 First_Floor 101 102 
 Second_Floor 201 202 
 Third_Floor 301 302 

请填写完整查询。

解决方案

试试吧：

1）静态版本：

  DECLARE   @ rooms   TABLE （Floorno  VARCHAR （ 30 ），RoomNo  INT ）
 
  INSERT   INTO   @ rooms （Floorno，RoomNo）
  SELECT  '  First_Floor'， 101  
  UNION   ALL   SELECT  '  First_Floor'， 102  
  UNION   ALL   SELECT  '  Second_Floor'， 201  
  UNION   ALL   SELECT  '  Second_Floor'， 202  
  UNION   ALL   SELECT  '  Third_Floor'， 301  
  UNION   ALL   SELECT  '   Third_Floor'， 302  
 
  SELECT  Floorno，[ 1 ]，[ 2 ] 
  FROM （
  SELECT  Floorno，RoomNo，ROW_NUMBER（） OVER （ PARTITION   BY  Floorno 订购  BY  Floorno，RoomNo） AS  RoomID 
  FROM   @ rooms  
） AS  DT 
 PIVOT（MAX（RoomNo） FOR  RoomID  IN （[ 1 ]，[ 2 ]）） AS  PT 

结果：

 Floorno 1 2 
 ------ ---------------------- 
 First_Floor 101 102 
 Second_Floor 201 202 
 Third_Floor 301 302 

2）动态版

 创建  TABLE  #rooms（Floorno  VARCHAR （ 30 ），RoomNo  INT ）
 
  INSERT   INTO  #rooms（Floorno，RoomNo）
  SELEC T  '  First_Floor'， 101  
  UNION   ALL   SELECT  '  First_Floor'， 102  
  UNION   ALL   SELECT  '  Second_Floor'， 201  
  UNION   ALL   SELECT  '  Second_Floor'， 202  
  UNION   ALL   SELECT  '  Third_Floor'， 301  
  UNION   ALL   SELECT  '  Third_Floor'， 302  
  UNION   ALL   SELECT  '  Third_Floor'， 303  
 
  DECLARE   @cols   VARCHAR （ 300  ）
  DECLARE   @ dt   VARCHAR （ 2000 ）
  DECLARE   @ pt < /秒pan>  VARCHAR （MAX）
 
  SET   @cols  = STUFF（（ SELECT   DISTINCT  ' ]，[' +  CONVERT （ NVARCHAR （ 10 ），O。[RoomID]）
  FROM （
  SELECT  ROW_NUMBER（） OVER （ PARTITION   BY  Floorno  ORDER   BY  Floorno，RoomNo） AS  RoomID 
  FROM  #rooms 
） AS  O 
  ORDER   BY  ' ]，[' +  CONVERT （ NVARCHAR （ 10 ），O。[RoomID]）
  FOR  XML PATH（' '））， 1 ， 2 ，' '）+ ' ]' 
 
 
  SET   @ dt  = N '  SELECT Floorno，RoomNo，ROW_NUMBER（）OVER（Floorno ORDER BY Floorno，RoomNo分区）AS RoomID 
来自#rooms' 
  SET   @ pt  = N '   SE LECT Floorno，' +  @cols  + ' ' + 
 '  FROM（' +  @ dt  + ' ）AS DT' + 
 < span class =code-string>'  PIVOT（MAX（RoomNo）FOR RoomID IN（' +  @cols  + ' ））AS PT' 
 
 < span class =code-keyword> EXEC （ @ pt ）
 
  DROP   TABLE  #rooms 

结果：

 Floorno 1 2 3 
 ----------------------------- ------- 
 First_Floor 101 102 NULL 
 Second_Floor 201 202 NULL 
 Third_Floor 301 302 303 

select Floorno,RoomNo from floor

First_Floor	101
First_Floor	102
Second_Floor	201
Second_Floor	202
Third_Floor	301
Third_Floor	302

**************************************

I want to show this table as

First_Floor   101   102
Second_Floor  201   202
Third_Floor   301   302

Please Write the Full Query.

解决方案

Try it:
1) Static version:

DECLARE @rooms TABLE (Floorno VARCHAR(30),RoomNo INT)

INSERT INTO @rooms (Floorno ,RoomNo)
SELECT 'First_Floor', 101
UNION ALL SELECT 'First_Floor', 102
UNION ALL SELECT 'Second_Floor', 201
UNION ALL SELECT 'Second_Floor', 202
UNION ALL SELECT 'Third_Floor', 301
UNION ALL SELECT 'Third_Floor', 302

SELECT Floorno, [1], [2]
FROM (
	SELECT Floorno ,RoomNo, ROW_NUMBER() OVER (PARTITION BY Floorno ORDER BY Floorno, RoomNo) AS RoomID
	FROM @rooms
	) AS DT
PIVOT(MAX(RoomNo) FOR RoomID IN([1],[2])) AS PT

Result:

Floorno          1       2
----------------------------
First_Floor	101	102
Second_Floor	201	202
Third_Floor	301	302

2) Dynamic version

CREATE TABLE #rooms (Floorno VARCHAR(30),RoomNo INT)

INSERT INTO #rooms (Floorno ,RoomNo)
SELECT 'First_Floor', 101
UNION ALL SELECT 'First_Floor', 102
UNION ALL SELECT 'Second_Floor', 201
UNION ALL SELECT 'Second_Floor', 202
UNION ALL SELECT 'Third_Floor', 301
UNION ALL SELECT 'Third_Floor', 302
UNION ALL SELECT 'Third_Floor', 303

DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(NVARCHAR(10),O.[RoomID])
					FROM (
						SELECT ROW_NUMBER() OVER (PARTITION BY Floorno ORDER BY Floorno, RoomNo) AS RoomID
						FROM #rooms
					) AS O
					ORDER BY '],[' + CONVERT(NVARCHAR(10),O.[RoomID])
			FOR XML PATH('')),1,2,'') + ']'


SET @dt = N'SELECT Floorno ,RoomNo, ROW_NUMBER() OVER (PARTITION BY Floorno ORDER BY Floorno, RoomNo) AS RoomID
		FROM #rooms'
SET @pt = N'SELECT Floorno, ' + @cols + ' ' +
	'FROM (' + @dt +  ') AS DT ' +
	'PIVOT(MAX(RoomNo) FOR RoomID IN(' + @cols + ')) AS PT'

EXEC(@pt)

DROP TABLE #rooms

Result:

Floorno          1       2       3
------------------------------------
First_Floor	101	102	NULL
Second_Floor	201	202	NULL
Third_Floor	301	302	303

这篇关于如何在MS SQL中透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！