TSQL - View with cross apply and pivot

Question

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.

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:

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

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.

Answer 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.

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