some dynamic sql - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

some dynamic sql

Author

Topic

uv2944875
Starting Member

5 Posts

Posted - 2012-11-27 : 13:59:09

Suppose I have table T(A,B). A tuple t in this table t = (a,b) means that column b is in table a, i.e., there exists a table a in the database, with the schema a(b,...).

How can I query column b in table a, for all the tuples t in T, when T itself is the result of a select?

Thanks

Update. A small example.

Suppose I have table x(y) with tuples {1,2,3} and table c(d,e) with tuples{(4,100),(5,100)}. In a select query (with some filters) I obtain table T(a,b) with tuples {(x,y),(c,d)}. I'd like to query this table T (in the same select where I obtained T) to get a final table with schema(z) and tuples {(1),(2),(3),(4),(5)}.

Assume same column type everywhere, no worries about that.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-27 : 14:19:39

I don't speak college professor, but you can query a query, it's called a derived table, if this is what you're after.

SELECT <some columns from a>,<some columns from b>
FROM
( select <some other stuuf>
from someTable
) a
INNER JOIN
( select <some other stuuf>
from someOtherTable
) b
ON a.someColumn = b.SomeColumn

jim

Everyday I learn something that somebody else already knew

uv2944875
Starting Member

5 Posts

Posted - 2012-11-28 : 05:29:45

I don't think this is it. The content of a tuple, i.e., the values for columns a and b in table T, is now used both in the SELECT and FROM clauses of a select query. I think it's smth to do with dynamic sql.

Mike Jackson
Starting Member

37 Posts

Posted - 2012-11-28 : 08:34:53

Perhaps you could supply us a data sample, an example of how it is normally solved and expected result.

Mike

Elizabeth B. Darcy
Starting Member

39 Posts

Posted - 2012-11-28 : 09:03:44

The only way to do this in Transact SQL is using dynamic queries. For the example you posted, you would construct the query string like in the example below:

CREATE TABLE #t(tbl VARCHAR(32), col VARCHAR(32));
INSERT INTO #t VALUES ('x','y'),('c','d');

DECLARE @sql NVARCHAR(4000);

SET @sql = 
STUFF(
	(
		SELECT 
			' UNION ALL SELECT ' + QUOTENAME(col) + ' FROM ' + QUOTENAME(tbl)
		FROM 
			#t 
		FOR XML PATH('')
	) ,1,11,'')
EXEC( @sql );

DROP TABLE #t;