Replace row with Column Data in Sql Server

Question

I have a table with some data and I was trying to Replace the Column with Row.

    SELECT Handset_UID, [IMEI_NO_1] AS [IMEI #1],[IMEI_NO_2] AS [IMEI #2],
    Isnull(Convert(varchar,Mfg_Date,106),'Not Found') as [Mfg. Date]        
    FROM  stock.Handset a
    join Stock.Model b
    on a.Model_ID = b.Model_ID
    JOIN Stock.Brand C
    ON A.Brand_ID = C.Brand_ID
    where a.Handset_UID = 'GFI13508637275434'

The above Query gives me the result in one single row data.

But I want the Result in below format

I have tried the Pivot Operator using Derived Column but got confused during implementation.

Please help to get the correct query.

Answer 1

Assuming that you are running SQL Server, as the syntax suggests, you can unpivot with cross apply:

select x.col, x.val
from stock.handset h
inner join stock.model m on h.model_id = m.model_id
inner join stock.brand b on on h.brand_id = b.brand_id
cross apply (values
    ('handset_uid', handset_uid),
    ('IMEI #1', imei_no_1),
    ('IMEI #2', imei_no_2),
    ('Mfg.Date', convert(varchar, Mfg_Date, 106), 'Not Found')
) x(col, val)
where h.handset_uid = 'gfi13508637275434'

Side notes:

• meaningful table aliases make the query easier to read and write

• your query has many columns that are not qualified with the alias of the table they belong to, which makes your query rather unclear about the underlying structure; I would strongly recommend that you qualify all columns in the query