TSQL - 使用交叉应用和透视视图

this is my base table:

这是我的基表:

docID |  rowNumber | Column1 |  Column2 | Column3

I use cross apply and pivot to transform the records in Column1 to actual columns and use the values in column2 and column3 as records for the new columns. In my fiddle you can see base and transformed select statement.

我使用cross apply和pivot将Column1中的记录转换为实际列,并使用column2和column3中的值作为新列的记录。在我的小提琴中你可以看到base和转换的select语句。

I have columns like Plant and Color which are numbered, e.g. Plant1, Plant2, Plant3, Color1, Color2 etc.

我有像植物和颜色这样的列,它们被编号,例如Plant1,Plant2,Plant3,Color1,Color2等

For each plant that exists in all plant columns I want to create a new row with a comma separated list of colors in one single column.

对于存在于所有工厂列中的每个工厂,我想在一个列中创建一个逗号分隔的颜色列表的新行。

What I want to achieve is also in below screenshot:

我想要实现的还有以下截图:

This should become a view to use in Excel. How do I need to modify the view to get to the desired result?

这应该成为在Excel中使用的视图。如何修改视图以获得所需的结果?

Additional question: The Length-column is numeric. Is there any way to switch the decimal separator from within Excel as a user and apply it to this or all numeric column(s) so that it will be recognized by Excel as a number? I used to have an old php web query where I would pass the separator from a dropdown cell in Excel as a parameter.

附加问题:长度列是数字。有没有办法在Excel中作为用户切换小数点分隔符并将其应用于此数字列或所有数字列,以便Excel将其识别为数字?我以前有一个旧的PHP Web查询,我将从Excel中的下拉单元格中传递分隔符作为参数。

Thank you.

1 个解决方案

#1

First off, man the way your data is stored is a mess. I would recommend reading up on good data structures and fixing yours if you can. Here's a TSQL query that gets you the data in the correct format.

首先,你的数据存储方式是一团糟。如果可以的话,我建议你阅读好的数据结构并修复你的数据结构。这是一个TSQL查询,可以以正确的格式获取数据。

WITH CTE_no_nums
AS
(
SELECT  docID,
        CASE
            WHEN PATINDEX('%[0-9]%',column1) > 0
                THEN SUBSTRING(column1,0,PATINDEX('%[0-9]%',column1))
            ELSE column1
        END AS cols,
        COALESCE(column2,column3) AS vals
FROM miscValues
WHERE       column2 IS NOT NULL
        OR  column3 IS NOT NULL
),
CTE_Pivot
    AS
    (
    SELECT docID,partNumber,prio,[length],material
    FROM CTE_no_nums
    PIVOT
    (
        MAX(vals) FOR cols IN (partNumber,prio,[length],material)
    ) pvt
)

SELECT  A.docId + ' # ' + B.vals AS [DocID # Plant],
        A.docID,
        A.partNumber,
        A.prio,
        B.vals AS Plant,
        A.partNumber + '#' + A.material + '#' + A.[length] AS Identification,
        A.[length],
        SUBSTRING(CA.colors,0,LEN(CA.colors)) colors --substring removes last comma
FROM CTE_Pivot A
INNER JOIN CTE_no_nums B
    ON      A.docID = B.docID
        AND B.cols = 'Plant'
CROSS APPLY (   SELECT vals + ',' 
                FROM CTE_no_nums C 
                WHERE   cols = 'Color' 
                    AND C.docID = A.docID 
                FOR XML PATH('') 
            ) CA(colors)

Results:

DocID # Plant    docID  partNumber prio Plant      Identification     length  colors
---------------- ------ ---------- ---- ---------- ------------------ ------- -------------------------
D0001 # PlantB   D0001  X001       1    PlantB     X001#MA123#10.87   10.87   white,black,blue
D0001 # PlantC   D0001  X001       1    PlantC     X001#MA123#10.87   10.87   white,black,blue
D0002 # PlantA   D0002  X002       2    PlantA     X002#MA456#16.43   16.43   black,yellow
D0002 # PlantC   D0002  X002       2    PlantC     X002#MA456#16.43   16.43   black,yellow
D0002 # PlantD   D0002  X002       2    PlantD     X002#MA456#16.43   16.43   black,yellow