SQL How to create a table with values ​​in the form of columns and a new format?

Question

I have a table that currently looks something like this:

CASEID ¦ FORMNAME ¦ NAME ¦ VALUE 601554 ¦ CASEFORM ¦ Mond ¦ AAAA 601554 ¦ CASEFORM ¦ Tues ¦ BBBB 601554 ¦ CASEFORM ¦ Wedn ¦ CCCC 601554 ¦ CASEFORM ¦ Thur ¦ DDDD 

Now I want to create a new table in SQL that will copy the data and completely change its format as follows:

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

The source table contains about 400 rows, so 400 columns are needed for the new table.

My knowledge of SQL is certainly limited, but I can always make a mistake when I need it. However, in this case, I don’t even know where to start. 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 automatic 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 define a metamodel.

What you need to do is loop in your METAMODEL table and create a custom ALTER statement for each row found that matches your MODEL column definition.

I also see that you also want to convert the values. If so, create the DML statement on the fly also in the same loop and execute them later so that your target model is complete.

References:

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

mysql cursors: 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 instructions needed to manually add these columns. You can use this as a model to create the next query to add values ​​to the table later.

Rgds.

Answer 3

For Sql Server, you may need to look at the reference function. Here is an example that matches your scenario and displays 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 do not know the 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 

Answer 5

IMHO, this is really a strange requirement! Saying you need a consolidated query. Details can be found at Ask Tom - Pivot Query