SQL View

Question

SQL View

frecal

Ten Centuries

Points: 1094
More actions
July 20, 2009 at 11:41 pm

#95567

I have been trying to work this out from some of the other requests but havnt had much luck so thought id post another one.
I have Name table with id,fname,surname
and a relationship table with id, relatedto, relationtype
with a one to many relationship. name can have multiple relationships
can i do a view that displays
id, surname, relatedto, relationtype, relatedto,relationtype, etc depending on how many there are
TIA
Fred

Viewing 10 posts - 1 through 9 (of 9 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

frecal (7/20/2009)
can i do a view that displays
id, surname, relatedto, relationtype, relatedto,relationtype, etc depending on how many there are

You can't do it with a view: it must have a fixed number of columns.

You could do it with a stored procedure with dynamic sql and sp_executesql.

Hope this helps

Gianluca

-- Gianluca Sartori

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

In May 2009,Adam Machanic had a contest to develop the most efficient solution to a problem named "Grouped String Concatenation" that corresponds to your problem. The contest and solutions are at http://sqlblog.com/blogs/adam_machanic/archive/2009/05/31/grouped-string-concatenation-the-winner-is.aspx

Peter Larsson, SQL Server MVP, was the winner with Leonid Koyfman's solution a very close second.

The winning solution used a cross apply to a nested query that included a "FOR XML PATH('')" specification. Here is the solution applied to your problem:

First, create the tables and add some rows:

CREATE TABLE Person

(PersonIdINTEGER NOT NULL

, PersonName VARCHAR(255) NOT NULL

, CONSTRAINT Person_PK PRIMARY KEY ( PersonId )

)

GO

CREATE TABLE PersonRelationship

(PersonId_FromINTEGER NOT NULL

,PersonId_ToINTEGER NOT NULL

,PersonRelationshipNameVARCHAR(8) NOT NULL

, CONSTRAINT PersonRelationship_PK PRIMARY KEY (PersonId_From , PersonId_To )

)

GO

INSERT INTO Person

VALUES (1,'John' ), (2,'James') , ( 3 , 'Peter' ),( 4 ,'Andrew'),(5,'Philip')

GO

INSERT INTO PersonRelationship

VALUES( 1 , 2 , 'Friend')

,( 1 , 3 , 'Friend')

,( 1 , 4 , 'Enemy')

,( 2 , 3 , 'Friend')

,( 2 , 4 , 'Friend')

,( 2 , 5 , 'Enemy')

GO

SQL to build a string concatenation of the personrelationships

SELECTPerson.PersonId

,Person.PersonName

,STUFF(PersonRelationship.PersonRelationshipXML, 1, 1, '') AS PersonRelationshipArray

FROMdbo.Person

CROSS APPLY(

SELECT';' + CAST(PersonRelationship.PersonId_To AS VARCHAR(255))

+ ',' + PersonRelationshipName

FROMPersonRelationship

WHEREPersonRelationship.PersonId_From = Person.PersonId

ORDER BYPersonRelationship.PersonId_From

FOR XMLPATH('')

) AS PersonRelationship ( PersonRelationshipXML )

The result is:

PersonId PersonName PersonRelationshipArray

1 John 2,Friend;3,Friend;4,Enemy

2 James 3,Friend;4,Friend;5,Enemy

3 Peter NULL

4 Andrew NULL

5 Philip NULL

SQL = Scarcely Qualifies as a Language

frecal Ten Centuries Points: 1094 More actions · Answer 3

Fantastic, will have a go with my real data tomorrow

thanks again

Fred

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 4

frecal (7/20/2009)
I have been trying to work this out from some of the other requests but havnt had much luck so thought id post another one.
I have Name table with id,fname,surname
and a relationship table with id, relatedto, relationtype
with a one to many relationship. name can have multiple relationships
can i do a view that displays
id, surname, relatedto, relationtype, relatedto,relationtype, etc depending on how many there are
TIA
Fred

Fred... is this supposed to be hierarchical in nature? Or just a single level for each relationship?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frecal Ten Centuries Points: 1094 More actions · Answer 5

I am using it as one to many ..one parent can have multipe relationships.

the query only really will indicate that the record has a relationship with another record in the table.

thanks

Fred

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 6

frecal (7/22/2009)
I am using it as one to many ..one parent can have multipe relationships.
the query only really will indicate that the record has a relationship with another record in the table.
thanks
Fred

Yep... I read that about the multiple relationships... I just wanted to make sure that you weren't looking for something like this ...

[font="Courier New"] 1

/

2 3

/ \ /

4 5 6 7[/font]

... where the line for item 1 would contain the entire downline of the tree including 2,3,4,5,6,7

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Carl Federl One Orange Chip Points: 25384 More actions · Answer 7

Jeff Moden's comment got me thinking about a similar question.

Based on the business rules, if "Peter" considers "Paul" a "friend" , does this mean always mean the reverse is true ? e.g. "Paul" considers "Peter" a "friend"? This would be a b-directional relationship.

Alternatively, could there be one row for "Peter" considers "Paul" a "friend" but a different row that indicates "Paul" considers "Peter" an "Enemy" ?

SQL = Scarcely Qualifies as a Language

frecal Ten Centuries Points: 1094 More actions · Answer 8

Hi Jeff

that would be the correct way of recording it but probably over kill for what i am needing it for.

I am working on a data linkage project and just wanted a flag which indicates that 2 records are different even though the matching program gives them a high weight based on matching algorithms.

cheers

Fred

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 9

frecal (7/22/2009)
Hi Jeff
that would be the correct way of recording it but probably over kill for what i am needing it for.
I am working on a data linkage project and just wanted a flag which indicates that 2 records are different even though the matching program gives them a high weight based on matching algorithms.
cheers
Fred

Then, I think the code that Carl posted will probably do the trick for you with the understanding that all the next-level relationships will be concatenated into a single column.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)