时间戳

首页 > 解决方案 > Show all columns when using pivot

sql - Show all columns when using pivot

问题描述

im working to build a dynamic table (header from table and cells from a table) this is my table

select rrc.Text , rch.Name
from RateColumnHeaders rch inner join
RateRowColumn rrc on rch.RateColumnHeadersId=rrc.RateColumnHeadersId

Text    Name
------------------------
test    Account Type
test2   Account Type
test3   Account Type
test4   Account Type
lorem1  Program
lorem2  Program
lorem3  Program
.           .
.           .
.           .

and after using pivot method i got that result but in mean time i need to show all the columns not only the max(text) how i can achieve that please ?

for me i want to show result like that

Account Type |  Program |.....
test         |  lorem1  
test2        |  lorem2  
test3        |  lorem3

. . . this is my pivot query

select * from (
select rrc.Disclaimer,rch.Name,rrc.Text 
from RateRowColumn rrc 
inner join RateColumnHeaders rch 
on rrc.RateColumnHeadersId=rch.RateColumnHeadersId
) s pivot ( max(text) for name in ( [Account Type],[Program]) ) pvt

标签： sqlsql-servervb.netpivot

解决方案

你不想要一个支点（我不认为）。我认为您只想将每个组中的值列为列表。

您可以使用条件聚合和row_number()：

select max(case when name = 'Account Type' then text end) as account_type,
       max(case when name = 'Program' then text end) as program,
       . . .
from (select rrc.Text, rch.Name,
             row_number() over (partition by rch.Name order by rrc.Text) as seqnum
      select rrc.Text, rch.Name
      from RateColumnHeaders rch inner join
           RateRowColumn rrc 
           on rch.RateColumnHeadersId = rrc.RateColumnHeadersId
     ) rrc
group by seqnum;

推荐阅读