Combine Multiple Rows single column into multiple column single ro
in [Programming SQL]
|
Prev: The database is being closed before database mirroring is fully initialized. The ALTER DATABASE command failed
Next: IRR calculation
From: Linchi Shea on 3 Sep 2009 15:46 select vst_int_id, [1] as id1, [2] as id2, [3] as id3, [4] as id4, [5] as id5 from (select vst_int_id, cpt4_int_id, rank() over (partition by R1 order by cpt4_int_id) R2 from (select vst_int_id, cpt4_int_id, RANK() over (order by vst_int_id) as R1 from TPM319 ) as t1) as t2 pivot (max(cpt4_int_id) for R2 in ([1], [2], [3], [4], [5])) as pvt order by i Linchi "rob muzzy" wrote: > Hi, I have a table called TPM319 that contains two columns vst_int_id > and a cpt4_int_id > > what i want to do is grab the first 5 values from the cpt4_int_id > column and show them next to the vst_int_id > > > and example is > vst_int_id cpt4_int_id > 576316 364 > 576317 269 > 576320 5123 > 576355 6967 > 576387 5119 > 576387 5417 > > > vst_int_id cpt4_int_id (occur1) cpt4_int_id (occur2) cpt4_int_id > (occur3) ect > 576316 364 > 576317 269 > 576320 5123 > 576255 6967 > 576387 5119 5417 > > This shows that 57638 only had 2 occurances but it can go up to 5 > > can someone help me write a script to accomplish this > > Thanks > >
From: rob muzzy on 3 Sep 2009 16:05 On Sep 3, 3:46 pm, Linchi Shea <LinchiS...(a)discussions.microsoft.com> wrote: > select vst_int_id, [1] as id1, [2] as id2, [3] as id3, [4] as id4, [5] as id5 > from (select vst_int_id, cpt4_int_id, > rank() over (partition by R1 order by cpt4_int_id) R2 > from (select vst_int_id, cpt4_int_id, > RANK() over (order by vst_int_id) as R1 > from TPM319 ) as t1) as t2 > pivot (max(cpt4_int_id) for R2 in ([1], [2], [3], [4], [5])) as pvt > order by i > > Linchi > > > > "rob muzzy" wrote: > > Hi, I have a table called TPM319 that contains two columns vst_int_id > > and a cpt4_int_id > > > what i want to do is grab the first 5 values from the cpt4_int_id > > column and show them next to the vst_int_id > > > and example is > > vst_int_id cpt4_int_id > > 576316 364 > > 576317 269 > > 576320 5123 > > 576355 6967 > > 576387 5119 > > 576387 5417 > > > vst_int_id cpt4_int_id (occur1) cpt4_int_id (occur2) cpt4_int_id > > (occur3) ect > > 576316 364 > > 576317 269 > > 576320 5123 > > 576255 6967 > > 576387 5119 5417 > > > This shows that 57638 only had 2 occurances but it can go up to 5 > > > can someone help me write a script to accomplish this > > > Thanks- Hide quoted text - > > - Show quoted text - It is giving me rank () is not a recognized function name
From: rob muzzy on 8 Sep 2009 08:27 Yes I am using SQL 2000
From: Plamen Ratchev on 8 Sep 2009 12:59
Here is one method for SQL Server 2000. Note performance will not be good on a large data set. SELECT vst_int_id, MAX(CASE WHEN rk = 1 THEN cpt4_int_id END) AS [1], MAX(CASE WHEN rk = 2 THEN cpt4_int_id END) AS [2], MAX(CASE WHEN rk = 3 THEN cpt4_int_id END) AS [3], MAX(CASE WHEN rk = 4 THEN cpt4_int_id END) AS [4], MAX(CASE WHEN rk = 5 THEN cpt4_int_id END) AS [5] FROM ( SELECT vst_int_id, cpt4_int_id, (SELECT COUNT(*) FROM TPM319 AS B WHERE B.vst_int_id = A.vst_int_id AND B.cpt4_int_id <= A.cpt4_int_id) AS rk FROM TPM319 AS A) AS T GROUP BY vst_int_id; -- Plamen Ratchev http://www.SQLStudio.com |