0

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

2
Contributors
1
Reply
20
Views
2 Years
Discussion Span
Last Post by Hardz
0

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

Edited by Hardz

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.