Need to pivot or crosstab a table but not in the conventional way. please

Question

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

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)

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.

See also

Poor Man's SQL Pivot.

See also

Sql Pivot Query with Dynamic Columns

Answer 1

You need poor man's pivot:

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

Static Columns

For example:

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

Example Data:

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

Example output:

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