分组和切换列和行 [英] grouping and switching the columns and rows
问题描述
我不知道这是否正式被称为支点,但是我想要的结果是:
I don't know if this would officially be called a pivot, but the result that I would like is this:
+------+---------+------+
| Alex | Charley | Liza |
+------+---------+------+
| 213 | 345 | 1 |
| 23 | 111 | 5 |
| 42 | 52 | 2 |
| 323 | | 5 |
| 23 | | 1 |
| 324 | | 5 |
+------+---------+------+
我的输入数据采用以下格式:
my input data is in this form:
+-----+---------+
| Apt | Name |
+-----+---------+
| 213 | Alex |
| 23 | Alex |
| 42 | Alex |
| 323 | Alex |
| 23 | Alex |
| 324 | Alex |
| 345 | Charley |
| 111 | Charley |
| 52 | Charley |
| 1 | Liza |
| 5 | Liza |
| 2 | Liza |
| 5 | Liza |
| 1 | Liza |
| 5 | Liza |
+-----+---------+
因为我大约有100个名字,所以我不想像这样子做大量的子查询
because I have approximately 100 names, I don't want to have to do a ton of sub queries lik this
select null, null, thirdcolumn from...
select null, seconcolumn from...
select firstcolumn from...
是否可以通过PIVOT
或其他方式执行此操作?
Is there a way to do this with PIVOT
or otherwise?
推荐答案
您可以使用动态PIVOT
和ROW_NUMBER()
函数来做到这一点:
You can do this with dynamic PIVOT
and the ROW_NUMBER()
function:
DECLARE @cols AS VARCHAR(1000),
@query AS VARCHAR(8000)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(Name)
FROM (SELECT DISTINCT Name
FROM #test
)sub
ORDER BY Name
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')
,1,1,'')
PRINT @cols
SET @query = '
WITH cte AS (SELECT DISTINCT *
FROM #test)
,cte2 AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Apt)RowRank
FROM cte)
SELECT *
FROM cte2
PIVOT (max(Apt) for Name in ('+@cols+')) p
'
EXEC (@query)
如果您不希望列表是不同的,请删除上面的第一个cte,如果您想保持任意顺序,请将ORDER BY
更改为(SELECT 1)
:
If you don't want the list to be distinct, eliminate the first cte above, and if you want to keep arbitrary ordering change the ORDER BY
to (SELECT 1)
:
DECLARE @cols AS VARCHAR(1000),
@query AS VARCHAR(8000)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(Name)
FROM (SELECT DISTINCT Name
FROM #test
)sub
ORDER BY Name
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')
,1,1,'')
PRINT @cols
SET @query = '
WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY (SELECT 1))RowRank
FROM #test)
SELECT *
FROM cte
PIVOT (max(Apt) for Name in ('+@cols+')) p
'
EXEC (@query)
最后,如果您不想在结果中使用RowRank
字段,只需在SELECT
中重新使用@cols
变量:
And finally, if you didn't want the RowRank
field in your results, just re-use the @cols
variable in your SELECT
:
SET @query = '
WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY Name ORDER BY (SELECT 1))RowRank
FROM #test)
SELECT '+@cols+'
FROM cte
PIVOT (max(Apt) for Name in ('+@cols+')) p
'
EXEC (@query)
这篇关于分组和切换列和行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!