Give Alias to Query columns to rows Query

Other SQL Server Topics
1

Hi Experts !
The query below works fine for columns to rows , I wonder how to give the alias to column names because I need to create a view for below query . I tried to create view but find below error

Msg 4506, Level 16, State 1, Procedure test_view, Line 2
Column names in each view or function must be unique. Column name 'ITEM_NAME' in view or function 'test_view' is specified more than once.


SELECT    [START_DATETIME],
    [ITEM_NAME]  = MAX(CASE WHEN [ITEM_NAME] = 'test1' THEN [ITEM_NAME]  END),
    [T_NAME] = MAX(CASE WHEN [ITEM_NAME] = 'test1' THEN [T_NAME] END)
,
    [ITEM_NAME]  = MAX(CASE WHEN [ITEM_NAME] = 'test2' THEN [ITEM_NAME]  END),
    [T_NAME] = MAX(CASE WHEN [ITEM_NAME] = 'test2' THEN [T_NAME] END)

 FROM    [DRM_REPORTII_STOCK_VIEW]
GROUP BY     [START_DATETIME]
2

Your column 2 and 4 are both called ITEM_NAME and column 3 and 5 are both called T_NAME.

A quick fix could be:

SELECT [START_DATETIME]
      ,[ITEM_NAME1] = MAX(CASE WHEN [ITEM_NAME] = 'test1' THEN [ITEM_NAME]  END)
      ,[T_NAME1]    = MAX(CASE WHEN [ITEM_NAME] = 'test1' THEN [T_NAME] END)
      ,[ITEM_NAME2] = MAX(CASE WHEN [ITEM_NAME] = 'test2' THEN [ITEM_NAME]  END)
      ,[T_NAME2]    = MAX(CASE WHEN [ITEM_NAME] = 'test2' THEN [T_NAME] END)
  FROM [DRM_REPORTII_STOCK_VIEW]
 GROUP BY [START_DATETIME]
1 Like
3

Dear bitsmed,
Many thanks worked like a charm !