按第一列分组，并拆分（pivot？）剩余两列

TSQL question here. See source and desired output in image below. Code to build source table is also provided.

这里有TSQL问题。请参阅下图中的源和所需输出。还提供了构建源表的代码。

---

DECLARE @tablevar TABLE(
record nvarchar(10),
category nvarchar(50),
value float)

INSERT INTO @tablevar
VALUES
('110-AL','credits_cle',1),
('110-AL','credits_ethics',2),
('110-AR','credits_ethics',2.5),
('110-AZ','credits_prof_resp',1.5),
('110-AZ', 'credits_ethics',5),
('110-AZ', 'credits_cle',4)

3 个解决方案

#1

Since you want to PIVOT two columns of data, one way you can do this would be to apply both the UNPIVOT and the PIVOT functions. The UNPIVOT will convert the multiple columns category and value to multiple rows, then you can apply the PIVOT to get the final result:

由于你想要PIVOT两列数据，你可以这样做的一种方法是同时应用UNPIVOT和PIVOT函数。 UNPIVOT会将多列类别和值转换为多行，然后您可以应用PIVOT来获得最终结果：

select record, 
  category1, value1, 
  category2, value2, 
  category3, value3
from
(
  select record, col+cast(seq as varchar(10)) col, val
  from
  (
    select record, category, 
      cast(value as nvarchar(50)) value,
      row_number() over(partition by record order by category) seq
    from tablevar
  ) d
  unpivot
  (
    val
    for col in (category, value)
  ) unpiv
) src
pivot
(
  max(val)
  for col in (category1, value1, category2, value2, category3, value3)
) piv;

See SQL Fiddle with Demo.

请参阅SQL Fiddle with Demo。

If you have an unknown number of values, then you will have to use dynamic SQL similar to this:

如果您具有未知数量的值，则必须使用与此类似的动态SQL：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(seq as varchar(10))) 
                    from
                    (
                      select row_number() over(partition by record order by category) seq
                      from tablevar
                    ) d
                    cross apply
                    (
                      select 'category', 1 union all
                      select 'value', 2
                    ) c (col, so)
                    group by seq, so, col
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT record,' + @cols + ' 
             from 
             (
               select record, col+cast(seq as varchar(10)) col, val
                from
                (
                  select record, category, 
                    cast(value as nvarchar(50)) value,
                    row_number() over(partition by record order by category) seq
                  from tablevar
                ) d
                unpivot
                (
                  val
                  for col in (category, value)
                ) unpiv
            ) x
            pivot 
            (
                max(val)
                for col in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

#2

This being the first time I've used PIVOT, the code is probably pretty ugly. Here goes:

这是我第一次使用PIVOT，代码可能非常难看。开始：

with ranked as (
    select *, RANK() OVER (PARTITION by record ORDER by category) as r
    from @tablevar
), labeled as (
    select record, category as content, 'category' + CAST(r as varchar(MAX)) as label
     from ranked
    union all
    select record, cast(value AS nvarchar(MAX)),  'value' + CAST(r as varchar(MAX)) as label
     from ranked) --select * from labeled
select record, [category1] as [category], [value1] as [value], [category2] as [category], [value2] as [value], [category3] as [category], [value3] as [value]
from (SELECT * FROM labeled) as source
PIVOT(
    max(content)
    for label in ([category1], [value1], [category2], [value2], [category3], [value3])) as pvt

#3

Here's my stab at it

这是我对它的刺痛

;with Z as
(
select record, category, value,  ROW_NUMBER() over (partition by record order by category) as ranker
from @tablevar
) 
select Z2.record, Z2.c1, Z3.v1, Z2.c2, Z3.v2, Z2.c3, Z3.v3 from 
(
select  record, [1] c1, [2] c2, [3] c3 from 
(select record, category, ranker from Z) as Z0
pivot
( min(category) for ranker in ([1], [2], [3])) as pvt
) Z2

join
(
select  record, [1] v1, [2] v2, [3] v3 from 
(select record, value, ranker from Z) as Z1
pivot
( min(value) for ranker in ([1], [2], [3])) as pvt
) Z3 
on Z2.record = Z3.record