TSQL - 具有交叉应用和枢轴的视图 [英] TSQL - View with cross apply and pivot

问题描述

这是我的基准表：

docID |  rowNumber | Column1 |  Column2 | Column3

我使用cross apply和pivot将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.

我有一些列，如植物和颜色，编号，例如Plant1，Plant2，Plant3，Color1，Color2等。

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

对于所有植物列中存在的每个植物，我想用逗号分隔的颜色列表创建一个新行一个单列。

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:

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

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

其他问题：长度列是数字。有没有办法以Excel形式切换用户的小数分隔符，并将其应用于此或所有数字列，以便Excel将其识别为数字？
我曾经有一个旧的php Web查询，我将通过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.

谢谢。 >

Thank you.

推荐答案

首先，人们存储数据的方式是一团糟。我建议您阅读良好的数据结构并修复您的数据。这是一个TSQL查询，可以以正确的格式获取数据。

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.

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)

结果：

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

这篇关于TSQL - 具有交叉应用和枢轴的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！