当列为字符串数据类型时，使用mssql中的“数据透视表"将行转换为列 [英] Convert Rows to columns using 'Pivot' in mssql when columns are string data type

问题描述

我需要知道，如果没有聚合函数要使用，是否可以使用MS SQL中的数据透视表"将行转换为列.我看到了很多仅带有聚合函数的示例.我的字段是字符串数据类型，我需要将此行数据转换为列数据.这就是为什么我写这个问题.我只是用'case'做到了.谁能帮我......提前感谢.

I need to know whether 'pivot' in MS SQL can be used for converting rows to columns if there is no aggregate function to be used. i saw lot of examples with aggregate function only. my fields are string data type and i need to convert this row data to column data.This is why i wrote this question.i just did it with 'case'. Can anyone help me......Thanks in advance.

推荐答案

您可以使用

You can use a PIVOT to perform this operation. When doing the PIVOT you can do it one of two ways, with a Static Pivot that you will code the rows to transform or a Dynamic Pivot which will create the list of columns at run-time:

静态数据透视(请参见带有演示的SQL提琴):

Static Pivot (see SQL Fiddle with a Demo):

SELECT *
FROM
(
  select empid, wagecode, amount
  from t1
) x
pivot
(
  sum(amount)
  for wagecode in ([basic], [TA], [DA])
) p

动态枢轴:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(wagecode) 
                  FROM t1 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT empid, ' + @cols + ' from 
             (
                 select empid, wagecode, amount
                 from t1
            ) x
            pivot 
            (
                sum(amount)
                for wagecode in (' + @cols + ')
            ) p '

execute(@query)

这两种方法都能为您带来相同的结果

Both of these will give you the same results

这篇关于当列为字符串数据类型时，使用mssql中的“数据透视表"将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！