July 7, 2016 at 9:55 am
HI,
The following code I need assign the list of columns into new variable, but I am struggling to populate the result into variable, could any give their valuable suggestions.
I am passing this into variable because, I have another variable1 which has ' select standardcolumns, '
I want to put the following output into variable2.
Many Thanks in advance
with cte
as
(
select c.name as cname,t.name as tname,
case when count(*) over (partition by c.name) > 1 then 'Yes'
else 'No'
end as t,
row_number () over ( partition by c.name order by c.name) as tt
from
sys.tables t
inner join sys.columns c on t.object_id = c.object_id
inner join sys.schemas ss on t.schema_id = ss.schema_id
)
select distinct case when t = 'no' then ('[' + tname + ']' + '.' +'['+ cname +']' + ',' + char(13) + char(10) )
when t = 'Yes' then coalesce (('[' + (select top(1) tname from cte where tt = 1) + ']' + '.' + cname + ',' + char(13) + char(10)) , ((select top(1) tname from cte where tt = 2) + '.' + cname + ',' + char(13) + char(10)))
end
from cte
July 7, 2016 at 10:01 am
Sangeeth878787 (7/7/2016)
HI,The following code I need assign the list of columns into new variable, but I am struggling to populate the result into variable, could any give their valuable suggestions.
I am passing this into variable because, I have another variable1 which has ' select standardcolumns, '
I want to put the following output into variable2.
Many Thanks in advance
with cte
as
(
select c.name as cname,t.name as tname,
case when count(*) over (partition by c.name) > 1 then 'Yes'
else 'No'
end as t,
row_number () over ( partition by c.name order by c.name) as tt
from
sys.tables t
inner join sys.columns c on t.object_id = c.object_id
inner join sys.schemas ss on t.schema_id = ss.schema_id
)
select distinct case when t = 'no' then ('[' + tname + ']' + '.' +'['+ cname +']' + ',' + char(13) + char(10) )
when t = 'Yes' then coalesce (('[' + (select top(1) tname from cte where tt = 1) + ']' + '.' + cname + ',' + char(13) + char(10)) , ((select top(1) tname from cte where tt = 2) + '.' + cname + ',' + char(13) + char(10)))
end
from cte
I have read this entire thing 2-3 times and I just don't understand what you are trying to do. This code makes no sense to me at all and I don't understand what any of this has to do with a variable. Can you try to explain more clearly what you trying to accomplish?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 7, 2016 at 11:37 am
Hi,
I am trying to create view dynamically using sys.tables and sys.columns with filtering required tables.
For that I am using multiple variables.
Eg
Declare @varaiable nvarchar(max)
Declare @varaiable2 nvarchar(max)
Declare @varaiable2 nvarchar(max)
Declare @varaiable3 nvarchar(max)
select @variable1 = ' create view vw_FactTable as select * from fewcolumns'
select @variable3 = 'from tablenames a inner join table b on a.id = b.id....... '
select @variable 2 = (list of columns required), the query outputs the list of columns from corresponding tables A, B,C....
I am concatenating three variables and executing to create view
Select @ Variable = @Variable1+@varaibale2 + @variable3
Now I missing @variable2, which is result of the query
July 7, 2016 at 12:07 pm
Sangeeth878787 (7/7/2016)
Hi,I am trying to create view dynamically using sys.tables and sys.columns with filtering required tables.
For that I am using multiple variables.
Eg
Declare @varaiable nvarchar(max)
Declare @varaiable2 nvarchar(max)
Declare @varaiable2 nvarchar(max)
Declare @varaiable3 nvarchar(max)
select @variable1 = ' create view vw_FactTable as select * from fewcolumns'
select @variable3 = 'from tablenames a inner join table b on a.id = b.id....... '
select @variable 2 = (list of columns required), the query outputs the list of columns from corresponding tables A, B,C....
I am concatenating three variables and executing to create view
Select @ Variable = @Variable1+@varaibale2 + @variable3
Now I missing @variable2, which is result of the query
Why do you need to create views dynamically like this? How can you not know what needs to be in the view? Something here has a very bad code smell to it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply