CODEWITHSUNDEEP
sqlsql-server
RockOn
RockOn

Reputation: 197

How can I flip rows from one table into columns of a second table?

How can I take a row value from table FormField and create a column in table Registrant? Somehow it also needs to be smart enough to know if the column already exists.

Essentially I am flipping rows from the 1st table into columns on the 2nd table.

What I have right now was manually entered. However I need to code it to do this. Would this be a trigger or how would I even properly do this?

FormField table contains column ColumnName, one example of a row being FirstName.

enter image description here

Registrant table contains columns that should correspond, such as column FirstName

enter image description here

It needs to be "fool-proof" because if someone else enters FirstName into ColumnName, it shouldn't try to add another FirstName column. This also means it should reformat the string to work as a proper column (proper case, no spaces, etc).

Upvotes: 0

Views: 77

Answers (1)

Malk
Malk

Reputation: 11983

This will generate ALTER TABLE commands for missing fields. "Fool-proofing" should be done beforehand:

SELECT 'ALTER TABLE [Registrant] ADD [' + [ColumnName] + '] NVARCHAR(MAX);' 
FROM [FormField] f
        LEFT JOIN syscolumns c ON OBJECT_NAME(c.id) = 'Registrant' 
                              AND c.Name = f.[ColumnName]
WHERE c.id IS NULL

Upvotes: 2

Related Questions