0

For a purpose of reporting I need to present data from table: table A (column1, column2, date1, date2, date3,...,dateN)

My query need to present all dates in one column separated with # .

(YYYY-MM-DD# YYYY-MM-DD#..)

But problem is that number of date columns is not fixed, because from product to product can be different number of dates.

Any idea ?

  • what do you mean by "that number of date column is not fixed" ? do you mean you have several tables with different structure or only one table with more or less null inside ? – irimias Dec 6 '16 at 13:35
  • one table, but app which inserting data in table can insert more columns in table, so for one product can be 11 dates and for another only 2. – Darko Milic Dec 6 '16 at 13:38
1

I'm was thinking about some ugly query such as:

select isnull(convert(varchar(8), date1, 121),'') 
        + isnull('#'+convert(varchar(8), date2, 121),'')
        + isnull('#'+convert(varchar(8), date3, 121),'')
        ...
        + isnull('#'+convert(varchar(8), dateN, 121),'')
from A

But we could do better using something like:

select cast((
       select (
              select '#'+T2.N.value('./text()[1]',  'varchar(max)')
              from (
                   select T.*
                   for xml path(''), type
                   ) as T1(N)
                cross apply T1.N.nodes('/*') as T2(N)
              for xml path(''), type
              ).value('substring(./text()[1], 2)',  'varchar(max)')
       for xml path(''), type
       ) as varchar(max))
from A as T

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.