Converting row data to columns with conditional data

Transact-SQL
1

I need to convert some rows to columns with conditions attached. I am unsure how to do this? Also, the data is coming from 2 different tables ie: the ASSET table and the ATTRIBUTES table so I need to join the tables in the script. Can anyone help please?

SQL data rows columns.JPG1316×270 50.9 KB

2

can you post the DDL for both the tables ?

3

Hi there, sorry I am not a programmer so can you explain in layman's terms what I need to give you? :-\

4

Can you post the what are the columns in your ASSET and ATTRIBUTES table

5

the column names appear at line two in the data supplied - ASSET TABLE - ASSET_NO
ATTRIBUTES TABLE: LEVEL_NBR, ATTR_CODE and ATTR_ITEM_NARR.

6

Which column are from which table ? in your first post you mention "the data is coming from 2 different tables"

7

just clarified that in my previous post. All columns come from ATTRIBUTES table except for ASSET No. which comes from the ASSET table.

8

and how do you join these tables ?

The result that you expected will required the use of Dynamic SQL. I need to know which table contains which columns in order to provide a query.

9

I have simplified the data above as there are a few tables involved because of the ASSET_NO not being in the ATTRIBUTES table. Here is my current script which includes other tables. Hope this makes sense.

select * from dbo.F1ASR_REG_ASSET
LEFT JOIN dbo.F1ASR_ASSET_BOOK
ON dbo.F1ASR_REG_ASSET.ASSNBRI=dbo.F1ASR_ASSET_BOOK.ASSNBRI
AND dbo.F1ASR_REG_ASSET.REG_NAME=dbo.F1ASR_ASSET_BOOK.REG_NAME
LEFT JOIN dbo.F1ASR_REG_ASSET_ATTR
ON dbo.F1ASR_ASSET_BOOK.ASSNBRI=dbo.F1ASR_REG_ASSET_ATTR.ASSNBRI
AND dbo.F1ASR_REG_ASSET.REG_NAME=dbo.F1ASR_REG_ASSET_ATTR.REG_NAME
LEFT JOIN dbo.F1ASS_ATTR_ITEM
ON dbo.F1ASS_ATTR_ITEM.ITEM_UNIQUE_ID=dbo.F1ASR_REG_ASSET_ATTR.ATTR_UNIQUE_ID

10 
-- create table variable 
declare    @code_level table
(
    ATTR_CODE    varchar(30),
    LEVEL_NBR    int
)

-- Get All combinition of ATTR_CODE & LEVEL_NBR
insert into @code_level ( ATTR_CODE, LEVEL_NBR )
select    distinct ATTR_CODE, LEVEL_NBR
from    ATTRIBUTE

select    *
from    @code_level

declare    @sql    nvarchar(max)

select    @sql    = N'SELECT a.ASSET_NO' + char(13)

select    @sql    = @sql + ','
        + 'MAX ( CASE WHEN b.ATTR_CODE = ''' + ATTR_CODE + ''' AND b.LEVEL_NBR = ' + CONVERT ( VARCHAR(10), LEVEL_NBR ) + 'THEN ATTR_ITEM_NARR END)' + char(13)
from    @code_level
order by ATTR_CODE, LEVEL_NBR

select    @sql    = @sql 
        + N'FROM    ASSET a '
        + N'INNER JOIN ATTRIBUTE b    on    a.ASSET_NO    = b.ASSET_NO '
        + N'GROUP BY a.ASSET_NO '

print    @sql
/* result of print
SELECT a.ASSET_NO
,MAX ( CASE WHEN b.ATTR_CODE = 'CLASSIFICATION' AND b.LEVEL_NBR = 1THEN ATTR_ITEM_NARR END)
,MAX ( CASE WHEN b.ATTR_CODE = 'CLASSIFICATION' AND b.LEVEL_NBR = 2THEN ATTR_ITEM_NARR END)
,MAX ( CASE WHEN b.ATTR_CODE = 'CLASSIFICATION' AND b.LEVEL_NBR = 3THEN ATTR_ITEM_NARR END)
,MAX ( CASE WHEN b.ATTR_CODE = 'LOCATION' AND b.LEVEL_NBR = 1THEN ATTR_ITEM_NARR END)
,MAX ( CASE WHEN b.ATTR_CODE = 'LOCATION' AND b.LEVEL_NBR = 2THEN ATTR_ITEM_NARR END)
,MAX ( CASE WHEN b.ATTR_CODE = 'LOCATION' AND b.LEVEL_NBR = 3THEN ATTR_ITEM_NARR END)
,MAX ( CASE WHEN b.ATTR_CODE = 'ORGANISATION' AND b.LEVEL_NBR = 1THEN ATTR_ITEM_NARR END)
,MAX ( CASE WHEN b.ATTR_CODE = 'ORGANISATION' AND b.LEVEL_NBR = 2THEN ATTR_ITEM_NARR END)
FROM    ASSET a INNER JOIN ATTRIBUTE b    on    a.ASSET_NO    = b.ASSET_NO GROUP BY a.ASSET_NO 

*/

exec    sp_executesql @sql
11

Thanks so much!!!! This gave me exactly what I wanted. Much appreciated. :smiley::dizzy: