Split multirow resultset to single row, multicolum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Split multirow resultset to single row, multicolum

Author

Topic

loken.bhatt
Starting Member

5 Posts

Posted - 2007-03-28 : 02:52:58

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 53532 454 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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 03:02:32

[code]-- Prepare sample data
DECLARE @ProfileDetail TABLE (ProfileID INT, PropertyID INT, PropertyValue VARCHAR(40))

INSERT @ProfileDetail
SELECT 97, 1, 'lokendra' UNION ALL
SELECT 97, 2, 'bhatt' UNION ALL
SELECT 97, 3, 'm' UNION ALL
SELECT 97, 4, 'Oct 23 1987 12:00AM' UNION ALL
SELECT 97, 5, '2344' UNION ALL
SELECT 97, 6, '535324' UNION ALL
SELECT 97, 7, '1' UNION ALL
SELECT 97, 8, '1' UNION ALL
SELECT 97, 9, 'xyz' UNION ALL
SELECT 97, 10, '0' UNION ALL
SELECT 197, 1, 'Peter' UNION ALL
SELECT 197, 2, 'Larsson' UNION ALL
SELECT 197, 3, 'm' UNION ALL
SELECT 197, 4, 'Last century' UNION ALL
SELECT 197, 5, '25462' UNION ALL
SELECT 197, 6, 'Unlisted' UNION ALL
SELECT 197, 7, '2' UNION ALL
SELECT 197, 8, '9' UNION ALL
SELECT 197, 9, 'xyz' UNION ALL
SELECT 197, 10, 'f'

-- Show the expected output
SELECT MAX(CASE WHEN PropertyID = 1 THEN PropertyValue END) AS FName,
MAX(CASE WHEN PropertyID = 2 THEN PropertyValue END) AS Lname,
MAX(CASE WHEN PropertyID = 3 THEN PropertyValue END) AS Gender,
MAX(CASE WHEN PropertyID = 4 THEN PropertyValue END) AS BDate,
MAX(CASE WHEN PropertyID = 5 THEN PropertyValue END) AS Zip,
MAX(CASE WHEN PropertyID = 6 THEN PropertyValue END) AS Phone,
MAX(CASE WHEN PropertyID = 7 THEN PropertyValue END) AS GMTZoneId
FROM @ProfileDetail
--WHERE ProfileID = 97
GROUP BY ProfileID
ORDER BY ProfileID[/code]
Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources