January 15, 2004 at 10:15 am
Hello
I have the following situation. I need to put this result in a single line.
For example: The result of the should be.
Claim ID Cust Name Entry Date Reports Printed
---------------------------------------------------
555555 XXXXXX 1/5/2003 1,2,3
Where the record on one of the table contain A for the first report printed
and B for second report printed and C for the third.
The problem is that is it does not group up.
Example:
Claim ID Cust Name Entry Date Reports Printed
---------------------------------------------------
555555 XXXXXX 1/5/2003 1
555555 XXXXXX 1/5/2003 2 555555 XXXXXX 1/5/2003 3
This is my script on the SQL:
SELECT Claim.Claim_ID,
Cust_Name,
Entry_Date,
(CASE report_type when 'A' then '1' else '' end) as A,
(CASE report_type when 'B' then '2' else '' end) as B, (CASE report_type when 'B' then '3' else '' end) as C,
FROM Claim,
report_log
WHERE (Claim.claim_id *= report_log.claim_id
GROUP BY Claim_ID
Cust_Name,
Entry_Date,
ORDER BY Claim.Claim_ID
Claim ID Cust Name Entry Date Reports Printed
---------------------------------------------------
555555 XXXXXX 1/5/2003 1
555555 XXXXXX 1/5/2003 2 555555 XXXXXX 1/5/2003 3
I need:
Claim ID Cust Name Entry Date Reports Printed
---------------------------------------------------
555555 XXXXXX 1/5/2003 1,2,3
By the way, there could be more than just 1,2,3.
Thanks in Advance
January 15, 2004 at 2:21 pm
You can create a UDF for returning the third column. The UDF would accept claimID as the parameter and would loop to create a string separated with commans.
e.g. SELECT Claim.Claim_ID,Cust_Name,Entry_Date,UDF(CLaimID)
group by Claim.Claim_ID,Cust_Name,Entry_Date
Hope this helps.....
You have to dig for tons of dirt to get an ounce of Gold
January 15, 2004 at 4:05 pm
Try adapting this bit of skeleton code .... should be OK providing your tables are small enough.
declare @t1 table (claim char(1), cust char(1), report char(1))
insert into @t1 values ('a','x','1')
insert into @t1 values ('a','x','2')
insert into @t1 values ('a','x','3')
insert into @t1 values ('a','y','1')
insert into @t1 values ('a','y','2')
select * from @t1
declare @t2 table (claim char(1), cust char(1), reports varchar(50)) declare c1 cursor for select distinct claim, cust from @t1 declare @claim char(1), @cust char(1), @reports varchar(50)
open c1 fetch c1 into @claim, @cust while @@fetch_status = 0 begin set @reports = null select @reports = coalesce(@reports+',','') + report from @t1 where claim = @claim and cust = @cust order by report insert into @t2 values (@claim, @cust, @reports) fetch c1 into @claim, @cust end close c1 deallocate c1
select * from @t2
January 19, 2004 at 3:58 am
creating a UDF for returning several rows into one row
Somebody posted this nice solution earlier:
create function ValList ( @anID VARCHAR(5) ) returns varchar(500) as
BEGIN
DECLARE @retval VARCHAR(500)
SET @retval = ''
SELECT @retVal = @retval + CASE WHEN @retVal = '' THEN '' ELSE ',' END + myVal
FROM TestMe (NOLOCK) WHERE myID = @anID
RETURN @retVal
END
select DISTINCT myID, dbo.ValList( myID )
from TestMe
January 19, 2004 at 7:05 am
This solution uses a loop instead of a function
CREATE TABLE #temp (Claim_ID, Cust_Name, Entry_Date, ReportsPrinted)
INSERT INTO #temp
SELECT DISTINCT Claim_ID,Cust_Name,Entry_Date,NULL
FROM Claim
DECLARE @report_type char(1)
SELECT @report_type = MIN(report_type)
FROM report_log
WHILE (@report_type IS NOT NULL)
BEGIN
UPDATE t
SET ReportsPrinted = COALESCE(ReportsPrinted+','+r.report_type,r.report_type)
FROM #temp t
INNER JOIN report_log r
ON r.Claim_ID = t.Claim_ID
AND r.report_type = @report_type
SELECT @report_type = MIN(report_type)
FROM report_log
WHERE report_type > @report_type
END
SELECT * FROM #temp
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply