I have a text file with date field and analysis header fields on one row and the annalysis values on a second row. Wants to have the date and the analysis values on one row. The pattern repeates for any sample analysed as in attached file.

Any help will be much appreciated. table.PNG

Hi,

Since you don't have a column that references the date field and analysis header field, you have to manually create a reference for these two fields. For this sample I created an autogenerated number labeled as Category that would references the two, then combined using union clause.

;with cte As (
Select Date, Pot, Si, Fe, row_number() OVER (ORDER BY (SELECT 0)) As Category From YourTable Where Pot != '' And Pot Is Not Null
Union All
Select Date, Pot, Si, Fe, row_number() OVER (ORDER BY (SELECT 0)) As Category From YourTable Where Pot = '' Or Pot Is Null)

Select Max(Date) As Date, Max(Pot) As Pot, Min(Si) As Si, Min(Fe) As Fe From cte Group By Category