Group and Transpose data

Or:SELECT DISTINCT t1.ID,   (SELECT t2.value FROM SomeTable t2 WHERE t2.ID = t1.ID AND t2.key = 'name') AS name,   (SELECT t3.value FROM SomeTable t3 WHERE t3.ID = t1.ID AND t3.key = 'surname') AS surname,   (SELECT t4.value FROM SomeTable t4 WHERE t4.ID = t1.ID AND t4.key = 'identity') AS identityFROM SomeTable t1ORDER BY t1.ID

select a.[id],a.[name],a/surname,a/[identity] from (select a.[id],a.value as name        ,b.value as surname       ,c.value as [identity], row_number() over (partition by (a.[id] order by identity) as rn from (select [id],[value] from yourtable where [key] = 'name') as aleft outer join (select [id],[value] from yourtable where [key] = 'surname')  as b  on a.id=b.id left outer join (select [id],[value] from yourtable where [key] = 'identity')  as c on a,id=c,id.   ) as a where rn=1 order by 1if rn <> 1 then you have a problem with the joins as you will not have distinguishable sets of data, with the columns you have provided

MattDuPlessis,

I happened by and see that you already have solutions from two very brilliant Experts; therefore, please ensure to get back to them regarding how that worked out. As an alternative, since this looks like a simple PIVOT to me given that the keys involved is a very specific, finite list (i.e., always name, surname and identity) then you can use the PIVOT syntax in SQL 2008 Express.

Again, this is just another approach. Please award experts above if worked for you.

Regards,
Kevin

select ID, name, surname, identity
from your_table_name
pivot(
   max(value) 
   for key 
   in (name, surname, identity)
) pvt
;

Select allOpen in new window

seems like group by id on a view with mathewspatrick's select would be the most robust and flexible

@stevebobs: what is your reasoning behind that conclusion ?
It made me re-look at above though as I thought the difference was one was using sub queries and the other was using conditional aggregates.

https://www.experts-exchange.com/A_3527.html

Since it is not, that is another option not yet mentioned:

select id
     , max(case [key] when 'name' then value end) as 'name'
     , max(case [key] when 'surname' then value end) as 'surname'
     , max(case [key] when 'identity' then value end) as 'identity'
from your_table_name
group by id

Select allOpen in new window

For the most flexibility (to not have to know keys upfront or have to maintain list in hard coded fashion), you will have to turn to dynamic SQL. Mark Wills has a nice explanation of this in his article regarding his dynamic pivot procedure which is an already developed procedure that wraps the dynamic SQL execution for you. You would put your data in a pivot ready format (view if necessary) and then simply pass the name of the view to the procedure and some other pieces of information like which column to pivot on and aggregate to use.

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

Regards,
Kevin

Does exactly what is needed, thanks guys. The dynamic pivot solution might be the best way in the future as there is no requirement to know what the name keys are but for now. this is great.