将行数转换为Sql中的列格式

I have the following table:

我有以下表格:

VID RS
1   A
1   B
1   B
2   C
2   A

what I want to do is to calculate the count of each RS for each VID and want to have an output as follows:

我想做的是计算每个VID的每个RS的计数，并希望输出如下:

VID A   B   C
1   1   2   0
2   1   0   1

Is it possible to do through a query or I need to create temp table and perform insert/update on that? Thanks

是否可以通过查询完成，或者我需要创建临时表并对其执行插入/更新?谢谢

4 个解决方案

#1

If your number of RS is fixed then you can do

如果你的RS数是固定的，那么你可以这么做

select vid, 
       sum(case when RS = 'A' then 1 else 0 end) AS A,
       sum(case when RS = 'B' then 1 else 0 end) AS B,
       sum(case when RS = 'C' then 1 else 0 end) AS C
from your_table
group by vid

#2

You can do this in two ways

你可以用两种方法

One is use Pivot

一个是使用主

SELECT *
FROM   Yourtable
       PIVOT (Count(rs)
             FOR rs IN([A],
                       [B],
                       [C]) )piv

Note : If your RS column values are not static then convert the pivot to Dynamic Pivot

注意:如果RS列值不是静态的，那么将pivot转换为动态的pivot

Another way is use Conditional Aggregate

另一种方法是使用条件聚合

SELECT vid,
       Count(CASE Rs WHEN 'A' THEN 1 END) [A],
       Count(CASE Rs WHEN 'B' THEN 1 END) [B],
       Count(CASE Rs WHEN 'C' THEN 1 END) [C]
FROM   Yourtable
GROUP  BY vid 

SQLFIDDLE DEMO

#3

Check out the pivot command on msdn which will do just that.

检查msdn上的pivot命令，它就会这样做。

Update:

更新:

For this example:

对于这个示例:

SELECT Vid,
       [A] AS 'A',
       [B] AS 'B',
       [C] AS 'C'
FROM   your_table
       PIVOT (Count(RS)
             FOR RS IN( [A],
                        [B],
                        [C])) AS PivotTable; 

#4

If the values in RS is dynamic, you should chose dynamic pivot.

如果RS中的值是动态的，您应该选择dynamic pivot。

SAMPLE TABLE

样表

SELECT * INTO #TEMP
FROM
(
    SELECT 1 VID,   'A' RS
    UNION ALL
    SELECT 1,   'B'
    UNION ALL
    SELECT 1,   'B'
    UNION ALL
    SELECT 2,   'C'
    UNION ALL
    SELECT 2,   'A'
)TAB

QUERY

查询

Declare two variables to get columns for pivot and replace NULL with zero

声明两个变量来获取主元的列，并用0替换NULL

DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + RS + ']','[' + RS + ']')
               FROM    (SELECT DISTINCT RS FROM #TEMP) PV  
               ORDER BY RS

DECLARE @NullToZeroCols NVARCHAR (MAX)
SET @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+RS+'],0) AS ['+RS+']' 
FROM(SELECT DISTINCT RS FROM #TEMP GROUP BY RS)TAB  
ORDER BY RS  FOR XML PATH('')),2,8000)  

Now take the count and pivot

现在取计数和枢轴

DECLARE @query NVARCHAR(MAX)
SET @query = '
             SELECT VID,' + @NullToZeroCols + ' FROM 
             (
                 SELECT VID,RS,COUNT(RS)OVER(PARTITION BY VID,RS)CNT
                 FROM #TEMP
             ) x
             PIVOT 
             (
                 MIN(CNT)
                 FOR RS IN (' + @cols + ')
            ) p;' 

EXEC SP_EXECUTESQL @query

• SQL FIDDLE
• SQL小提琴