CODEWITHSUNDEEP
sqlsql-servert-sqlpivotunpivot
Vishal Mishr
Vishal Mishr

Reputation: 207

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.

enter image description here

But I want the Result in below format

enter image description here

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

Please help to get the correct query.

Upvotes: 0

Views: 269

Answers (1)

GMB
GMB

Reputation: 222622

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

Upvotes: 1

Related Questions