how to convert raw data in below form

Sandeep Kumar

4y1.1kSQL

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke4y ago
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 Kumar4y ago
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 Sontakke4y ago
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 Kumar4y ago
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 Sontakke4y ago
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.