Member Avatar for scomrade

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
  • 139 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by Hardz
Member Avatar for Hardz

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.