Pivot with Varchar Datatype

PIVOT with varchar datatype

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

SQL PIVOT with varchar datatype returning null

You're constructing the intermediate table with hdg taking the format University Issuing Body<n>. But you're then pulling out values where hdg has the format University Issuing Body_<n>. Unsurprisingly, this doesn't match anything - so you get NULLs.

Just put an underscore in your construction of hdg:

select
 [Sequence]
,[University Issuing Body_1]
,[University Issuing Body_2]
,[University Issuing Body_3]
from (
    select
        [Sequence]
        ,[University Issuing Body]
        ,'University Issuing Body_' + cast(row_number() over(partition by [Sequence] order by [University Issuing Body]) as varchar(12)) as hdg
    from [AB_DCU_IP_2018].[dbo].[PR_Q_Joined]
    ) d
    pivot(
        max([University Issuing Body])
        for hdg in ([University Issuing Body_1], [University Issuing Body_2], [University Issuing Body_3])
        ) pvt

SQL Pivot on varchar columns

Assuming the flight codes actually match, then use conditional aggregation:

select flightcode,
       max(case when col3 = 'SOURCE' then col2 end) as source,
       max(case when col3 = 'TARGET' then col2 end) as target,
       max(SourceAirportCode) as SourceAirportCode
from t
group by flightcode;

Where to do the CASTING in SQL PIVOT Expression

Do the conversion in the inner query itself

SELECT PartyRelationshipID,
       [2] AS OrderGroup,
       [3] AS TaxStatus,
       [4] AS Area
FROM   (SELECT PartyRelationshipID,
               PartyRelationshipSettingTypeID,
               Value = Cast(Value AS INT) --here
        FROM   [Party].PartyRelationshipSetting prs) AS SourceTable
       INNER JOIN Party.PartyRelationship prship
               ON SourceTable.PartyRelationshipID = prship.ID
PIVOT(SUM(Value) FOR PartyRelationshipSettingTypeID IN ([2],[3],[4]))AS PivotTable

How to use string type column in SQL pivot

If your dbms is sql-server you can try to use SUM condition aggregate function in a CTE

then use CAST with coalesce to make it.

;WITH CTE AS(
    SELECT Year,Name,
           SUM(CASE WHEN Bonus LIKE '%[0-9]%' THEN  CAST(Bonus AS DECIMAL) ELSE 0 END) total,
           COUNT(CASE WHEN Bonus = 'No Bonus' THEN 1 END) cnt
    FROM T 
    GROUP BY Year,Name
)
SELECT Name,
       coalesce(MAX(CASE WHEN  Year = 2011 THEN CAST(total AS VARCHAR(50)) END),'No Bonus') '2011',
       coalesce(MAX(CASE WHEN  Year = 2012 THEN CAST(total AS VARCHAR(50)) END),'No Bonus') '2012'
FROM CTE
GROUP BY Name

sqlfiddle

If you want to create columns dynamically you can try to use dynamic PIVOT.

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

;WITH CTE AS(
    SELECT Year,Name,
           SUM(CASE WHEN Bonus LIKE '%[0-9]%' THEN  CAST(Bonus AS DECIMAL) ELSE 0 END) total,
           COUNT(CASE WHEN Bonus = 'No Bonus' THEN 1 END) cnt
    FROM T 
    GROUP BY Year,Name
)
SELECT  @cols = STUFF((SELECT distinct ',coalesce(MAX(CASE WHEN cnt > 0 and Year = ' + cast(Year as varchar(5)) + ' THEN ''No Bonus''  WHEN Year = ' + cast(Year as varchar(5)) + ' and cnt = 0 THEN CAST(total AS VARCHAR(50)) END),''0'')' + QUOTENAME(Year)
            FROM CTE c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = '
;WITH CTE AS(
    SELECT Year,Name,
           SUM(CASE WHEN Bonus LIKE ''%[0-9]%'' THEN  CAST(Bonus AS DECIMAL) ELSE 0 END) total,
           COUNT(CASE WHEN Bonus = ''No Bonus'' THEN 1 END) cnt
    FROM T 
    GROUP BY Year,Name
)
SELECT Name, ' + @cols + ' 
from CTE
GROUP BY Name'

exec(@query) 

sqlfiddle

MySQL pivoting with VARCHAR

To do this you can use equivalent of ROW_NUMBER and GROUP BY calculated RowNumber column:

SELECT 
    MAX(CASE WHEN Role = 'Admin' THEN Name END) AS `Admin`, 
    MAX(CASE WHEN Role = 'Moderator' THEN Name END) AS `Moderator`, 
    MAX(CASE WHEN Role = 'User' THEN Name END) AS `User`
FROM (
      SELECT *
        ,@row_num := IF(@prev_value=concat_ws('',t.Role),@row_num+1,1) AS RowNumber
        ,@prev_value := concat_ws('',t.Role)  
      FROM Organization t,
         (SELECT @row_num := 1) x,
         (SELECT @prev_value := '') y
      ORDER BY t.Role   
     ) AS sub
GROUP BY RowNumber

SqlFiddleDemo

Output:

╔═════════╦════════════╦══════╗
║ Admin   ║ Moderator  ║ User ║
╠═════════╬════════════╬══════╣
║ Tony    ║ (null)     ║ Sara ║
║ (null)  ║ (null)     ║ John ║
╚═════════╩════════════╩══════╝

---

Related Topics