TSQL - view using the cross and rotate

Question

this is my base table:

docID | rowNumber | Column1 | Column2 | Column3 

I use cross apply and pivot to convert the entries in column 1 to actual columns and use the values ​​in column2 and column3 as entries for new columns. In my fiddle, you can see the base and converted select statement.

I have columns like Plant and Color that are numbered, for example. Plant1, Plant2, Plant3, Color1, Color2, etc.

For each plant that exists in all columns of the plant, I want to create a new row with a comma-separated list of flowers in one column.

What I want to achieve is also shown below:

This should be a view for use in Excel. How can I change the view to get the desired result?

Additional question: the Length column is numeric. Is there a way to switch the decimal separator from Excel as a user and apply it to this or to the whole numeric column (s) so that Excel recognizes it as a number? I used to have an old php web request where I passed the separator from the drop down cell to Excel as a parameter.

Thanks.

Answer 1

First, the person who stores your data is a mess. I would recommend reading good data structures and correcting yours if possible. Here's a TSQL query that will deliver 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