January 4, 2013 at 6:56 am
I have a temp table that I created that has the field name and table name that I want to pull the data from. I have other tables that correspond to these tables with these fields. I want to be able to scan through the table that has these different fields and pull the data from the other tables. Below is a sample of how I got the data into the table that has that data. I want to be able to query the 'table_name' value using the 'field_name' along with other tables that will be joined regardless of what is in this cte data. Not sure what my best approach would be.
;WITH field_cte (table_name,field_name,exporttype,rank)
AS
(
SELECT table_name,CAST(field_name AS VARCHAR(8000)),exporttype,ROW_NUMBER() OVER (PARTITION BY table_name,exporttype ORDER BY field_name)
FROM #configfields
WHERE LEN(LTRIM(RTRIM(table_name))) > 0 AND LEN(LTRIM(RTRIM(field_name))) > 0
GROUP BY table_name,field_name,exporttype
),
AnchorField (table_name,field_name,exporttype,rank)
AS (
SELECT table_name,LTRIM(RTRIM(field_name)) AS field_name,exporttype,rank
FROM field_cte
WHERE rank = 1
),
Recur_Field (table_name,field_name,exporttype,rank)
AS (
SELECT table_name,field_name,exporttype,rank
FROM AnchorField
UNION ALL
SELECT field_cte.table_name,LTRIM(RTRIM(Recur_field.field_name)) + ', ' + LTRIM(RTRIM(field_cte.field_name)),field_cte.exporttype,field_cte.rank
FROM field_cte
INNER JOIN Recur_Field on field_cte.table_name = recur_field.table_name and field_cte.exporttype = recur_field.exporttype
AND field_cte.rank = recur_field.rank + 1
/*AND field_cte.rank > Recur_field.rank*/
)
SELECT table_name,MAX(field_name) AS field_list,SPACE(4000) AS field_sql,exporttype INTO #fieldlist
FROM Recur_field
GROUP BY table_name,exporttype
ORDER BY table_name,exporttype
January 4, 2013 at 7:07 am
Concatenate the list of columns into a string, use that in dynamic SQL. It's the only way to to do what you're trying.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2013 at 7:15 am
Thats what Im not quite sure how to do. I issued an update below that to populate the field_sql field with field and table
UPDATE #fieldlist SET field_sql = 'SELECT ' + field_list + ' FROM ' + table_name
Now I have the #fieldlist table with the field_sql populated. I am not sure how to run through this table where I can use the field_sql value that can be joined to other sql statements.
For example..I have 'select city from demospan' in the field_sql field that I want to be able to join to the below query. demospan has patient_id as an foreign key...
select patient_id,last_name FROM patient
INNER JOIN #patient pat ON patient.patient_id = pat.patient_id
January 8, 2013 at 7:30 am
I guess I'm not clear on what end result you are trying to achieve here. Can you clarify that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply