Kategorien
Allgemeines

MS SQL PIVOT with text as Fieldtype

WITH PivotData AS
(
SELECT
       ItemCodeBase.ItemCode AS ItemCode
	  ,CAST(ITEMTEXT.[FieldAlias] as VARCHAR(MAX)) AS FieldAlias
	  ,CAST(MUTLITEXT.Trans as VARCHAR(MAX)) AS Trans     
  FROM DWHLN_TEST.dbo.ItemsTextCheckCreh as ItemCodeBase
	  inner join SAP002.SAP_Master.dbo.OMLT AS ITEMTEXT on ITEMTEXT.PK = ItemCodeBase.ItemCode  COLLATE DATABASE_DEFAULT
	  INNER JOIN SAP002.SAP_Master.dbo.MLT1 AS MUTLITEXT ON ITEMTEXT.TranEntry = MUTLITEXT.TranEntry
  where 
	1=1
    AND MUTLITEXT.LangCode = '9' and FieldAlias in ('U_COR_BU_TXTS','U_COR_BU_TXTQ')
)

SELECT 
	ItemCode
	,[U_COR_BU_TXTS]
	,[U_COR_BU_TXTQ]
FROM
	PivotData
	PIVOT(
		MAX (Trans)
		FOR FieldAlias in ([U_COR_BU_TXTS],[U_COR_BU_TXTQ])) AS P;

The trick is, that you cast the text/string into varchar and then you can use MAX as aggregation type to return the readable value.

Schreibe einen Kommentar