February 26, 2009 at 12:08 am
hi all,
I have a table as
ID Field Value
== ======== ======
1 UserName johnp
1 FirstName John
and i am looking for the output as
ID UserName FirstName
== ======== ======
1 johnp John
thanks in advance
regards
Durgesh J
February 26, 2009 at 5:46 am
Yep... it's call a "Cross-Tab" or "Pivot"... and the type of table you have is called an "NVP" or "Name/Value Pair" table...
SELECT ID,
MAX(CASE WHEN Field = 'UserName' THEN Value END) AS UserName,
MAX(CASE WHEN Field = 'FirstName' THEN Value END) AS FirstName
FROM dbo.yourtable
WHERE ID = @ID --Remove if you want to do the whole table
GROUP BY ID
For more information on this subject, please refer to the following articles...
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/cross+tab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply