SQL - How do I create a table with values as columns and a new format?

Question

I have a table which currently looks a bit like this-

CASEID  ¦  FORMNAME  ¦  NAME  ¦  VALUE

601554  ¦  CASEFORM  ¦  Mond  ¦  AAAA
601554  ¦  CASEFORM  ¦  Tues  ¦  BBBB
601554  ¦  CASEFORM  ¦  Wedn  ¦  CCCC
601554  ¦  CASEFORM  ¦  Thur  ¦  DDDD

I now want to create a new table in SQL that will copy the data and change the format of it entirely as follows-

CASEID  ¦  FORMNAME  ¦  Mond  ¦  Tues  ¦  Wedn  ¦  Thur

601554  ¦  CASEFORM  ¦  AAAA  ¦  BBBB  ¦  CCCC  ¦  DDDD

The original table has about 400 lines, so the new table is going to need 400 columns.

My SQL knowledge is certainly limited, but I can always blunder my way to a solution when I need one. In this case however I don't even know where to begin. Can someone point me in the right direction?

Answer 1

Start with this query:

create table NewTable(CASEID int,  FORMNAME varchar(255))
go

insert into NewTable select distinct caseid,formname from OldTable
go

select  distinct 'alter table NewTable add ' +[name]+ ' varchar(255)'  from oldtable

and then copy the results and run them.

Then run this query:

select distinct 'update NewTable set ' +Name+ ' = ''' + Value +''' where caseid = ' +cast(caseid as varchar(20))+
' and FORMNAME = '''+Formname+''''
from oldtable

and then copy the results and run them.

Edit: Added automated version:

create table NewTable(CASEID int,  FORMNAME varchar(255)) 
go 
insert into NewTable select distinct caseid,formname from OldTable 
go
DECLARE @query VARCHAR(max)
set @query = ''
select  @query = @query + 'alter table NewTable add ' +[name]+ ' varchar(255);'  from (select distinct name from oldtable )c
exec (@query)
set @query = ''
select @query = @query+ 'update NewTable set ' +Name+ ' = ''' + Value +''' where caseid = ' +cast(caseid as varchar(20))+ ' and FORMNAME = '''+Formname+'''' 
from  (select distinct  Name, Value, Caseid, FormName from OldTable)c
exec (@query)

Answer 2

You're defining a metamodel.

What you need to do is to loop on your METAMODEL table and build a custom ALTER statement for each row found corresponding to whatever your definition of a MODEL column is.

I also see you also want to transform the values. If that is so, build the DML statement on the fly as well in the same loop, and execute them afterwards so your destination model is finished building.

Links:

alter table mysql: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

cursors mysql: http://dev.mysql.com/doc/refman/5.0/en/cursors.html

---

On the other hand, this query:

SELECT CONCAT('ALTER TABLE mytable ADD COLUMN ', t.name, ' VARCHAR(256);') FROM mytable t

will return the alter table statements you need to manually add these columns. You can use this as a model to build the next query to add up the values to your table later.

Rgds.

Answer 3

For Sql Server might want to look at the pivot feature. Heres a sample that matches your scenario and outputs the results you wanted...

declare @data table (CASEID int, FORMNAME varchar(20), NAME varchar(20), VALUE varchar(20))
insert into @data values
(601554, 'CASEFORM', 'Mond', 'AAA'),
(601554, 'CASEFORM', 'Tues', 'BBB'),
(601554, 'CASEFORM', 'Wedn', 'CCC'),
(601554, 'CASEFORM', 'Thur', 'DDD')

SELECT *
FROM @data
PIVOT
(
  MAX(VALUE)
  FOR [NAME] IN ([Mond],[Tues],[Wedn],[Thur])
)
AS data

Answer 4

If you don't know exact number of columns you can use this:

DECLARE @columns varchar(max)
DECLARE @query VARCHAR(max)
SELECT @columns = COALESCE(@columns + ',[' + cast([Name] as varchar(100)) + ']', 
                 '[' + cast([Name] as varchar(100))+ ']')           
    FROM   @data1 

SET @query = 'SELECT * FROM @data1 '
SET @query = @query  + '            
            PIVOT
            (
                MAX(VALUE) FOR [NAME] IN (' + @columns + ')
            )
             AS p'                                  

EXEC @query