quote:

---

Originally posted by seany

Hi TG,

Ok, so I guess I will ask it in another way because I don't think I was clear enough in terms of what I am really after:

Let's say the scenerio is as follows:


Table1 has column “projects”

Table1
Projects
------------
Proja
Projb

Now there is also a table called “Proja” that has the
version numbers

Proja
VersionNumb
------------
V1.9
V1.8
V1.7


There is also a table called “Projb” that has the
version numbers

Projb
VersionNumb
-----------
V1.2
V1.1
V1.0



Can I run a select to give me the following output (I
just want the first 3 values in the table Proja or the Table
Projb). I need to first retrieve the value from
Table1 and then run the select on the Tables “Proja” or
“Projb”

Output:
Proja,V1.9,V1.8,V1.7
Projb,V1.2,V1.1,V1.0

---

SELECT t.Projects + ','
       CASE WHEN t.Projects ='Proja' THEN LEFT(pal.Projalist,LEN(pal.Projalist-1))
            WHEN t.Projects ='Projb' THEN LEFT(pbl.Projblist,LEN(pbl.Projblist-1))
       END

FROM Table t
CROSS APPLY (SELECT VersionNumb + ',' [AS text()]
             FROM Proja
             FOR XML PATH(''))pal(Projalist)
CROSS APPLY (SELECT VersionNumb + ',' [AS text()]
             FROM Projb
             FOR XML PATH(''))pbl(Projblist)

quote:

---

Originally posted by seany

I might be confusing everyone in the way i am asking the question. I have one table that contains a list of values which these values reference their own tables. So, I have to get the list of the values from that table and loop through each one of the values which represent a table and get the top 3 values in each one of those tables. In my example, there is the table called "table1" which consists of two rows (proja and projb) but the table can grow to have projc, projd, etc. So in total, with my example, I have 3 tables. I hope this makes sense so far. At this point, once I know what the values from table1 are, I will then need to iterate through. Does that make sense? There is no specific reason why I did it like that, but now, that is the way the tables are set and that is what I have to work with. I am hoping that there is a way to get the information I need extracted.

---

CREATE TABLE #ProjVersions
(
ID int IDENTITY(1,1)
Project varchar(20),
Version varchar(20)
)

INSERT INTO #ProjVersions(Project,Version)
SELECT 'Proja',Version from Proja
UNION ALL
SELECT 'Projb',Version from Projb
...........


SELECT t1.Projects + ',' + LEFT(vl.VersionList,LEN(vl.VersionList)-1)
FROM Table1 t1
CROSS APPLY (SELECT TOP 3 Version + ',' AS [text()]
             FROM #ProjVersions
             WHERE Project=t1.Projects
             FOR XML PATH(''))vl(VersionList)

quote:

---

I am trying to write a select statement that will return the values of two columns from two different tables in the same statement. I believe the join word needs to be used somewhere, but I am having difficulty with it.

---

use tempdb
set nocount on
----------------------------------------------------------------
--set upt the example
--You should have done this for us seany!!!

create table Table1 (Projects varchar(5))
insert table1 (projects)
select 'Proja' union
select 'Projb'


create table Proja (VersionNumb varchar(10), versionDate datetime)
insert proja (versionNumb, versionDate)
select 'V1.9', getdate() union 
select 'V1.8', getdate()-1 union
select 'V1.7', getdate()-2


create table Projb (VersionNumb varchar(10), versionDate datetime)
insert projb (versionNumb, versiondate)
select 'V1.2', getdate() union 
select 'V1.1', getdate()-1 union
select 'V1.0', getdate()-2
----------------------------------------------------------------
go

if object_id('dbo.PrjectVersions_select') > 0
	drop proc dbo.PrjectVersions_select
go

create proc dbo.PrjectVersions_select
as
set nocount on

create table #results
	(rowid int identity(1,1)
	,projects varchar(5)
	,versionsCSV varchar(30))

declare @csv varchar(30)
	,@sql NVarchar(200)
	,@sql2 NVarchar(200)
	,@params nVarchar(200)
	,@tbl varchar(50)

set @sql = '
	select @csv = coalesce(@csv + '', '' + versionNumb, versionNumb)
	from	(
		select top 3 versionNumb
		from	[tbl]
		order by versionDate desc
		) d
'
set @params = '@csv varchar(30) output'

--Get all [projects] from Table1 than exists as a table 
--and contains a column: [verionNumb]
insert	#results (projects)
select	t.projects
from	Table1 t
join	information_schema.columns c 
		on c.table_name = t.projects 
		and c.column_name = 'versionNumb'

--loop through all eligible proj tables
declare @rowid int
select @rowid = min(rowid) from #results
while @rowid is not null
begin
	select @tbl = projects from #results where rowid = @rowid

	set @sql2 = replace(@sql, '[tbl]', @tbl)
	exec sp_executesql @sql2, @params, @csv = @csv output

	update #results set
		versionsCSV = @csv
	from	#results
	where	rowid = @rowid

	select @rowid = min(rowid)
		,@csv = null
	from #results 
	where rowid > @rowid
end

--return results
select	projects
	,versionsCSV 
from	#results

go

--Here is your "one liner" to call from your app 
exec dbo.PrjectVersions_select

go
drop table Proja
drop table Projb
drop table Table1


OUTPUT:

projects versionsCSV                    
-------- ------------------------------ 
Proja    V1.9, V1.8, V1.7
Projb    V1.2, V1.1, V1.0