Hello

Probably pretty trivial and classic problem but I can't figure it
out...

I'd like to design a view to "flatten" a 1:n relation, where n is
unknown but say less than 100.

So I have

Parent  1:n  Child

Where Parent is defined like

Pidx (PK)
pTxt

and Chlid as

Chidx (PK)
ChPidx (link to Parent)
ChSeq
ChTxt

I'd like a view that returns one row per Pidx with

! Pidx ! Ptxt ! concat(ChTxt 1..n) !

How would you go about that ? Not sure it can be done in "pure" SQL
but I guess that some T-SQL will do... I'd like to avoid temporary
tables if possible...

Any help welcome

Regards

Alext

Hi

You could do this using multiple joins if you can differentiate each child,
this is ok if you only have a few repeates, otherwise if you are on SQL 2005
you can use the PIVOT operator, on SQL 2000 it is not possible without
dynamic SQL or temporary tables, therefore you can't create a view.

There are many posts on pivoting data, usually the best solution is to do
this on the client.

John
Show quoteHide quote

John

Yup, I can differeniate based on ChSeq.

What do you have in mind ?

What about a strored proc... ?

Thanks & regards

--alexT

Hi Alex

With a finite number of  children you can do something like:

CREATE TABLE tbl_Parents ( Pidx int not null, pTxt varchar(30) )

INSERT INTO tbl_Parents ( Pidx, pTxt )
SELECT 1, 'Parent 1'
UNION ALL SELECT 2, 'Parent 2'

CREATE TABLE tbl_Children ( Chidx int not null, ChSeq int not null, ChPidx
int not null, CHTxt varchar(30) )

INSERT INTO tbl_Children ( Chidx, ChSeq, ChPidx, CHTxt )
SELECT 3, 1, 1, 'Child 3'
UNION ALL SELECT 4, 2, 1, 'Child 4'
UNION ALL SELECT 5, 1, 2, 'Child 5'
UNION ALL SELECT 6, 2, 2, 'Child 6'
UNION ALL SELECT 7, 3, 2, 'Child 7'

SELECT * FROM tbl_Parents
SELECT * FROM tbl_Children

SELECT p.Pidx, p.pTxt, C1.Chidx, C1.ChSeq, C1.CHTxt, C2.Chidx, C2.ChSeq,
C2.CHTxt
FROM tbl_Parents p
LEFT JOIN tbl_Children C1 ON C1.ChPidx = p.Pidx AND C1.ChSeq = 1
LEFT JOIN tbl_Children C2 ON C2.ChPidx = p.Pidx AND C2.ChSeq = 1

If your child sequence number is not sequential for each parent, you will
need to rank the position.

Other other solutions can be found if you Google for PIVOT or CROSSTAB,
there was an article in SQL Server Magazine that summaries all the various
ways to pivot see http://www.sqlmag.com/Articles/ArticleID/15608/15608.html

John

Show quoteHide quote

Dynamic SQL and column-values
SQL 2005 slower than 2000?
XP_CMDSHELL Problem
validate statement before execute with sp_executesql
Re: Connections List
Other ways to run this query?
Trying to do a blog join (entry columns, and # of comments) without success
How do I.....?
Assign A Flag Value
**SET NULL**