Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nitinag
Starting Member
20 Posts |
 Posted - 2003-09-25 : 15:57:23
|
| I need to display Rowvalues of a table as a columns and join to other table as the column values. I tried using dynamic sql but got struck on using the joins (because the columns(rows) are dynamic)here's the scenarioTable web_Asset_custom_field has field_id and fieldname + other dataTable web_Asset_custom_field_values has field_id ,field_value and asset_ID. I would like the result set to be asset_id field_id fieldname1 fieldname2....(all the rows)-------------------------------------------------1 1000 field_value1 fieldvalue2 ........2 1000 fv2 fv31 1001 fv3 fv3 please help thanks |
|
|
nitinag
Starting Member
20 Posts |
Posted - 2003-09-25 : 16:16:40
|
| I already tried the following dynamic sql but couldn't figure out yet what to put in the insert statement, if you guys have any clue let me know thanks alotSELECT @SQL = N'Declare @tblAsset TABLE ' SELECT @SQL = @SQL + '(asset_tag varchar(50) ' SELECT @SQL = @SQL + ', ' SELECT @ColumnsList=COALESCE(@ColumnsList + ',','') + field_name + ' VARCHAR(50) NULL' from dbo.web_Asset_custom_field a Where a.company_id=@company_id SELECT @SQL = @SQL + @ColumnsList SELECT @SQL = @SQL + ') '-----------------------couldn't figure out yet ------------ SELECT @SQL = @SQL + ' INSERT into @tblAsset ' SELECT @SQL = @SQL + ' SELECT '------------------------ SELECT @SQL = @SQL + ' Select * from @tblAsset' SELECT @SQL Execute sp_executesql @SQL |
 |
|
|
|
|
|
display Rowvalues as Column, Problems?