February 19, 2010 at 7:59 am
I have a header column I am trying to join with two rows from a table of fields and make one row. The header is basically just:
HeaderID as int
Header as varchar
...
Some data fields
...
The field table is basically:
FieldID as int
HeaderID as int
FieldType as varchar
FieldValue as varChar
I am looking to join the header column with two field columns with specific fieldTypes into one row. Anybody know how to do this with a join.
e.g.:
HeaderRec:
1, "I am a header"
FieldRecs:
1, 1, "0001", "Some value"
2, 1, "0002", "Some other value"
Results I want:
1, "I am a header", "Some value", "Some other value"
Thanks for any help.
February 19, 2010 at 8:03 am
Nevermind, I figured it out, I can just subquery in the select statement and not try to use a join.
February 19, 2010 at 10:43 pm
DECLARE @Header
TABLE (
header_id INTEGER IDENTITY PRIMARY KEY,
data VARCHAR(50) COLLATE LATIN1_GENERAL_CI_AS NOT NULL
);
DECLARE @Field
TABLE (
field_id INTEGER IDENTITY PRIMARY KEY,
header_id INTEGER NOT NULL,
data VARCHAR(50) COLLATE LATIN1_GENERAL_CI_AS NOT NULL
);
INSERT @Header (data) VALUES ('Header 1');
INSERT @Header (data) VALUES ('Header 2');
INSERT @Field (header_id, data) VALUES (1, 'Field 1');
INSERT @Field (header_id, data) VALUES (1, 'Field 2');
INSERT @Field (header_id, data) VALUES (1, 'Field 3');
INSERT @Field (header_id, data) VALUES (2, 'Field 4');
INSERT @Field (header_id, data) VALUES (2, 'Field 5');
SELECT H.data + F.row_data
FROM @Header H
CROSS
APPLY (
SELECT ' ,' + F.data
FROM @Field F
WHERE F.header_id = H.header_id
FOR XML PATH('')
) F (row_data);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply