Concatenate child data values without functions/procedures

Hi,

I have a table:
REFERRALCASE
RefCaseId (PK)   Data1    Data2, etc (various columns)
1                      AAA       AAA
2                      BBB        BBB
3                      CCC       CCC

and table:
REFCASECOMMENT
RefCaseId (FK)    Comment (varchar)
1                        Comment1
1                        Comment2
2                        Comment3
3                        Comment4
3                        Comment5
3                        Comment6

I need to select and get results like this:
RefCaseId   Data1   Data2   Comment(s)
1                AAA     AAA     Comment1.  Comment2.
2                BBB      BBB      Comment3.
3                CCC     CCC     Comment4.  Comment5.  Comment6.

I've seen all the posts that suggest using UDFs, etc.  The problem is I can't use functions or SPs because I only have read access to the db/server.  So - is there a way to do this in a query only?

Thanks,
Ian
LVL 10
ibostAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Probably not, certainly nothing that would perform well.

You should have access to the tempdb database.  Can you create the function there? :-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ibostAuthor Commented:
Hmm let me try it out.
0
ibostAuthor Commented:
ugh didn't work:

Server: Msg 262, Level 14, State 1, Procedure ib_GetRefCaseComments, Line 18
CREATE FUNCTION permission denied in database 'tempdb'.

If I talk to the IT guys, is it just a matter of giving me permission in tempdb?  Do I need to worry much about cleaning up anything I create out there?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

derobyCommented:
I was wondering, why try to do it in SQL ? Given the restrictions it seems to me that you might as well do it client-side.

Make your query return this

RefCaseId   Data1   Data2   Comment(s)
1                AAA     AAA     Comment1.
1                AAA     AAA     Comment2.
2                BBB      BBB      Comment3.
3                CCC     CCC     Comment4.
3                CCC     CCC      Comment5.
3                CCC     CCC      Comment6.

=> All you need to do is loop through the recordset more or less like this
* read first record + store refcaseid variable, store data1 variable, store data2 variable, store comment variable
* read next record
    + if RefCaseId  equals refcaseid variable, then add this comment to the already stored comment
    + if RefCaseID does not equal the stored refcaseid variable then do whatever is needed with the stored variables, re-init variables and store current record in it
Loop back to read next record for as long as needed

Off course, a lot depends on the client-env you're working in.

Seems to me, the overhead of duping the id & data-fields is a lot less trouble versus finding a way around the sql limitations


0
Scott PletcherSenior DBACommented:
That makes good sense, especially since the number of rows *actually seen* by the user should be small (don't do the concatenation until that row is to be displayed on the screen).
0
ibostAuthor Commented:
Unfortunately my "environment" is:

1)  Run query in Query Analyzer.
2)  Cut/Paste results to Excel.
3)  Format Excel tables all pretty for upper management.
4)  Create any needed charts because reading tables of data is too hard on upper management's eyes.
5)  Cut/Paste pretty charts to Powerpoint slides because upper management likes PowerPoint slide shows.

If this were a recurring report I'd eventually try and get it into Reporting Services.  As it is, this is one of those Run Once types that I will never look at again (but will save the query just in case...)

I'm open to more efficient ideas tho.
0
derobyCommented:
Hmm, well, I've come up with this... not sure it's the best way to do this kind of stuff, nor how it will react on bigger data-sets, but you migh want to give it a try.
Some tweaking will be needed
=> for starters you'll need a column that adds 'sequence' to the comments table. If that's impossible, then you will first have to copy the comments into a temptable like this :

SELECT seqnr = IDENTITY(int, 1,1), * INTO #REFCASECOMMENT FROM REFCASECOMMENT

and work from that temp-table

The script will create a 'new script' stored in a table, and then has that executed using sp_execresultset

Hope it helps you out...  
(the first lines are simply there to create a test databse and some test-data)
-----------------------------------------

-- create test-env
-- DROP DATABASE test
CREATE DATABASE test
go
-- create test-data
USE test

CREATE TABLE REFERRALCASE (RefCaseId int NOT NULL
                            CONSTRAINT pk_REFERRALCASE
                            PRIMARY KEY (RefCaseId),
                           Data1     varchar(100) NOT NULL,
                           Data2     varchar(100) NOT NULL)

INSERT REFERRALCASE VALUES (1, 'AAA', 'AAA')
INSERT REFERRALCASE VALUES (2, 'BBB', 'BBB')
INSERT REFERRALCASE VALUES (3, 'CCC', 'CCC')

CREATE TABLE REFCASECOMMENT (RefCaseId int NOT NULL
                                CONSTRAINT fk1_REFCASECOMMENT
                                FOREIGN KEY (RefCaseId) REFERENCES REFERRALCASE (RefCaseId),
                             seqnr     int NOT NULL,
                                CONSTRAINT pk_REFCASECOMMENT
                                PRIMARY KEY (RefCaseId, seqnr),
                             comment varchar(8000) NOT NULL)

INSERT REFCASECOMMENT VALUES (1, 1, 'comment 1')
INSERT REFCASECOMMENT VALUES (1, 2, 'comment 2')
INSERT REFCASECOMMENT VALUES (2, 1, 'comment 3')
INSERT REFCASECOMMENT VALUES (3, 1, 'comment 4')
INSERT REFCASECOMMENT VALUES (3, 2, 'comment 5')
INSERT REFCASECOMMENT VALUES (3, 3, 'comment 6')

GO
-- actual fetch

-- I need to select and get results like this:
-- RefCaseId   Data1   Data2   Comment(s)
-- 1                AAA     AAA     Comment1.  Comment2.
-- 2                BBB      BBB      Comment3.
-- 3                CCC     CCC     Comment4.  Comment5.  Comment6.


CREATE TABLE #sql (row_id int IDENTITY(1, 1),
                   txt    varchar(8000))

TRUNCATE TABLE #sql


SET NOCOUNT ON

INSERT #sql (txt) VALUES ('USE ' + DB_NAME() + ' ')
INSERT #sql (txt) VALUES ('SELECT src.RefCaseId, src.Data1, src.Data2, Comments = ''''')
INSERT #sql (txt) SELECT DISTINCT ' + ISNULL(cmt' + Convert(varchar, seqnr) + '.comment, '''') ' FROM REFCASECOMMENT
INSERT #sql (txt) VALUES ('FROM REFERRALCASE src ')
INSERT #sql (txt) SELECT DISTINCT ' LEFT OUTER JOIN REFCASECOMMENT cmt' + Convert(varchar, seqnr) + ' ON (src.RefCaseId = cmt' + Convert(varchar, seqnr) + '.RefCaseId AND cmt' + Convert(varchar, seqnr) + '.seqnr = ' + Convert(varchar, seqnr) + ') ' FROM REFCASECOMMENT
INSERT #sql (txt) VALUES (' WHERE 1 = 2')


DECLARE @c_RefCaseId int

DECLARE id_loop CURSOR FOR SELECT RefCaseId FROM REFERRALCASE
OPEN id_loop
FETCH NEXT FROM id_loop INTO @c_RefCaseId
WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT #sql (txt) VALUES ('')
        INSERT #sql (txt) VALUES ('UNION ALL')
        INSERT #sql (txt) VALUES ('')
        INSERT #sql (txt) VALUES (' SELECT src.RefCaseId, src.Data1, src.Data2, Comments = ''''')
        INSERT #sql (txt) SELECT '       + cmt' + Convert(varchar, seqnr) + '.comment + ''. ''' FROM REFCASECOMMENT WHERE RefCaseId = @c_RefCaseId ORDER BY seqnr
        INSERT #sql (txt) VALUES ('  FROM REFERRALCASE src ')
        INSERT #sql (txt) SELECT '   JOIN REFCASECOMMENT cmt' + Convert(varchar, seqnr) + ' ON (src.RefCaseId = cmt' + Convert(varchar, seqnr) + '.RefCaseId AND cmt' + Convert(varchar, seqnr) + '.seqnr = ' + Convert(varchar, seqnr) + ') ' FROM REFCASECOMMENT WHERE RefCaseId = @c_RefCaseId ORDER BY seqnr
        INSERT #sql (txt) VALUES ( 'WHERE src.RefCaseId = ' + Convert(varchar, @c_RefCaseId))

        FETCH NEXT FROM id_loop INTO @c_RefCaseId
    END
CLOSE id_loop
DEALLOCATE id_loop
INSERT #sql (txt) VALUES (' ORDER BY 1')


-- SELECT * FROM #sql ORDER BY row_id

SET NOCOUNT OFF

EXEC master..sp_execresultset 'SELECT txt FROM #sql ORDER BY row_id'



0
imran_fastCommented:
Create function on your database
======================

CREATE FUNCTION YOURFUNCTIONNAME (@REFCASEID INT) RETURNS NVARCHAR(1000)
AS BEGIN
DECLARE @COMMENNT VARCHAR(100),
      @COMMNETS NVARCHAR(1000)
      SET @COMMENTS = ''
DECLARE C CURSOR FOR SELECT COMMENT FROM REFCASECOMMENT WHERE RefCaseId = @RefCaseId AND COMMENT IS NOT NULL FOR READ ONLY
OPEN C
FETCH NEXT FROM C INTO @COMMENT
      WHILE @@FETCH_STATUS = 0
            BEGIN
                  SET @COMMENTS = @COMMENTS + @COMMENT
                  FETCH NEXT FROM C INTO @COMMENT
                  IF @@FETCH_STATUS = =
                  SET @COMMENTS = @COMMENTS + ','
            END
CLOSE C
DEALLOCATE C
RETURN @COMMNETS
END
GO

then use this query
==============
select REFCASEID, DATA1, DATA2, DBO.YOURFUNCTIONNAME(REFCASEID) AS COMMENTS
FROM REFERRALCASE
GO
0
imran_fastCommented:
syntax error in previous post use this
========================
CREATE FUNCTION YOURFUNCTIONNAME (@REFCASEID INT) RETURNS NVARCHAR(1000)
AS BEGIN
DECLARE @COMMENT VARCHAR(100),
      @COMMENTS NVARCHAR(1000)
      SET @COMMENTS = ''
DECLARE C CURSOR FOR SELECT COMMENT FROM REFCASECOMMENT WHERE RefCaseId = @RefCaseId AND COMMENT IS NOT NULL FOR READ ONLY
OPEN C
FETCH NEXT FROM C INTO @COMMENT
      WHILE @@FETCH_STATUS = 0
            BEGIN
                  SET @COMMENTS = @COMMENTS + @COMMENT
                  FETCH NEXT FROM C INTO @COMMENT
                  IF @@FETCH_STATUS = 0
                  SET @COMMENTS = @COMMENTS + ','
            END
CLOSE C
DEALLOCATE C
RETURN @COMMENTS
END
GO
0
ibostAuthor Commented:
Ok well I just had IT create me a new database to scratch around in.  Politically, it was easier than asking the powers that be if I can write to the temp DB.  Once I got my own db to scratch in I was able to use a function after all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.