March 16, 2010 at 4:04 pm
hi,
i need to modify and make no. of rows for a member to 1 row for a member,based on the id, their can be only 3 uniquenumbers for a member.
CREATE TABLE #temp123
(
membername varchar(50) NULL,
uniqueNum varchar(20) NULL,
id int null
)
the values i get in #temp123
are:
membername uniqueNum id
abc 4567A4 1
mmm 23555A 2
abc 23456A 3
abc 23456 4
bdb 56778 5
bdb 23555 6
the result i want after the query is
membername uniqueNum1 uniqueNum2 uniqueNum3
abc 23456 23456A 4567A4
mmm 23555A
bdb 23555 56778
the values in the id which is highest should go in uniqueNum1 ,
if only one id exists then that goes in uniquenum1.
Can you direct me how to code this and store in a table .
Thanks
March 16, 2010 at 4:30 pm
Please have a look at the CrossTab article referenced in my signature.
If the number of uniqueNum columns is unknown, you might want to have a look into the DynamicCrossTab article as well.
March 17, 2010 at 12:51 am
Why can't you use XML here instead of the table approach? I think it will be more elegant.
create table #t(membername varchar(10), uniquenum varchar(10), id int)
insert into #t values('abc', '4567A4', 1)
insert into #t values('mmm', '23555A', 2)
insert into #t values('abc', '23456A', 3)
insert into #t values('abc', '23456', 4)
insert into #t values('bdb', '56778', 5)
insert into #t values('bdb', '23555', 6)
select
membername,
(
select uniquenum
from #t where membername = t.membername
for xml path(''), root('uniquenumroot'),type
) 'uniquenum'
from #t t
group by membername
drop table #t
I am not sure if that is the best way to generate the XML part. Please compare the execution plans if you have other approaches.
https://sqlroadie.com/
March 17, 2010 at 5:07 am
SELECT Pvt.membername,
uniqueNum1 = Pvt.[1],
uniqueNum2 = Pvt.[2],
uniqueNum3 = Pvt.[3]
FROM (
SELECT membername,
uniquenum,
rn = ROW_NUMBER() OVER (PARTITION BY membername ORDER BY id)
FROM #t
) Source
PIVOT (
MAX(uniquenum) FOR
rn IN ([1],[2],[3])
) Pvt;
March 18, 2010 at 7:51 am
thank you all for the response.
i used the pivot and it worked great!
kudos to forum members..
March 18, 2010 at 8:41 am
Thanks for the feedback!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy