如何在查询中获取Row in Column

I have this view in my sql database:

我在我的sql数据库中有这个视图:

I have this values in my View with IdEsame = 39

我在我的视图中使用IdEsame = 39获得此值

    ID  IdEsame IdParametro Ordinamento Colonna
    72  39      163         1           1
    73  39      164         1           2
    74  39      165         1           3
    75  39      166         2           1
    76  39      167         2           2
    77  39      168         2           3
    78  39      169         3           1
    79  39      170         3           2
    80  39      171         3           3

Now I want to build a query to set the row in column by Ordinamento Column.

现在我想构建一个查询来在Ordinamento Column的列中设置行。

So this query that return this result set

所以这个返回这个结果集的查询

ID  IdEsame IdParametro IdParametro2    IdParametro3
72  39      163         164             165
73  39      166         167             168
74  39      169         170             171

2 个解决方案

#1

TRY THIS: The solultion would be dynamic PIVOT as below but id will return the minimum of every set of Ordinamento and I hope you also want the same

尝试这个:解决方案将是动态PIVOT如下,但id将返回每组Ordinamento的最小值,我希望你也想要相同

CREATE TABLE #temp(ID INT,  IdEsame INT, IdParametro INT, Ordinamento INT, Colonna INT)
INSERT INTO #temp VALUES
(72,39,163,1,1),
(73,39,164,1,2),
(74,39,165,1,3),
(75,39,166,2,1),
(76,39,167,2,2),
(77,39,168,2,3),
(78,39,169,3,1),
(79,39,170,3,2),
(80,39,171,3,3)

DECLARE @col VARCHAR(1000)
DECLARE @sql VARCHAR(2000)

SELECT @col = 
    COALESCE(@col + ', ','') + QUOTENAME('IdParametro' + CAST(Colonna AS VARCHAR))
FROM #temp
GROUP BY Colonna

SET @sql = '
SELECT t.ID, IdEsame, ' + @col + '
FROM (SELECT 
        IdEsame, 
        IdParametro,
        CONCAT(''IdParametro'',Colonna) AS Colonna,
        Ordinamento
    FROM #temp)p
PIVOT(MAX (IdParametro) FOR Colonna IN ( ' + @col + ' )
) AS pvt
OUTER APPLY( SELECT MIN(id) AS ID 
            FROM #temp t WHERE t.Ordinamento = pvt.Ordinamento) t'

EXEC (@sql)

OUTPUT:

ID  IdEsame IdParametro1    IdParametro2    IdParametro3
72  39      163             164             165
75  39      166             167             168
78  39      169             170             171

#2

One method uses conditional aggregation:

一种方法使用条件聚合:

select min(id), idesame,
       max(case when colonna = 1 then IdParametro end) as IdParametro1,
       max(case when colonna = 2 then IdParametro end) as IdParametro2,
       max(case when colonna = 3 then IdParametro end) as IdParametro3
from t
group by Ordinamento, idesame;

I don't understand how the first column is assigned. The numbers seem rather arbitrary, but I'm speculating that you want the minimum id for each row.

我不明白第一列的分配方式。数字似乎相当随意,但我推测你想要每行的最小id。

#1