How to get the following result using SQL

Question

EID PID     Metric  Limit1  Limit2  Limit3
1    8       20       <      210    <
1    8       22       >       89    >=

I have the following requirement

Source

To transform into

EID PID     20-Limit1    20-Limit2 20-Limit3  22-Limit1  22-Limit2   22-Limit3
1    8        <             210     <          >          89           >=

Can you help with this.

Answer 1

Try using this code

      Create table #TempTable (EID INT,PID INT,Col1 VARCHAR(100),Col2 VARCHAR(100))

      ;WITH TestingCTE AS(
            Select EID,PID,
            Cast(Metric as varchar(50))+'-'+ColumnName AS Col1,
            Columnvalue AS Col2,
            Cast(Metric as varchar(50)) +'-'+ ColumnName1 AS Col3,
            Columnvalue1 AS col4,
            Cast(Metric as varchar(50)) +'-'+ ColumnName2 AS col5,
            Columnvalue2  AS col6
            from 
                 (
           select EID,PID,Metric,Limit1,Limit2,Limit3 from Testing
                 ) src
           unpivot
                (
                Columnvalue for ColumnName in (Limit1)
                ) un
           unpivot
                (
               Columnvalue1 for ColumnName1 in (Limit2)
               ) un
          unpivot
              (
              Columnvalue2 for ColumnName2 in (Limit3)
              ) un  
           )
        INsert into #TempTable (EID,PID,Col1,Col2)
        Select EID,PID,Col1,Col2 from TestingCTE
        UNION ALL
        Select EID,PID,Col3,Cast(Col4 as Varchar(100)) from TestingCTE
        UNION ALL
        Select EID,PID,Col5,Col6 from TestingCTE

        Declare @var NVARCHAR(MAX)
        Declare @query NVARCHAR(MAX)
        Select @var= Stuff((SELECT ', ' + QUOTENAME(col1) from #TempTable       
                For XML PATH ('')),1,1,'')

        SET @query='Select * from (Select * from #TempTable) a
        PIVOT(MAX(col2) for col1 IN('+@var+'))Pivoted'

        EXEC sp_executesql @query

        Drop table #TempTable

Answer 2

A simple solution to get the desired result would be the following:

create table E_Limit  (EID int, PID int,    Metric int,  Limit1 varchar(10),  Limit2 varchar(10),  Limit3 varchar(10));
go
insert into E_LIMIT values(1,    8 ,      20  ,     '<'   ,   '210' ,   '<');
insert into E_LIMIT values(1,    8 ,      22  ,     '>'   ,    '89'  ,  '>=');
Go
Select * From
(SELECT EID, PID, CONVERT(VARCHAR,METRIC) + '-'+ LIMIT_RANGE METRIC_LIMIT_RANGE,LIMIT
FROM 
   (SELECT EID, PID, METRIC, LIMIT1, LIMIT2, LIMIT3
   FROM E_LIMIT) UP
UNPIVOT
   (LIMIT FOR LIMIT_RANGE IN 
      (LIMIT1, LIMIT2, LIMIT3)
)AS unpvt) P
Pivot (MAX(LIMIT) FOR METRIC_LIMIT_RANGE IN
(
[20-LIMIT1]
,[20-LIMIT2]
,[20-LIMIT3]
,[22-LIMIT1]
,[22-LIMIT2]
,[22-LIMIT3]
)) PVT

If you need more LIMIT columns or Metric values, you can try to use the information schema along with the values ​​of the METRIC column and create a dynamic summary query to get the desired result.

Email me if you want to have a sample code for a dynamic script to generate the required results.