主页 / dba / 问题 / 23232
Accepted
Vaccano
Asked: 2012-08-28 14:36:36 +0800 CST

如何将查询结果转换成一组普通结果

  • 772

我有一个查询返回这样的结果：

     MyValue        MyGroup
_______________|_______________
       56      |      I
_______________|_______________
       12      |      I
_______________|_______________
       56      |      II
_______________|_______________
       12      |      II
_______________|_______________
       56      |      III
_______________|_______________
       56      |      IV
_______________|_______________
       56      |      V
_______________|_______________
       12      |      V
_______________|_______________

我需要把它转换成这样的东西：

      ColA           ColB             ColC            ColD           Count
_______________|_______________|_______________|_______________|_______________
       12      |      56              null            null             3
_______________|_______________|_______________|_______________|_______________
       56      |      null            null            null             2
_______________|_______________|_______________|_______________|_______________

我知道我可以设置一个迭代器并遍历我的表的所有行并构造这个结果。

但我认为还有另一种“基于集合”的方式。也许是“交叉应用”或“枢轴”。

但无论我阅读文档或查看这些文档的示例多少次，我似乎都无法充分理解它们来编写自己的文档。

有人有建议吗？

注意：以防万一，假设我获取第一个结果的查询如下所示：

select MyValue, MyGroup
from SomeTable

（它实际上是一组复杂的 CTE，但如果需要我可以添加另一层。）

sql-server pivot

3 个回答

  1. Best Answer

    似乎更简单的方法：

    SELECT GroupValue = Val, [Count] = COUNT(DISTINCT MyGroup) FROM 
(
  SELECT MyGroup, Val = STUFF((SELECT ', ' + RTRIM(MyValue) 
   FROM dbo.MyTable WHERE MyGroup = t.MyGroup FOR XML PATH('')), 1, 2, '')
  FROM dbo.MyTable AS t
) AS x GROUP BY Val;
    • 1 
  2. DECLARE @columns VARCHAR(8000)

SELECT @columns = COALESCE(@columns + ',[' + Mygroup + ']',
'[' + Mygroup+ ']')
FROM MyTable
GROUP BY Mygroup


DECLARE @query VARCHAR(8000)

SET @query = '
SELECT *
FROM (  select MyGroup,
        MyValue, 
        Row_number() over ( partition by Mygroup 
                            Order by Mygroup) as rownum 
        from Mytable) a         
PIVOT
(
MAX(MyValue)
FOR [MyGroup]
IN (' + @columns + ')
)
AS p'

EXECUTE(@query)
    • 0

  3. 这就是我要找的东西：

    -- Setup the table
create table dbo.MyTable(MyValue int, MyGroup varchar(5))
insert into dbo.MyTable values
(56,'I'), (12,'I'), (56, 'II'), (12, 'II'), (56, 'III'), (56, 'IV'), (56, 'V'), (12, 'V')

-- Create a function to cross apply with
create function dbo.GetOrderGroup(@groupName varchar(15))
returns @ReturnTable table(GroupValue varchar(8000), GroupName varchar(15))
as                
begin
    DECLARE @groupValue VARCHAR(8000)

    SELECT @groupValue = COALESCE(@groupValue + ', ' 
           + cast(MyValue as VARCHAR(10)), cast(MyValue as VARCHAR(10)))
    FROM MyTable
    where MyGroup = @groupName

    insert into @ReturnTable(GroupValue, GroupName)
    SELECT @groupValue, @groupName 
    return;
end;
go

-- cross apply and get the distinct values
SELECT  grp.GroupValue, count(distinct (grp.GroupValue + grp.GroupName)) as 'Count'
from    MyTable tbl (NOLOCK)
        cross apply dbo.GetOrderGroup(tbl.MyGroup) grp
GROUP BY grp.GroupValue

    它返回：

       GroupValue         Count
_________________|______________
    12, 56       |      3
_________________|______________
      56         |      2
_________________|______________

    这足够接近我所需要的。

    • 0

相关问题