I have a query that I am trying to write that will get demographic
information from one table and then get journal entries from another
table. However, I want all the journal entries to be concatenated
together so in the end I only have one record for each person.
Any ideas?
Thanks!!use coalesce|||I tried the COALESCE function. It still returns mutiple rows for each
record.|||This is what I am dealing with:
Demographics Table
Person (PK)
Name
Address
City
State
Journal Table
Person (FK)
Journal
Each person can have only one record in Demographics. But there may be
multiple records for each person in Journal. I need to take all the
records from Journal, match them up to the records in Demographics and
then output it all as one record per person.
Thanks for the help.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
multiple records[sic] for each person in Journal.<<
What you attempted to post makes no sense. Rows are not anything like
records, But ignoring that, if this "person_<something>" is a key,
then BY DEFINITION there can be multiple rows per person in the Journal
table.
If I understand what you mean, that is called violating First Normal
Form and we do not do this in an RDBMS. You want to use a 1950's COBOL
file system with variant records and an OCCURS clause.|||What CELKO means to say is you can do this in the application rather than
the database. Applications work well for these, not so much with database
queries.
If you are on SQL 2005 you can use the XML functionality to concatenate the
values together. Do a search in this newsgroup for XML and concatenation,
and you'll find a neat trick that makes this very simple.
If you are not on 2005 yet, I think you are stuck with a cursor, which can
be written simpler and more efficiently in most programming languages.
"CJEN" <christopher.t.davis@.gmail.com> wrote in message
news:1146667896.994197.34100@.u72g2000cwu.googlegroups.com...
> This is what I am dealing with:
> Demographics Table
> Person (PK)
> Name
> Address
> City
> State
> Journal Table
> Person (FK)
> Journal
> Each person can have only one record in Demographics. But there may be
> multiple records for each person in Journal. I need to take all the
> records from Journal, match them up to the records in Demographics and
> then output it all as one record per person.
> Thanks for the help.
>|||I might have misunderstood what you're trying to do, but what about
something like this:
CREATE TABLE People(personid int, name varchar(50))
CREATE TABLE Journals(entrydate datetime, PersonID int, entry
varchar(200))
INSERT INTO People
SELECT 1, 'Rod'
UNION ALL
SELECT 2, 'Jane'
UNION ALL
SELECT 3, 'Freddy'
INSERT INTO Journals
SELECT '2006-04-02', 1, 'Up above the streets and houses'
UNION ALL
SELECT '2006-04-01', 1, 'etc...'
UNION ALL
SELECT '2006-04-06', 2, 'I think Rod and Freddy are conspiring against
me'
UNION ALL
SELECT '2006-04-07', 2, 'I must strike first...'
GO
CREATE FUNCTION GetJournal(@.PersonID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.TotalJournal varchar(8000)
SET @.TotalJournal = ''
SELECT @.TotalJournal = @.TotalJournal + ', ' + Entry FROM Journals
WHERE PersonID = @.PersonID
RETURN @.TotalJournal
END
GO
SELECT PersonID, Name, dbo.GetJournal(PersonID)
FROM People
DROP FUNCTION GetJournal
DROP TABLE Journals
DROP TABLE People
GO
