Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Concatenate child data values without functions/procedures

Posted on 2006-04-17
10
Medium Priority
?
464 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:ibost
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 16472157
Probably not, certainly nothing that would perform well.

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

Author Comment

by:ibost
ID: 16472170
Hmm let me try it out.
0
 
LVL 10

Author Comment

by:ibost
ID: 16472272
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 11

Expert Comment

by:deroby
ID: 16472405
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16472444
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
 
LVL 10

Author Comment

by:ibost
ID: 16472619
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
 
LVL 11

Assisted Solution

by:deroby
deroby earned 1000 total points
ID: 16476191
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16476282
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16476290
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
 
LVL 10

Author Comment

by:ibost
ID: 16511442
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question