sql - Selecting specific COLUMNS after a previous select that returned one ROW -

i stumped here , in need of help.

i have table called "2016blocks" table has rows each individual. each row has columns every day of year (365 columns each row).

i have select statement return single row looks this:

select * [2016blocks] ([officialid] = @officialid) 

the above select statement return 1 row 365 columns. each column either blank or have date in it. name of columns (blocks1, blocks2, blocks3......blocks365)

i want select statement on row return columns have date in them.

how do this?

you'll have use unpivot function that. have 365 columns might not want mention 365 hardcoded column names. purpose can use dynamic query. can use below query in subquery or insert records in temp table , add filter blockvalue. hope helps.

declare @colsunpivot nvarchar(max),    @query  nvarchar(max)  select @colsunpivot    = stuff((select ','+quotename(c.column_name)            information_schema.columns c            c.table_name = '2016blocks' ,                  c.column_name 'blocks%'            xml path('')), 1, 1, '')  set @query    = 'select id, entityid,         blocknumber,         blockvalue      2016blocks      ([officialid] = @officialid)      unpivot      (         blockvalue         blocknumber in ('+ @colsunpivot +')      ) u'  exec sp_executesql @query;