Creating dynamic column names in a view

[wbodger]

You folks have been quite helpful, so here is another problem that I am trying to figure out. I am trying to figure out if I can use column data from one table or view to create the column names in another view. For instance, I have vwABC with columns

DName    VName     TName
F34_C62  PPT_F_HX  tblPart
F97_C35  PPT_C_HX  tblBio

Now I want to create a view

select tblPart.ppt_f_hx AS F34_C62, tblBio.ppt_c_hx AS F97_C35
from tblPart JOIN tblBio ON tblPart.pptid = tblBio.pptid

Except I want the F34_C62 and the F97_C35 to be pulled dynamically from vwABC where the link is on a combination of VName and TName and the value that I want is DName as the column name.

Does that make sense?

wb

 

[markros]

You would not be able to have a view with dynamic field names, but you can create your query dynamically and execute with with execute (@SQL) syntax.

PluralSight Learning Library

 

[wbodger]

So, if I wrapped it all in a stored procedure that created the view? Say I have a query that pulls the data from the view for the column names then loop thru that recordset creating the columns and then aliasing them from the variable populated as I iterate thru the recordset. Wouldn't that work?

 

[wbodger]

Something like

SQL = SELECT tablename+'.'+variablename as column, SAS_Variable from vwStatisticianVariable

set rs=Exec(sql)

select @sql2 = 'SELECT '

do while not rs.bof or rs.eof
set @ocol=rs(0)
set @cname=rs(1)

select @sql2 = @sql2 + @ocol+' AS ' + @cname + ','
loop

select @sql2 = @sql2 + 'getdate() AS AccessDate'
select @sql2 = @sql2 + 'FROM (SELECT * FROM dbo.vwRptFinalG) AS vwRptFinalG '
select @sql2 = @sql2 + '        JOIN (SELECT * FROM dbo.TBLCONSENT WHERE deleteflag=0 and entryflag=1) AS tblConsent  ON vwRptFinalG.Study_Participant_ID = tblConsent.Study_Participant_ID'
select @sql2 = @sql2 + '        JOIN (SELECT * FROM dbo.tblParticipantCancerType WHERE deleteflag=0) AS tblParticipantCancerType ON vwRptFinalG.Study_Participant_ID = tblParticipantCancerType.Study_Participant_ID'

Exec (@sql2)

I know my loop code is incorrect, I am running out of the office (again), but it seems like this should work perhaps inside of a sproc?

wb

 

[markros]

Yes, that's the idea. Check this blog post for clarification of this idea

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-get-information-about-all-databas

PluralSight Learning Library

 

[wbodger]

Realized I never posted any resolution for this question, so here is what I came up with.

use dbName

Declare @sql2 varchar(8000);
--Here you need to name the view you want to create
select @sql2 = 'Create VIEW dbo.vwName AS' + char(13)
select @sql2 = @sql2 + 'SELECT '

DECLARE @NewCol varchar(75), @SASName varchar(12), @formversion char(4)

-- This creates a very quick forward-only cursor to loop thru the recordset from the imported 
-- data to build the columns in the new view. SAS variable was added to vwStatisticianVariables
-- as an agreed upon naming convention that would allow easy lookup for the statisticians. As an
-- added bonus, this process will catch any duplicated rows

DECLARE VariableNames CURSOR FAST_FORWARD
FOR
SELECT ('['+rtrim(tblname)+'].['+rtrim(variablename)+']') AS NewCol, SAS_Variable, max(formversion) 
FROM vwStatisticianVariables
where (rtrim(tblname)+'.'+rtrim(variablename)) IN 
	(SELECT (rtrim(tblname)+'.'+rtrim(variablename)) FROM tblvwCancerTypesFields) --This is the table of imported tables and columns from Deanna
GROUP BY ('['+rtrim(tblname)+'].['+rtrim(variablename)+']'), SAS_Variable

OPEN VariableNames
FETCH NEXT FROM VariableNames
INTO @NewCol, @SASName, @formversion

WHILE @@FETCH_STATUS = 0

BEGIN

select @sql2 = @sql2 + @NewCol+' AS ' + @SASName + ','

FETCH NEXT FROM VariableNames
INTO @NewCol, @SASName, @formversion

END

CLOSE VariableNames

DEALLOCATE VariableNames

--Done with the cursor, now any derived columns and the joins. Not sure how nicely this part will automate.
--There are issues with the identified PKs, so as of now 3/14/12 This part is not automated.

select @sql2 = @sql2 + 'getdate() AS AccessDate '
select @sql2 = @sql2 + 'FROM (SELECT * FROM dbo.vwRptFinalG) AS vwRptFinalG '
select @sql2 = @sql2 + '		JOIN (SELECT * FROM dbo.TBLCONSENT WHERE deleteflag=0 and entryflag=1) AS tblConsent  ON vwRptFinalG.Study_Participant_ID = tblConsent.Study_Participant_ID'
select @sql2 = @sql2 + '		JOIN (SELECT * FROM dbo.tblParticipantCancerType WHERE deleteflag=0) AS tblParticipantCancerType ON vwRptFinalG.Study_Participant_ID = tblParticipantCancerType.Study_Participant_ID'

Exec(@sql2)

/*
Use this query or some derivative thereof to create this last piece of @sql2?
select tn.tbl_id, rtrim(driver.tblname)+'.'+rtrim(cd.variablename), pk_id
from (select distinct tblname from tblvwBiopsySubFields
) as driver 
	join dbo.tbltblname tn on driver.tblname=tn.tblname
	left join dbo.tbltblpk pk on tn.tbl_id=pk.tbl_id
	left join dbo.tblCDE cd on pk.pk_id=cd.cde_id
*/

The final piece of the solution really was not nicely automatable (nice word) given our table structures, but the rest of it nicely creates the views within our parameters. Thanks fr the help, folks!

wb