Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 568
amal50 Avatar asked May 06 '15 21:05

amal50


People also ask

How do I change a column name in query?

To change a column name, enter the following statement in your MySQL shell: ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name; Replace table_name , old_column_name , and new_column_name with your table and column names.

How do I Unpivot multiple columns?

Select the columns you do want to unpivot. To select more than one column contiguously or discontiguously, press Shift+Click or CTRL+Click on each subsequent column. Select Transform > Unpivot Only Selected Columns.


1 Answers

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;
like image 186
Jim Avatar answered Oct 02 '22 18:10

Jim