PIVOT with varchar datatype

pivot, sql, sql-server-2008

Solution

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

Problem

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 `varchar` columns. I have this table: And what I need is this: 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 ``` Can someone help me??. Thanks.

Original source