Replace row with Column Data in Sql Server

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

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