Hello All
I want to Split multirow resultset to single row, multicolumn resultset
my table is
ProfileDetail(ProfileId bigint, PropertyId bigint PropertyValue nvarchar(400))
Table data :
ProfileId PropertyId PropertyValue
97 1 lokendra
97 2 bhatt
97 3 m
97 4 Oct 23 1987 12:00AM
97 5 2344
97 6 535324
97 7 1
97 8 1
97 9 xyz
97 10 0
I put a Query to retrive data from multiple row to single row :
select b.PropertyValue as FName,
(select b.PropertyValue from (select ProfileId,PropertyValue,Row_Number() over( order by ProfileDetail.ProfileId ) as RowId from ProfileDetail (Nolock)) b where b.rowid=2 ) as Lname ,
(select b.PropertyValue from (select ProfileId,PropertyValue,Row_Number() over( order by ProfileDetail.ProfileId ) as RowId from ProfileDetail (Nolock)) b where b.rowid=3 ) as Gender ,
(select b.PropertyValue from (select ProfileId,PropertyValue,Row_Number() over( order by ProfileDetail.ProfileId ) as RowId from ProfileDetail (Nolock)) b where b.rowid=4 ) as BDate ,
(select b.PropertyValue from (select ProfileId,PropertyValue,Row_Number() over( order by ProfileDetail.ProfileId ) as RowId from ProfileDetail (Nolock)) b where b.rowid=5 ) as Zip ,
(select b.PropertyValue from (select ProfileId,PropertyValue,Row_Number() over( order by ProfileDetail.ProfileId ) as RowId from ProfileDetail (Nolock)) b where b.rowid=6 ) as Phone ,
(select b.PropertyValue from (select ProfileId,PropertyValue,Row_Number() over( order by ProfileDetail.ProfileId ) as RowId from ProfileDetail (Nolock)) b where b.rowid=7 ) as GMTZoneId
from (select ProfileId,PropertyValue,Row_Number() over( order by ProfileDetail.ProfileId ) as RowId
from ProfileDetail (Nolock) where ProfileID=97) b where RowId=1
it return the required result
ouput :
Fname Lname Gender Bdate Zip Phone GmtZoneId
hdg bhatt m Oct 23 1987 535324 1
Question : What I need to tune the above query so that it can give me faster result or any idea to get the same result set with different query
Thnks
Lokendra