具有varchar数据类型的PIVOT

本文介绍了具有varchar数据类型的PIVOT的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试在表中添加一些数据，但是我无法做到这一点，因为我找不到使用charchar列进行数据处理的方法.我有这张桌子:

I´m trying to PIVOT some data in a table, but I cannot do it because I do not find the way to do it using carchar columns. I have this table:

我需要的是这个

我需要将"ug_label"行数据用作列.由于数据类型为VARCHAR，因此我无法在PIVOT中使用聚合函数.

I need to use the 'ug_label' row data as columns. As the datatype is VARCHAR, I cannot use an agregate function inside the PIVOT.

我想我可能需要这样的东西:

I think I might need something like this:

SELECT * FROM (SELECT [c_id] ,[c_lname] as [Apellido] ,[c_fname] as [Nombre] ,[c_nick_name] as [documento] ,[ut_text] ,f.ug_label FROM [pegasys].[dbo].[cardholder] c inner join [pegasys].[dbo].[udftext] u on c.c_id = u.ut_cardholder_id inner join [pegasys].[dbo].[udfgen] f on u.ut_udfgen_id = f.ug_id) AS S PIVOT ( UT_TEXT FOR [UG_LABEL] IN ([Torre], [Cuit], [Empresa], [Departamento]) ) as s

有人可以帮我吗?

谢谢.

推荐答案

您仍然可以使用PIVOT函数获取结果，但是由于要聚集varchar，因此必须使用max或min:

You can still use the PIVOT function to get the result but since you are aggregating a varchar you have to use either max or min:

SELECT * FROM ( SELECT [c_id] ,[c_lname] as [Apellido] ,[c_fname] as [Nombre] ,[c_nick_name] as [documento] ,[ut_text] ,f.ug_label FROM [pegasys].[dbo].[cardholder] c inner join [pegasys].[dbo].[udftext] u on c.c_id = u.ut_cardholder_id inner join [pegasys].[dbo].[udfgen] f on u.ut_udfgen_id = f.ug_id ) d PIVOT ( max(ut_text) FOR UG_LABEL IN ([Torre], [Cuit], [Empresa], [Departamento]) ) p