CODEWITHSUNDEEP
sqlsql-server-2008
Craig C
Craig C

Reputation: 165

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

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?

Upvotes: 6

Views: 391

Answers (5)

Kevin
Kevin

Reputation: 7309

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)

Upvotes: 1

rs.
rs.

Reputation: 27417

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

Upvotes: 1

MVK
MVK

Reputation: 127

IMHO, that's a really strange requirement! Being said that, what you need is a pivot query. Details can be found at Ask Tom - Pivot Query

Upvotes: 0

Chris Moutray
Chris Moutray

Reputation: 18349

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

Upvotes: 1

Sebas
Sebas

Reputation: 21522

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.

Upvotes: 1

Related Questions