By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,694 Members | 1,882 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,694 IT Pros & Developers. It's quick & easy.

Split multirow resultset to single row, multicolumn resultset

P: 1
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
Mar 28 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.