Display of Columns From Database

Question

I have 3 text files each of which contains a list of 20 items. A new column will be added per new day.

I'd like to display these items in a spreadsheet or spreadsheet type layout with each column adjacent to the next.

Spreadsheet output like:

10-28-09 10-29-09 10-30-09
data1    data2    data3
data1    data2    data3
data1    data2    data3

I'm thinking of arranging the data in a mysql database, and execute a query to simply the process, however I can't figure out how to do a few things.

I can arrange the data like this: DB schema; fields: varchar (50)
Columns name = date: 10-30-09 ( mon - fri);

The query would be simple then: "SELECT * FROM table1"

The data needs to expand horizontally so a new column needs to be created by day.

If I was to arrange the data by date like this, I'm not sure what the query would be to get the output into dated columns:

id  name   date
1   data1  10-28-09
2   data1  10-28-09
3   data1  10-28-09
4   data2  10-29-09
5   data2  10-29-09
6   data2  10-29-09
7   data3  10-30-09
8   data3  10-30-09
9   data3  10-30-09

I was thinking of adding a sort column and increment it 1-20 for all rows per date, but then you would need to join on every date somehow, again the output would be:

10-28-09 10-29-09 10-30-09
data1    data2    data3
data1    data2    data3
data1    data2    data3

Answer 1

You shouldn't try to arrange your data in database columns from the database, you should write a front end to arrange the data. You certainly shouldn't be modifying the table every day to add a column.

It's possible that what you really want to do is have 20 columns in your data and add a row each day. Then transposing the data is a relatively easy job for your front end, but it's unclear from your question why you are trying to do this.

Answer 2

MySQL has a hard limit of 4,096 columns: http://dev.mysql.com/doc/refman/4.1/en/column-count-limit.html

That's not an ideal method of storing things. Arranging the data by date is better:

1. It won't hit the column limit
2. It supports optional relations. For instance, if you don't have data1 for a given date - the record won't exist.

TABLE

• RECORD_DATE, [while you've stated varchar, I recommend DATETIME]
• RECORD_VALUE, varchar(50)

Pivoting/transposing the data requires using a CASE expression:

SELECT CASE WHEN t.record_date = '10-28-09' THEN t.record_value ELSE NULL END AS '10-28-09',
       CASE WHEN t.record_date = '10-29-09' THEN t.record_value ELSE NULL END AS '10-29-09',
       CASE WHEN t.record_date = '10-30-09' THEN t.record_value ELSE NULL END AS '10-30-09'
  FROM TABLE t

Reference: CASE

You might've noticed that the columns are hard coded - you'd want to consider using MySQL's Prepared Statements to dynamically create the statement.

If you find that you're missing dates, you'd want to consider creating a numbers table so you can artificially construct dates by adding the appropriate value to a given date.

Answer 3

You can try ur problem with DYNAMIC PIVOTING.

A sample is given here.

------------------Initial Table Creation & Data Population---------------

create table #tblpk (id int identity,groupid int)
insert into #tblpk 
    select 1 union all select 1 union all select 1 
    union all select 2 union all select 2 union all select 1
    union all select 3

create table #tblfk (id int, groupdate datetime, flag int)
insert into #tblfk 
    select 1,'01/02/2009',1 union all 
    select 1,'02/02/2009',0 union all 
    select 1,'03/02/2009',1

----------- Program Starts -------------------------------------------

-- Variable Declarations

declare @col_list varchar(max)

declare @dynquery varchar(max)

--Step 1: Get all the matching records from the two tables

select 
     tpk.id id
     ,tfk.groupdate groupdate
     ,tfk.flag 
into #allRectbl
from #tblpk tpk
inner join #tblfk tfk
on tpk.groupid = tfk.id

--Step 2: Get the unique dynamic columns

select distinct(Cols) as DynamicCols into #dynamicColumns from
(
    select   
     IsNull(@col_list,'') + 
     '[' + cast(groupdate as varchar(32)) + '],' as Cols
    from #allRectbl

)X(Cols)

--Step 3: Make the dynamic pivot columns

select @col_list = IsNull(@col_list,'') + DynCols from

(
    select left(DynCols,len(DynCols)-1) DynCols
    from
     (
      select     
       cast(DynamicCols as varchar(max)) 
      from #dynamicColumns 
      for xml path ('')
     ) X(DynCols)
)Y(DynCols)

--Step 4: Make the Pivoting happen

set @dynquery = 'select * ' +
    'from #allRectbl ' +
    'pivot ( ' +
    'max(flag) ' +
    'for groupdate ' +
    'in (' + @col_list + ') ' +
    ') as pvt'

--Step 5: Execute the query

exec (@dynquery)

--Step 6: Droping the test tables -------------------------

drop table #tblpk
drop table #tblfk

drop table #allRectbl
drop table #dynamicColumns