how to convert raw data in below form

Question

Please help me as soon as posible

This is the main table ,I want convert into as output result

select gp,Safety_Id,activity,Ua from TblPpe

Gp	SId	Activity	Ua
2	15	Helmet	4
3	16	Guard	5

Output Result:

Helmet_GP	Helmet_SId	Helmet_Activity	Helmet_Ua	Guard_Gp	Guard_SId	Guard_Activity	Guard_Ua
2	15	Helmet	4	3	16	Guard	5

Nitin Sontakke · Answer

Here you go: declare @tblppe table ( [Id] integer not null primary key clustered ,[Gp] integer not null ,[SId] integer not null ,[Activity] varchar (30) ,[Ua] integer not null ) insert into @tblppe values (1,2, 15, 'Helmet' , 4) ,(2,3, 16, 'Guard' , 5) ,(3,4,17, 'Mask' ,6) select * from @tblppe select a.[Gp] [Helmet_GP] ,a.[SId] [Helmet_SId] ,a.[Activity] [Helmet_Activity] ,a.[Ua] [Helmet_Ua] ,b.[Gp] [Guard_GP] ,b.[SId] [Guard_SId] ,b.[Activity] [Guard_Activity] ,b.[Ua] [Guard_Ua] ,c.[Gp] [Mask_GP] ,c.[SId] [Mask_SId] ,c.[Activity] [Mask_Activity] ,c.[Ua] [Mask_Ua] from @tblppe a left outer join @tblppe b on b.[Activity] = 'Guard' and b.[Id] = a.[Id] + 1 left outer join @tblppe c on c.[Activity] = 'Mask' and c.[Id] = a.[Id] + 2 where a.[Activity] = 'Helmet'

Sandeep Kumar · Answer

this is the interview question and column should be concatinate (_) as you can see like Helmet_Gp etc. the requirement is column "Activity " againt record want to same in single raw, and Activity list can be more means lots of activity. please it is very important for mr,help me. sir plesae write queary again so that i can understand

Nitin Sontakke · Answer

You have to add just one more LEFT OUTER JOIN to my query. This still doesn't solve the problem. Is it GURANTEED that rows will ALWAYS be in this order? Is this an INTERVIEW question or a real life scenario you want to solve. There must be a SOLID connection between these rows. Without that the joins will not work.

Sandeep Kumar · Answer

Sorry -----this is my Scripts and i wanted as declare @tblppe table ( [Id] integer not null primary key clustered ,[Gp] integer not null ,[SId] integer not null ,[Activity] varchar(30) ,[Ua] integer not null ) insert into @tblppe values (1,2, 15, 'Helmet', 4) ,(2,3, 16, 'Guard', 5) ,(3,4,17,'Mask',6) select * from @tblppe ---------------------- Id Gp SId Activity Ua 1 2 15 Helmet 4 2 3 16 Guard 5 3 6 17 Mask 8 Output Result: Helmet_GP Helmet_SId Helmet_Activity Helmet_Ua Guard_Gp Guard_SId Guard_Activity Guard_Ua Mask_Gp Mask_Sid Mask_activity Mask_Ua 2 15 Helmet 4 3 16 Guard 5 6 17 Mask 8 please help me sir,

Nitin Sontakke · Answer

You have not mention which database you are using. ALWAYS mention that. This is very simple given just the two rows. However, if there are more, there MUST BE some unique way to pair the rows. Hope you understand. declare @tblppe table ( [Gp] integer not null primary key clustered ,[SId] integer not null ,[Activity] varchar (30) ,[Ua] integer not null ) insert into @tblppe values (2, 15, 'Helmet' , 4) ,(3, 16, 'Guard' , 5) select * -- just give the column names as expected here! from @tblppe a left outer join @tblppe b on b.[Activity] = 'Guard' where a.[Activity] = 'Helmet' Always provide script, instead of data in table. That way it is quicker to provide the solution.