locked
Microsoft SQL server - changing rows into columns RRS feed

  • Question

  • User-554278463 posted

    I need some help with turning some row data into columns.  My data from the ASSET table looks like this;

    ASSET_NO  LEVEL_NBR  ATTR_CODE                ATTR_ITEM_NARR

          1                   1             CLASSIFICATION       PLANT & EQUIP

          1                   2              CLASSIFICATION      OTHER EQUIP

          1                   3              CLASSIFICATION      P&E OTHER

          1                   1               LOCATION              UNI OF USC

          1                   2               LOCATION              BUILDING H

    So I am interested in getting the ATTR_ITEM_NARR information as columns but the columns are dependent on the LEVEL_NO and the ATTR_CODE

    What I want to see is

    ASSET_NO    LOCATION AT HIGHEST LEVEL     BLDG LOCATION             ASSET CATEGORY 1          ASSET CATEGORY 2

        1                         UNI OF USC                              BUILDING H             PLANT & EQUIPMENT               OTHER EQUIPMENT

    Can anyone help me to get to this point.  thanks.

    Wednesday, February 17, 2016 6:49 AM

Answers

  • User-62323503 posted
    declare @tab table(ASSET_NO int, LEVEL_NBR int,  ATTR_CODE varchar(50),  ATTR_ITEM_NARR varchar(50))
    insert into @tab values
    (1,1,'CLASSIFICATION','PLANT & EQUIP'),
    (1,2,'CLASSIFICATION','OTHER EQUIP'),
    (1,3,'CLASSIFICATION','P&E OTHER'),
    (1,1,'LOCATION','UNI OF USC'),
    (1,2,'LOCATION','BUILDING H')
    
    select ASSET_NO
    	, [LOCATION AT HIGHEST LEVEL]=max(case when LEVEL_NBR=1 and ATTR_CODE='LOCATION' then ATTR_ITEM_NARR end)
    	, [BLDG LOCATION]=max(case when LEVEL_NBR=2 and ATTR_CODE='LOCATION' then ATTR_ITEM_NARR end)
    	, [ASSET CATEGORY 1]=max(case when LEVEL_NBR=1 and ATTR_CODE='CLASSIFICATION' then ATTR_ITEM_NARR end)
    	, [ASSET CATEGORY 2]=max(case when LEVEL_NBR=2 and ATTR_CODE='CLASSIFICATION' then ATTR_ITEM_NARR end)
    from	@tab
    group by ASSET_NO

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 17, 2016 10:40 AM