Search code examples
sqlsql-server-2008unpivot

Change the name of columns after they became rows values in unpivot query SQLSERVER2008


I have table with the following information:

CREATE TABLE [dbo].[ApprovedPalmsInFarm](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [FarmID] [nvarchar](10) NOT NULL,
    [kalas] [int] NULL,
    [khnizi] [int] NULL

The default result of select query is:

id  FarmID      kalas   khnizi
4   12100462    5        0
5   11520344    3        0
6   12104277    150      15

I wanted to generate report using Unpivot query where the columns become row values

My report query is as the following:

SELECT FarmID, PalmsName, Quantity
FROM [ApprovedPalmsInFarm]
unpivot
(
Quantity
  FOR PalmsName in ([kalas] , [khnizi] )
) u;

The result of my Unpivot query is the following:

FarmID      PalmsName   Quantity
12100462    kalas        5
12100462    khnizi       0
11520344    kalas        3
11520344    khnizi       0
12104277    kalas        150
12104277    khnizi       15

Now my question is, how to change the name of the values of PalmsName while they are not understandable and they are the names of the columns so I would like to change the names for example to Arabic Language as the following:

خلاص instead of kalas

خنيزي instead of khnizi

Is it possible to change the name of the columns after they became row values?


Solution

  • I made a comment, but I'll put an answer down here so it formats better:

    SELECT 
        FarmID, 
        CASE PalmsName 
            WHEN 'kalas' THEN 'خلاص'
            WHEN 'khnizi' THEN 'خنيزي'
            ELSE PalmsName END AS 'PalmsName',
        Quantity
    FROM [ApprovedPalmsInFarm]
    unpivot
    (
    Quantity
      FOR PalmsName in ([kalas] , [khnizi] )
    ) u;