SQL查询未知行到列 [英] SQL query unknown rows into columns

问题描述

我问了这个问题，它被标记为

I asked this question and it was marked as a duplicate of How to pivot unknown number of columns & no aggregate in SQL Server?, but that answer doesn't help me.

我有一个看起来像这样的数据表，其中的行和值的数量未知.

I have a table of data that looks like this, with an unknown number of rows and values.

RecID     Name      Value
1         Color     Red
2         Size      Small
3         Weight    20lbs
4         Shape     Square

我需要一个查询，该查询将返回这样的数据，为每一行建立一列.除了列标题名称"和值"之外，我无法进行任何硬编码.

I need a query that will return the data like this, building out a column for each row. I cannot hard code anything except the column headers 'Name' and 'Value'.

Color     Size     Weight     Shape
Red       Small    20lbs      Square

到目前为止，这是我目前正在部分工作的内容:

Here is what I have so far that is partly working:

INSERT INTO @Table VALUES
(1,'Color' ,'Red'),
(2,'Size'  ,'Small'),
(3,'Weight','20lbs'),
(4,'Shape' ,'Square')

 ;with mycte
 as
 (
SELECT rn,cols,val
FROM   (SELECT  row_number() over(order by Name) rn, Name, Value
        FROM  @Table) AS src1
UNPIVOT (val FOR cols
IN ( [Name], [Value])) AS unpvt
 )

SELECT *
FROM   (SELECT rn,cols,val
        FROM   mycte) AS src2 PIVOT
( Max(val) FOR rn IN ([1], [2], [3])) AS pvt

哪个返回:

cols    1   2   3
Name    Color   Shape   Size
Value   Red Square  Small

与此有关的两个问题我似乎无法解决.

Two problems with this that I can't seem to resolve.

1. 我不需要列标题和第一列具有cols，Name和Value的列.
2. 在不指定[x]标识符的情况下，无法弄清楚如何为每一行建立一列.

任何指导都是很棒的，我已经坚持了一段时间.

Any guidance would be great I've been stuck on this a while now.

推荐答案

declare @collist nvarchar(max)
SET @collist = stuff((select distinct ',' + QUOTENAME(name) 
            FROM #t -- your table here
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

declare @q nvarchar(max)
set @q = '
select * 
from (
    select rn, name, Value
        from (
        select *, row_number() over (partition by name order by RecID desc) as rn
        from #t -- your table here
    ) as x
) as source
pivot (
    max(Value)
    for name in (' + @collist + ')
) as pvt
'

exec (@q)

这篇关于SQL查询未知行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！