我需要帮助将行拆分为列 [英] I need help to split rows to column

问题描述

我有像

i have columns like

Floor           RoomNo  
 
 1st floor      101
 2nd floor      201
 3rd floor      301
 4th floor      401
 1st floor      102
 2nd floor      202 
 3rd floor      302
 4th floor      402
 1st floor      103
 2nd floor      203
 3rd floor      303
 4th floor      403
 1st floor      104
 2nd floor      204
 3rd floor      304
 4th floor      404

i需要答案

i need the answer of

1st floor       101  102  103  104
2nd floor       201  202  203  204
3rd floor       301  302  303  304
4th floor       401  402  403  404

推荐答案

HI，请在您的查询中使用Pivot将行转换为列。请参阅以下链接以获取帮助。





在SQL查询中使用Pivot的简单方法 [ ^ ]



在sql server中有效地将行转换为列 - Stack Overflow [ ^ ]

please use Pivot in your query which will convert rows into columns. See the links below for your help.


Simple Way To Use Pivot In SQL Query[^]

Efficiently convert rows to columns in sql server - Stack Overflow[^]

这是相当讨厌的 - 它基本上是一个GROUP和一个PIVOT，没有GROUP子句...

但是，你可以使用ROW_NUMBER：

That's fairly nasty - it's basically a GROUP and a PIVOT, without the GROUP clause...
But, you can do it, using the ROW_NUMBER:

SELECT * FROM
   (SELECT Floor, RoomNo, ROW_NUMBER() OVER (PARTITION BY Floor ORDER BY RoomNo) rn FROM MyTable1) sr
PIVOT (MIN(RoomNo) FOR rn IN ([1], [2], [3], [4])) pvt

这篇关于我需要帮助将行拆分为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！