Convert Raw Value into column as different Comumn header in sql server

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

Output Result:

Helmet_GP	Helmet_Safety_Id	Helmet_Activity	Helmet_Ua	Guard_Gp	Guard_Safety_Id	Guard_Activity	Guard_Ua
2	15	Helmet	4	3	16	Guard	5

WITH CTE_TestData as (
select AnalysisID = 44, RefID=27, RefColumn = 'Reporting_Currency', Value='EUR'
union all select 44, 27, 'Reporting_Currency', 'EUR'
union all select 44, 27, 'Reporting_Currency', 'USD'
union all select 44, 27, 'Reporting_Group', '0001'
union all select 44, 27, 'Reporting_Group', '0001'
union all select 44, 27, 'Reporting_Group', '0002'
union all select 44, 27, 'Reporting_Language', 'EN'
union all select 44, 27, 'Reporting_Language', 'EN'
union all select 44, 27, 'Reporting_Language', 'DE'
union all select 65, 27, 'Company_Code', null
union all select 65, 27, 'MANDT', null
union all select 65, 27, 'Reporting_Currency', null
)
select AnalysisID, Reporting_Currency, Reporting_Group, Reporting_Language
from (
select *, rowno = row_number() OVER (PARTITION BY refid, analysisid, RefColumn Order By refid, analysisid, refcolumn)
from
CTE_TestData t
) unpvt
PIVOT (min(value) FOR RefColumn in ([Reporting_Currency], [Reporting_Group], [Reporting_Language])) pvt

2 Replies

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

Sandeep Kumar4y ago
I need result as output result as shown in table,please look carefully
Gajendra Jangid4y ago
1. WITH CTE_TestData as (
3. select AnalysisID = 44, RefID=27, RefColumn = 'Reporting_Currency', Value='EUR'
4. union all select 44, 27, 'Reporting_Currency', 'EUR'
5. union all select 44, 27, 'Reporting_Currency', 'USD'
6. union all select 44, 27, 'Reporting_Group', '0001'
7. union all select 44, 27, 'Reporting_Group', '0001'
8. union all select 44, 27, 'Reporting_Group', '0002'
9. union all select 44, 27, 'Reporting_Language', 'EN'
10. union all select 44, 27, 'Reporting_Language', 'EN'
11. union all select 44, 27, 'Reporting_Language', 'DE'
12. union all select 65, 27, 'Company_Code', null
13. union all select 65, 27, 'MANDT', null
14. union all select 65, 27, 'Reporting_Currency', null
16. )
18. select AnalysisID, Reporting_Currency, Reporting_Group, Reporting_Language
19. from (
20. select *, rowno = row_number() OVER (PARTITION BY refid, analysisid, RefColumn Order By refid, analysisid, refcolumn)
21. from
22. CTE_TestData t
23. ) unpvt
24. PIVOT (min(value) FOR RefColumn in ([Reporting_Currency], [Reporting_Group], [Reporting_Language])) pvt