June 28, 2012 at 5:14 am
Update:
Hi, for those who doesn't want to know everything about my code đ Here's the issue:
This is the code I'm using:
Declare @WhichOne int;
Declare @sql varchar(200);
Set @WhichOne = 1;
With cte As
(Select name, Row_Number() Over (Order By column_id) As rn
From sys.columns
Where Object_Name(object_id) = 'V_Cycle_status')
Select @sql = 'Select '+ QuoteName(name) + QuoteName(name) +' From V_Cycle_status'
From cte
Where rn = @WhichOne
Exec(@Sql);
The above code works great but it only brings back the first column. I need it to bring back 5 columns. Is this possible?
Original post (More detailed):
I'm doing a report to verify that data was migrated correctly with a software upgrade (So I'm checking that Required data has been entered. If not then It should display on this report.)
So I managed to get everything I needed. This is my code so far:
-- Get the correct column names
SELECT rg.name + '_' + ra.name + '_Code' as ColumnName
Into #Results
FROM [DomeProduction].[dbo].[ReferenceGroupAttribute] ra join
[DomeProduction].[dbo].[ReferenceGroup] rg on rg.ID = ra.ReferenceGROUP_id
Where IsRequired = '1'
Group by rg.name + '_' + ra.name + '_Code'
-- Get table name where column exist
select Row_Number() over (order by Table_Name) as id, Table_Name ,Column_Name
Into #Tables
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME in (Select * From #Results)
-- While look so that I can get the data for each column that doesn't have the Required Fields data entered
DECLARE @Count AS INT
Declare @id int
SET @Count = (SELECT COUNT(id) FROM #Tables) +1
set @id = 1
While @id <> @count
Begin
Declare @Query nVarChar(200)
Declare @Table nVarChar(100)
Declare @ColumnName nVarChar(100)
set @Table = (select Table_Name from #Tables Where id = @id)
set @ColumnName = (select Column_Name from #Tables Where id = @id)
set @Query = 'select * from ' + @Table + ' Where [' + @ColumnName + ']' + ' IS NULL'
exec sp_executesql @Query-- Insert Into #Final
Set @id = @id + 1
End
That works perfect. My issue is that I want to get the first 5 columns of the tables in the query above
this part:
set @Query = 'select * from ' + @Table + ' Where [' + @ColumnName + ']' + ' IS NULL'
You can see it selects everything at the moment. Because the column names are different I can't get them all into one table to run the report (Crystal Reports.) So I want to say
Select ColumnIndext(1,2,3,4,5).....
I did find this and it is working but only brings back 1 column and not 5:
Declare @WhichOne int;
Declare @sql varchar(200);
Set @WhichOne = 1;
With cte As
(Select name, Row_Number() Over (Order By column_id) As rn
From sys.columns
Where Object_Name(object_id) = 'V_Cycle_status')
Select @sql = 'Select '+ QuoteName(name) + QuoteName(name) +' From V_Cycle_status'
From cte
Where rn = @WhichOne
Exec(@Sql);
How can I change it to bring 5 columns back and not just 1?
ps: Using SQL 2008
June 28, 2012 at 6:34 am
I think (am not 100 percent sure) that this shows how you can do it:
create table test (a int, b int, c int, d int, e int);
go
insert into test values (11,22,33,44,55);
go
declare
@sql nvarchar(max);
set @sql = N'select ';
with CTE as (
select name, row_number() over (order by column_id) as ColNo
from sys.columns where object_id = object_id('test')
)
select @sql = @sql + quotename(name) + ' ' + quotename(name) + N', ' from CTE
where ColNo in (1,3,5);
set @sql = substring(@sql,1,len(@sql)-1)+ ' from test';
exec sp_executesql @sql;
drop table test
June 28, 2012 at 6:54 am
Hi man,
ok I just changed your test to tt (using test for something else hehe) but looks like this:
create table tt (a int, b int, c int, d int, e int);
go
insert into tt values (11,22,33,44,55);
go
declare
@sql nvarchar(max);
set @sql = N'select ';
with CTE as (
select name, row_number() over (order by column_id) as ColNo
from sys.columns where object_id = object_id('tt')
)
select @sql = @sql + quotename(name) + ' ' + quotename(name) + N', ' from CTE
where ColNo in (1,3,5);
set @sql = substring(@sql,1,len(@sql)-1)+ ' from tt';
exec sp_executesql @sql;
drop table tt
Using that I do get an error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
June 28, 2012 at 9:31 am
What version of SQL Server are you using? I've successfully tested the code both on SQL Server 2008 R2 and SQL Server 2012.
Edit:
Paste all the code into a blank SQL Server Management studio, and just hit F5 to execute it all. If you attempt to execute the select without having created the temp table and populated it with data, you'll get this error message.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply