Retrieving data from other tables, using row data as fields

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Retrieving data from other tables, using row...

Retrieving data from other tables, using row data as fields

Rate Topic

Display Mode

Topic Options

Author

Message

kcbjcb.brown

Posted Friday, January 04, 2013 6:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 04, 2013 12:35 PM
Points: 6, Visits: 14

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

Post #1402856

GSquared

Posted Friday, January 04, 2013 7:07 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Post #1402859

kcbjcb.brown

Posted Friday, January 04, 2013 7:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 04, 2013 12:35 PM
Points: 6, Visits: 14

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

Post #1402864

GSquared

Posted Tuesday, January 08, 2013 7:30 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Post #1404239

« Prev Topic | Next Topic »

Permissions