retrieve columns with through sql

Question

Here is a sample of my table , no index , no primary key , no nothing and with almost 6000 columns.

date     col1 col2 col3 ... col6000
jun14     0    .    0       0
may14     1    .    2       2
apr14     1    .    3       1
mar14     1    3    3       0
feb14     1    3    3       5
jan14     1    0    3       8
dec13     1    0    3       1

I want to know , which column has only . since apr 2014.

Edited

My expected results would be to know, in the example of 3 columns , see above, that column 2 has only dots since april 2014

End of Edited

I was thinking of transpose my table but it seems to be a pointless solution.

I'm stuck on that issue since yesterday and I don't feel I have a solution. Argh!

Thanks.

Edited: My query would be the following

select * from mytable where date < (input('may14',MONYY5.));

However, it does not give me what I'm expecting as it is retrieving all the rows from all the columns. I thought about transposing my table but having done that, it does not take me anywhere.

End of Edited

6000 columns??? are you trying to say table has 6000 rows of data? — Murtaza
– Murtaza, Commented Jun 20, 2014 at 8:54
What kind of db server are you using? What query have you got so far? Where is your specific problem? Can you set up some SQL to provide us with a repro? What research have you done and why didn't it help you solve the problem? — Jeroen
– Jeroen, Commented Jun 20, 2014 at 8:56
I'm using SQL within SAS. Let me put what I have done so far. — Andy K
– Andy K, Commented Jun 20, 2014 at 8:57
let's say you only have 3 columns, what result would you expect ? — NeedAnswers
– NeedAnswers, Commented Jun 20, 2014 at 9:03
@hoangnnm: I've put my expected results , above , in the edited part. — Andy K
– Andy K, Commented Jun 20, 2014 at 9:06

NeedAnswers · Accepted Answer · 2014-06-20 09:32:29Z

Here is a dynamic approach, and i assume your date column is in DateTime format, the result is a list of all columns which has only dots from a specific time :

if object_id('test') is not null drop table test

create table test
(
[date] datetime, 
col1 nvarchar(1), 
col2 nvarchar(1), 
col3 nvarchar(1), 
col4 nvarchar(1), 
col5 nvarchar(1), 
col6 nvarchar(1), 
col7 nvarchar(1)
)

declare @i int, @cols int, @cmd nvarchar(max)

insert into test values('2014-01-01', 1, 2, 3, 4, 5 ,6 ,7)
insert into test values('2014-02-01', 1, 2, 3, 4, 5 ,6 ,7)
insert into test values('2014-03-01', 1, 2, 3, 4, 5 ,6 ,7)
insert into test values('2014-04-01', 1, 2, 3, 4, 5 ,6 ,7)
insert into test values('2014-05-01', 1, '.', '.', 4, 5 ,6 ,7)
insert into test values('2014-06-01', 1, '.', '.', 4, 5 ,6 ,7)
insert into test values('2014-07-01', 1, '.', '.', 4, 5 ,6 ,7)
insert into test values('2014-08-01', 1, '.', 5, 4, 5 ,6 ,7)


if object_id('tempdb..#chk') is not null drop table #chk 
create table #chk
(
    checkValue int
)

declare @result table
(
    ColList nvarchar(64) 
)

declare @dateFrom datetime, @test cursor, @colName nvarchar(64), @returnValue int 

set @dateFrom = '2014-05-01'

set @test = cursor for 
                select name from syscolumns 
                where id = object_id('test') 
                and name <> 'date' 
                order by colorder 

open @test
fetch next from @test into @colName
while @@fetch_status = 0 
begin
    truncate table #chk
    set @cmd = 'select top 1 1 from test where date >= ''' + convert(varchar(32), @dateFrom) + ''' and ' + @colName + ' <> ''.'''

    insert into #chk exec( @cmd)

    if @@rowcount = 0
        insert into @result values(@colName) 

    fetch next from @test into @colName 
end

select * from @result

Please correct me if i misunderstood your need!

That is cool, @hoangnnm. I though simple sql could do the trick but it is not. I will need programmation in order to do that. Cheers.

Thilo · Accepted Answer · 2014-06-20 09:05:12Z

1

I don't think there is much you can do in SQL with 6000 columns.

I'd download the whole thing to a CSV file (how many rows do you have?) and write some processing code in a programming language that goes through the file.

From the looks of it, it can be a single-pass (make sure you have the file ordered by that date column).

let's say I have only 3 columns. Would it be simpler?

With three columns, maybe

SELECT max(date) FROM the_table WHERE col1 <> '.' HAVING max(date) < 'may14';
SELECT max(date) FROM the_table WHERE col2 <> '.' HAVING max(date) < 'may14';
SELECT max(date) FROM the_table WHERE col3 <> '.' HAVING max(date) < 'may14';

By the way, do the dates really look like that? If they cannot be sorted in a meaningful way, you have another big problem.

edited Jun 20, 2014 at 9:05

answered Jun 20, 2014 at 8:59

Thilo

264k107 gold badges527 silver badges674 bronze badges

3 Comments

Andy K Over a year ago

Hi Thilo, thanks for your answer. However, let's say I have only 3 columns. Would it be simpler?

Andy K Over a year ago

I have only 711 rows.

Andy K Over a year ago

The date looks like that. They are in a readable date format, so it works well.

Collectives™ on Stack Overflow

retrieve columns with through sql

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related