在SQL Server中使用平面表结构的透视表，不进行聚合

Question

我有一个平面表结构，我把它变成了一个基于列的表。我正在努力从我的原始数据中获取rowId，以显示在基于列的表中。非常感谢您的帮助。

表中的原始数据来源于三个不同的表：

| rowId            |columnName       |ColumnValue  |
| ---------------- |:---------------:| -----------:|
| 1                |itemNo           |1            |
| 1                |itemName         |Polo Shirt   |
| 1                |itemDescription  |Green        |
| 1                |price1           |4.2          |
| 1                |price2           |5.3          |
| 1                |price3           |7.5          |
| 1                |displayOrder     |1            |
| 1                |rowId            |[NULL]       |
| 2                |itemNo           |12           |
| 2                |itemName         |Digital Watch|
| 2                |itemDescription  |Red Watch    |
| 2                |price1           |4.0          |
| 2                |price2           |2.0          |
| 2                |price3           |1.5          |
| 2                |displayOrder     |3            |
| 2                |rowId            |[NULL]       |

SQL使用pivot给我列结构：

select [displayOrder],[itemDescription],[itemName],[itemNo],[price1],[price2],[price3],[rowId]
 from 
(
SELECT  [columnName], [columnValue] , row_number() over(partition by c.columnName order by cv.rowId) as rn
FROM  tblFlatTable AS t
JOIN tblFlatColumns c
ON t.flatTableId = c.flatTableId
JOIN tblFlatColumnValues cv
ON cv.flatColumnId = c.flatColumnId
WHERE (t.flatTableId = 1) AND (t.isActive = 1) 
AND (c.isActive = 1) AND (cv.isActive = 1)
) as S
Pivot
(
    MIN([columnValue])
    FOR columnName IN ([displayOrder],[itemDescription],[itemName],[itemNo],[price1],[price2],[price3],[rowId])
) as P

结果：

|displayOrder|itemDescription|itemName      |price1|price2|price3|rowId |
| ---------- |:-------------:|:------------:|:----:|:----:|:----:|-----:|
|1           |Green          |Polo Shirt    |4.2   |5.3    |7.5  |[NULL]|
|3           |Red watch      |Digital Watch |4.0   |2.0    |1.5  |[NULL]|

我理解为什么要获取rowId的NULL值。我一直坚持的是，我不确定是否有可能做到这一点，因为我已经看了很多例子，但似乎没有一个能做到，那就是从原始数据中提取rowId的值，并将其添加到我的结构中。

Answer 1

现在看起来很明显！

我现在不将rowId作为我的平面结构的一部分。

| rowId            |columnName       |ColumnValue  |
| ---------------- |:---------------:| -----------:|
| 1                |itemNo           |1            |
| 1                |itemName         |Polo Shirt   |
| 1                |itemDescription  |Green        |
| 1                |price1           |4.2          |
| 1                |price2           |5.3          |
| 1                |price3           |7.5          |
| 1                |displayOrder     |1            |
| 2                |itemNo           |12           |
| 2                |itemName         |Digital Watch|
| 2                |itemDescription  |Red Watch    |
| 2                |price1           |4.0          |
| 2                |price2           |2.0          |
| 2                |price3           |1.5          |
| 2                |displayOrder     |3            |

我已经更新了SQL，你可以看到我正在从tblFlatColumnValues 中提取rowId

select [rowId],[displayOrder],[itemDescription],[itemName],[itemNo],[price1],[price2],[price3]
 from 
(
SELECT  cv.rowId, [columnName], [columnValue] , row_number() over(partition by c.columnName order by cv.rowId) as rn
FROM  tblFlatTable AS t
JOIN tblFlatColumns c
ON t.flatTableId = c.flatTableId
JOIN tblFlatColumnValues cv
ON cv.flatColumnId = c.flatColumnId
WHERE (t.flatTableId = 1) AND (t.isActive = 1) 
AND (c.isActive = 1) AND (cv.isActive = 1)
) as S
Pivot
(
    MIN([columnValue])
    FOR columnName IN ([displayOrder],[itemDescription],[itemName],[itemNo],[price1],[price2],[price3])
) as P