SQL数据透视表
我有以下数据的SQL视图:SQL数据透视表
ID ClassName Description Flags
1 Class1 Desc1 F1
2 Class1 Desc1 F2
3 Class1 Desc1 F3
4 Class1 Desc1 F4
5 Class2 Desc2 F2
6 Class2 Desc2 F6
7 Class3 Desc3 F1
8 Class4 Desc4 F8
我想结果是这样的:
ClassName Description F1 F2 F3 F4 F6 F8
Class1 Desc1 T T T T F F
Class2 Desc2 F T F F T F
Class3 Desc3 T F F F F F
Class4 Desc4 F F F F F T
我试图来查找枢纽的例子,但所有的人都用于SUM或其他聚合函数。不知道是否会有任何工作,因为我没有做。
当flags ='F1'然后'T'else'F'结束F1时,case flags when'F2'then'T'else'F'end F2,...
我相信MS SQL Server支持枢轴表,但我不确定如何去做。当我必须在MySQL中传输数据时,我使用Sum和Case。但是,只有当你知道你的列名将会提前时才有效。以下是我可以做到这一点:
Select
X.ClassName,
X.Description,
Case When X.F1 = 1 Then 'T' Else 'F' End As `F1`,
Case When X.F2 = 1 Then 'T' Else 'F' End As `F2`
/* etc. for the rest of your Flags*/
FROM (
Select
ClassName,
Description,
Sum(Case When Flags = 'F1' Then 1 Else 0 End) As `F1`,
Sum(Case When Flags = 'F2' Then 1 Else 0 End) As `F2`
/* etc. for the rest of your Flags*/
From
ClassTable
Group By
ClassTable.ClassName
) X
在上面的代码中,子查询会产生输出像你想要的东西,但你会得到1和0(假设你永不重复标志的一类)。声明顶部的“主要”查询只是将1和0转换为T和F。
同样,这需要你知道你的列名是什么,但这是我知道如何在没有将“PIVOT”内置到你正在使用的SQL语言中的唯一途径。 MS SQL可能有一个内置的PIVOT,所以你可能想挖掘一下。
我不知道我的标志是什么,因为最终用户有能力添加,更新或删除标志并与他们想要的任何类进行关联(例如,用户可以添加新标志,如F101或将现有标志F1重命名为F01)。我使用的是SQL Express 2005 – Deepak 2009-11-03 19:59:47
下面是一个令人讨厌的黑客:如果您有能力在运行时生成SQL,则可以在表中查询所有可用的标志名称,然后使用列生成SQL语句(Sum(Case When Flags ...)会自动从代码中生成,这让我觉得自己很肮脏,但是如果你有代码可以工作,它就会工作。 – 2009-11-03 20:06:46
Henry Fao走在正确的轨道上 - 但是你必须用一组来平整行。
select ClassName as ClassName, Description as Description , coalesce(max(F1), 'F') as F1 , coalesce(max(F2), 'F') as F2 , etc from ( select classname, description , case when flags = 'F1' then 'T' else null end as F1 , case when flags = 'F2' then 'T' else null end as F2 etc. from tbl ) as t group by ClassName, Description
在每个组的F1列中应该只有一个'T'。其他行将在T1列中为空。 max()函数忽略空值并返回'T' - 如果有的话。
这不是很漂亮,我通常不喜欢动态创建的SQL,但有一个未知的标志列表,我不确定你会怎么做。此外,你是正确的,因为PIVOT命令期望一个聚合,所以我只用MAX。是的,这是一种黑客行为,但它确实完成了工作,并且会随着新标志的添加而扩展。我认为这应该适用于2005/2008版SQL-SERVER。我不知道2003年,因为我不知道它是否有XML PATH命令。
Declare @ColumnsIn varchar(max)
Declare @ColumnsIsNull varchar(max)
Declare @sql varchar(max)
Select @ColumnsIn = Stuff((Select Distinct ',[' + Flags + ']' From Classes For XML PATH('')),1,1,'')
Select @ColumnsIsNull = Stuff((Select Distinct ', IsNull([' + Flags + '], ''F'') as [' + Flags + ']' From Classes For XML PATH('')),1,1,'')
Set @sql = '
Select
ClassName,
Description,
' + @ColumnsIsNull + '
FROM
(
Select
Classes.ClassName,
Classes.Description,
Classes.Flags,
''T'' as HasFlag
From
Classes
) as Sub1
Pivot (Max(HasFlag) For Flags in (' + @ColumnsIn + ')) as Sub2'
Execute(@sql)
什么版本的SQL Server,你试过了什么? – 2009-11-03 19:32:03