需要枢转或交叉处理表格，但不需要常规方式。请 [英] Need to pivot or crosstab a table but not in the conventional way. please

问题描述

我这里的情况很小..希望你们能帮帮我。

I have a small situation here.. hope you guys can help me out.

我应该查询一个有4列的表

帐户编号，资源编号，产品编号，客户编号。

几个帐户编号有2个资源编号（115和134）

I'm supposed to query a table wich has 4 columns
AccountNo, ResourceNo, ProductNo, CustomerNo.
A few accountNo's have 2 ResourceNo's (115 and 134)

我必须以如下方式查询它：必须显示两个动态列以表示 resourceNo 值，并在 accountNo上放置一个'X' 具有那些 ResourceNo 的。因此，不会重复 AccountNo 。在这种情况下，旋转无济于事。

I have to Query it in such a way that I have to show two dynamic columns for the resourceNo values and put an 'X' against the accountNo which has those ResourceNo's.. So that the AccountNo is not repeated.. Pivoting doesn't help in this situation. Please look into this and help me.

>穷人的SQL Pivot。

另请参见

包含动态列的Sql Pivot查询

推荐答案

您需要穷人的帮助：

• >穷人的SQL Pivot。在每一行中将问题列为每位用户的列和答案

• Poor Man's SQL Pivot. List Questions as Columns and Answers per User in one row

例如：

select 
    AccountNo,
    case when sum(case when ResourceNo = 134 then 1 else 0 end) = 0 then '' else 'X' end as Resource_134,
    case when sum(case when ResourceNo = 115 then 1 else 0 end) = 0 then '' else 'X' end as Resource_115
from 
    AccountResource 
group by
    AccountNo

示例数据：

AccountNo ResourceNo ProductNo CustomerNo
A1        134        P1         C1
A1        134        P2         C1
A1        134        P3         C2
A2        134        P1         C1
A2        115        P1         C4
A2        115        P2         C1
A3        115        P5         C2

示例输出：

AccountNo Resource_134 Resource_115
A1        X
A2        X            X
A3                     X

动态列

对于动态列，您可以执行以下操作：

Dynamic Columns

For dynamic columns you can do this:

declare @sql nvarchar(max)
set @sql = ''
select @sql = @sql + ',' + char(13)+char(10)+
'case when sum(case when ResourceNo = ''' + replace(cast(ResourceNo as nvarchar(10)), '''', '''''') + ''' then 1 else 0 end) = 0 then '''' else ''X'' end as "Resource_' + replace(cast(ResourceNo as nvarchar(10)), '"', '""') + '"'
from AccountResource 
group by ResourceNo
order by Resourceno

set @sql = 'select AccountNo' + @sql
+ char(13)+char(10)
+ 'From AccountResource '
+ char(13)+char(10)
+ 'group by AccountNo'
+ char(13)+char(10)
+ 'order by AccountNo'
select * from datacheck.dbo.splitmax(@sql, null,null)

exec sp_executesql @sql

这篇关于需要枢转或交叉处理表格，但不需要常规方式。请的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！