Select (master/detail) sort of

Question

Select (master/detail) sort of

roy.tollison

SSCommitted

Points: 1930
More actions
May 11, 2010 at 10:17 am

#146934

master table
mf1
mf2
mf3
mf4
mf5
detail table
df1
df2
df3
df4
master table data
'01',10,1,'BOB','FISHER'
'01',20,4,'JOHN','SMITH'
detail table data
'01',10,'CODE1','SIDE ALLY'
'01',10,'CODE2','FRONT YARD'
'01',10,'CODE3','PAVEMENT'
'01',20,'CODE3','SIDEWALK'
How would i query this to come back with results of
mf1,mf2,mf4,mf5,df4 (df3=CODE1),df4 (df3=CODE3)
all master records returned with the same field from detail but based upon different df3 value?
'01',10,'BOB','FISHER','SIDE ALLY','PAVEMENT'
'01',20,'JOHN','SMITH',null,'SIDEWALK'

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

The concept is called "CrossTab". A related article is referenced in my signature. For a dynamic number of columns please see the DynamicCrossTab article...

DECLARE @master TABLE(mf1 CHAR(2),mf2 INT,mf3 INT,mf4 VARCHAR(30),mf5 VARCHAR(30))

INSERT INTO @master

SELECT '01',10,1,'BOB','FISHER' UNION ALL

SELECT '01',20,4,'JOHN','SMITH'

DECLARE @detail TABLE(df1 CHAR(2),df2 INT,df3 VARCHAR(30),df4 VARCHAR(30))

INSERT INTO @detail

SELECT '01',10,'CODE1','SIDE ALLY' UNION ALL

SELECT '01',10,'CODE2','FRONT YARD' UNION ALL

SELECT '01',10,'CODE3','PAVEMENT' UNION ALL

SELECT '01',20,'CODE3','SIDEWALK'

SELECT

m.mf1,

m.mf2,

m.mf4,

m.mf5,

MAX(CASE WHEN d.df3= 'CODE1' THEN d.df4 ELSE NULL END) AS c1,

MAX(CASE WHEN d.df3= 'CODE3' THEN d.df4 ELSE NULL END) AS c3

FROM @master m

INNER JOIN @detail d ON m.mf1=d.df1 AND m.mf2=d.df2

GROUP BY m.mf1,m.mf2,m.mf4,m.mf5

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

roy.tollison SSCommitted Points: 1930 More actions · Answer 2

Thank you

I am reading up on crosstab now.

I really appreciate all your help and the extra guidance