one record in one column. the rest concatenate into another column
-
I have the following info.
I need to update table OutPatientMedStat
from those records
PNO CPTCode
40000028 80061
40000028 83735
40000028 84100
40000028 84436
40000028 84443
40000028 84479
40000028 84480
40000028 85025
40000028 86617
40000028 86618
What I need to accomplish it. PUt one into PCPTCode, the rest I will Concatenate the other CPTCode and put into OtherPCPCode column.
In the vb, I need to loop through the record. I do not how to accomplish this in SQL. Thx.
PNO, PCPTCode, OtherPCPCode
40000028 80061 8373584100841008443684443447984480850258661786618
-
This will work with a small number of records or where you don't need for it to work fast:
declare @PNO int
declare @OtherPCPCode varchar(3000)
create table #newPCP (
PNO int not null,
PCPTCode int null,
OtherPCPCode varchar(3000) null,
IsDone bit default(0)
)
insert into #newPCP (PNO, PCPTCode)
select PNO, min(PCPTCode) from OutPatientMedStat
group by PNO
select top 1 @PNO= PNO from #newPCP where IsDone = 0
while (@PNO is not null)
begin
set @OtherPCPCode = ''
select @OtherPCPCode = isnull(@OtherPCPCode, '') + isnull(convert(varchar, o.PCPTCode ), '')
from OutPatientMedStat o
inner join #newPCP p
on o.PNO = p.PNO
where p.PNO = @PNO and p.PCPTCode <> o.PCPTCode
update #newPCP
set OtherPCPCode = @OtherPCPCode,
isDone = 1
where PNO = @PNO
set @PNO = null
select top 1 @PNO= PNO from #newPCP where IsDone = 0
end
Russel Loski, MCSE Business Intelligence, Data Platform
-
I have more than 20,000 records. The method would not work. I ran the procedure. It took more than forty five minutes, it is still running.
-
Is this a one time population of a table or is this a query that you are running frequently? If it is a one time population, then you aren't going to get much faster. If you run this frequently, do you get a bunch of records or do you just get one? Getting one is much faster.
I don't know how to do it, but if you were using SQL 2005 you could create a .Net assembly with a custom aggregate to do this.
Russel Loski, MCSE Business Intelligence, Data Platform
-
one time deal , but right now it has been running more than 1.45 hr. I have more than 15,000 records with different number.
-
Frances:
Leveraging the power of a user-defined function can really help in this situation. Below is what I wrote to derive a solution that should work for you.
--Pete
-- CREATE A TABLE TO HOLD A COUPLE OF VALUES
CREATE TABLE PNO_CPTCODES
(
PNO VARCHAR(10),
CPTCode VARCHAR(6)
)
INSERT PNO_CPTCODES
(
PNO,
CPTCODE
)
SELECT '40000028', '80061'
UNION
SELECT '40000028', '83735'
UNION
SELECT '40000028', '84100'
UNION
SELECT '40000028', '84436'
UNION
SELECT '40000028', '84443'
UNION
SELECT '40000028', '84479'
UNION
SELECT '40000028', '84480'
UNION
SELECT '40000028', '85025'
UNION
SELECT '40000028', '86617'
UNION
SELECT '40000028', '86618'
-- CREATE A USER DEFINED FUNCTION TO CONCATENATE CPTCODES
CREATE FUNCTION DBO.UDF_CONCAT_CPT (@PNO VARCHAR(10), @CPTCODE VARCHAR(10))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @CONCAT VARCHAR(1000)
SET @CONCAT = ''
SELECT @CONCAT = @CONCAT + ISNULL(CPTCODE, '')
FROM DBO.PNO_CPTCODES
WHERE PNO = @PNO
AND CPTCODE<> @CPTCODE
ORDER BY CPTCODE /* REMOVE THIS LINE IF THE ORDER OF THE CONCATENATED CPTCODES IS IRRELEVANT */
RETURN(@CONCAT)
END
-- QUERY THE DATA
SELECT
PNO,
MIN_PCT = MIN(CPTCODE),
CONCAT = DBO.UDF_CONCAT_CPT(PNO, MIN(CPTCODE))
FROM PNO_CPTCODES
GROUP BY
PNO
Hope that helps! --Pete
-
Theory wise, it will work but it still take more than one hour's hr. I think .. Thx you for your time.
-
The query is slow because it requires SQL Server to do something that it isn't really built for. Basically, for every record it has to build a string. It does that 15,000 times. It has to run 15,000 queries.
That is why I added my caviat that this query works only if you have a small number of records or you don't worry about performance.
Russel Loski, MCSE Business Intelligence, Data Platform
-
Frances -- what kind of hardware is your sql server on? Pentium II?
I just populated a test table with 190,000 records of sample cptcode data, and then ran the query I suggested previously using the user-define function, and I ended up populating a temp table with desired results in 3 seconds. Honest, 3 seconds vs 1 hr.
I also slapped on a clustered index on the PNO field before running the query.
If you wish, I can send you the code I used to build the test data set.
--Pete
-
Pete, you nailed something that I hadn't thought about. This query would work quickly with an index on the CPTCODE column.
Russel Loski, MCSE Business Intelligence, Data Platform
-
Pete:
You can post the code here, so that other people would benefit. I pm you my email address , too.
Thx.
Frances
-
OK -- here's the code i used to test my proposed solution of using a user-defined function to concantenate the cpt codes:
-- CREATE A TABLE TO HOLD A COUPLE OF VALUES
CREATE TABLE dbo.PNO_CPTCODES
(
PNO VARCHAR(10),
CPTCode VARCHAR(6)
)
INSERT PNO_CPTCODES
(
PNO,
CPTCODE
)
/* put lots of records in the table; for simplicity reuse the same cpts, but increment the pno by 1 for each set of cpts. */
DECLARE @RECORDS INT, @PNO VARCHAR(10), @val INT
SET NOCOUNT ON
SET @RECORDS = 1
SET @val = 40000028
WHILE @RECORDS <=300000
BEGIN
SELECT @PNO = CONVERT(VARCHAR(10),@VAL+1)
INSERT PNO_CPTCODES
(
PNO,
CPTCODE
 
SELECT @PNO, '80061'
UNION
SELECT @PNO, '83735'
UNION
SELECT @PNO, '84100'
UNION
SELECT @PNO, '84436'
UNION
SELECT @PNO, '84443'
UNION
SELECT @PNO, '84479'
UNION
SELECT @PNO, '84480'
UNION
SELECT @PNO, '85025'
UNION
SELECT @PNO, '86617'
UNION
SELECT @PNO, '86618'
SELECT @RECORDS = @RECORDS + 1
END
SET NOCOUNT OFF
-- slap on an index for speed
CREATE CLUSTERED INDEX PNO_CIDX ON PNO_CPTCODES(PNO)
/* NOTE: if you decide to rerun this code, be sure to drop the index prior to repopulating the table pno_cptcodes. */
/* build a temp table the quink-n-dirty way which will store the desired results. */
SELECT TOP 0
PNO,
MIN_PCT = MIN(CPTCODE),
CONCAT = DBO.UDF_CONCAT_CPT(PNO, MIN(CPTCODE))
INTO #RESULTS
FROM PNO_CPTCODES
GROUP BY
PNO
-- populate the temp table
INSERT #RESULTS
SELECT
PNO,
MIN_PCT = MIN(CPTCODE),
CONCAT = DBO.UDF_CONCAT_CPT(PNO, MIN(CPTCODE))
FROM PNO_CPTCODES
GROUP BY
PNO
-- see some results
SELECT TOP 10 * FROM #RESULTS
-
SELECT TOP 0
PNO,
MIN_PCT = MIN(CPTCODE),
CONCAT = DBO.UDF_CONCAT_CPT(PNO, MIN(CPTCODE))
INTO #RESULTS
FROM PNO_CPTCODES
GROUP BY
PNO
I do not know select top 0 mean here. It return 0 record.
while I run SELECT TOP 10 * FROM #RESULTS
Thx.
-
The "Select Top 0... into #Results From..." merely builds a table structure without data -- a lazy way, but effective, to build a table without using a formal Create Table statement.
As for the SELECT TOP 10 * FROM #RESULTS, I merely limited the number of rows to 10, rather than returning the entire dataset from #results, as a way to prove how quickly the process can be completed.
In a production situation, I suspect you would populate an actual table to report from rather than a temp table as I had done. The temp table was merely for demonstration purposes.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply