SQL server select one record per day based on value in field

I have a followup question to this question.

SQL Server Get values of top three records and display in one row per person

ID   Name       Date1                   Value2  Date2                   Value2  Date3                   Value3  Date4                   Value4  Date5                   Value5  Date6                   Value6  Date7                   Value7

12  John Smith  2011-06-27 14:06:10.517 None    2011-06-27 00:17:53.987 None    2011-06-26 21:56:07.577 Medium  2011-06-26 13:32:31.190 None    2011-06-26 02:47:54.357 None    2011-06-25 19:32:00.000 Medium  2011-06-25 13:43:22.000 Medium
12  Jack Smith  2011-06-27 05:54:59.320 None    2011-06-26 06:28:55.033 None    2011-06-25 16:25:00.000 Medium  2011-06-25 14:27:11.017 Large   2011-06-25 06:11:45.793 Large   2011-06-24 19:33:24.520 Medium  2011-06-24 06:17:35.887 None

I need to get one value per day. If there is a value that is not equal to 'None', I need that record for the given date.

Here is what the outcome should look like:

ID   Name       Date1                   Value2  Date2                   Value2  Date3                   Value3  Date4                   Value4  Date5                   Value5  Date6                   Value6  Date7                   Value7

12  John Smith  2011-06-27 00:17:53.987 None    2011-06-26 21:56:07.577 Medium  2011-06-25 13:43:22.000 Medium
12  Jack Smith  2011-06-27 05:54:59.320 None    2011-06开发者_JAVA百科-26 06:28:55.033 None    2011-06-25 06:11:45.793 Large   2011-06-24 19:33:24.520 Medium  

My raw data is in this format: (Records I need are marked with *)

ID  Name        Date                    Value
12  JACK Smith  2011-06-27 05:54:59.320 None    *
12  JACK Smith  2011-06-26 06:28:55.033 None    *
12  JACK Smith  2011-06-25 16:25:00.000 Medium  
12  JACK Smith  2011-06-25 14:27:11.017 Large
12  JACK Smith  2011-06-25 06:11:45.793 Large   *
12  JACK Smith  2011-06-24 19:33:24.520 Medium  *
12  JACK Smith  2011-06-24 06:17:35.887 None
12  JACK Smith  2011-06-23 00:30:28.363 None    *
12  JACK Smith  2011-06-22 00:47:41.800 None    *
12  JACK Smith  2011-06-21 06:03:55.000 None    *

Any help is greatly appreciated.

Not clear what you're asking...

You can restrict your recordset with a WHERE clause (this will remove the record entirely)...probably easier to do this on your original recordset (at the link you posted) than on this revised recordset above...

WHERE value <> 'None'

Or you can keep the record, and just restrict the display with a function (many options -- here's one:)

Replace('None','')

...here's another one:

CASE value WHEN 'None' THEN '' ELSE value END

Something like this then... It will work, but it might have a typo, as I'm just typing it out without testing.

SELECT
   ID,
   Name,
   Right(
   -- The Right() function will strip-off the leading integer that you need to first add to the date so
   -- you can get the record you want.
         Min(
         -- The Min() function will get a single record for you
         -- the functions below will manipulate the date so that records with a non-'None' value are
         -- guaranteed to have a larger date  then records with a 'None' value.  This is done by adding
         -- an integer to the front of the date -- '0' for non-'None' values, and '1' for 'None' values.
             Cast(CASE value WHEN 'None' THEN 1 ELSE 0 END as varchar(1))
             -- The CASE statement outputs a 0 or 1
             -- The Cast() function changes the output to text instead of a number, so you can use the
             --    string concat (+) later.
             + 
             -- string concatenation, which only works on text, not numbers
             Cast(Date as varchar(25))
             -- The Cast() function changes the date to text, so you can use it with the string concat above
             )
         , 23
        -- 23 should be the number of characters in the date itself
        -- adjust the number up or down as necessary.            
        ) as myDate,
   Value
FROM
   Table
GROUP BY
   ID,
   Name,
   Value

This should reduce your original recordset so that it only contains the records you want. Afterward, you can apply the horizontal solution that the @Manfred Sorg came-up with for your first question.

you want do transposition, i guess.

you should use pivot table some how that

table: pivot values: 0, 1, 2, ...

the idea be able to position each value the each day in correct column and after group for get one single row

after you need do query

select 

 resultset.id, resultset.Name,

 case when not  max(date1) is null then max(date1) 
 else null end as date1,
 case when not  max(value1) is null then max(value1) 
 else '' end as value1,
 ...
 ...
 ...

from (

    select mltwngd.id, mltwngd.Name,
      //sorry that part is ugly, ugly, 
      case when i=0 then datedd(day, mltwngdg.periodstart, auxtable.i)
      else '' end as date1,

      case when i=0 then datedd(day, mltwngdg.periodstart, auxtable.i)
      else '' end as value1,

      ...
      ...
      ...

    from mylinealtable_with_nogooddesing mltwngd

    inner join
    (
      select id, min(date) periodstart, 
      max(date) as periodend, 
      datediff("dd", max(date), min(date)) as days
      from mylinealtable_with_nogooddesing
      where ...
      group by id
    ) as mltwngdg
    on mltwngdg.id = mltwngd.id

    inner join     
    (
      select *
      from pivot
      where i >= 0 and i < @period_max_days
    )auxtable
    on auxtable.i >0 and auxtable.i < days
)resultset

group by resultset.id, resultset.Name

the idea simplified is

declare @aux table (id int, [name] varchar(20),
                      date1 datetime,
                      valor1 varchar(10),
                      date2 datetime,
                      valor2 varchar(10)
                      )


insert into @aux(id, [name], date1, valor1, date2, valor2)
values(1,'CARLOS','20110201','XP',NULL, '')

insert into @aux(id, [name], date1, valor1, date2, valor2)
values(1,'CARLOS',NULL, '','20110201','WIN7')

select * from @aux

select 
  x.id, x.name,
  case when not max(date1) is null then max(date1)
  else null end as date1,
  case when not max(valor1) is null then max(valor1)
  else null end as valor1,
  case when not max(date2) is null then max(date2)
  else null end as date2,
  case when not max(valor1) is null then max(valor2)
  else null end as valor2   
from @aux x
group by x.id, x.name