具有varchar数据类型的PIVOT [英] PIVOT with varchar datatype
本文介绍了具有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
这篇关于具有varchar数据类型的PIVOT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文