need to combine multiple rows to a single row

Question

Post reply

need to combine multiple rows to a single row

dsandhyarao

Say Hey Kid

Points: 683
More actions
March 16, 2010 at 4:04 pm

#94646

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

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.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 2

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/

Paul White SSC Guru Points: 150467 More actions · Answer 3

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;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

dsandhyarao Say Hey Kid Points: 683 More actions · Answer 4

thank you all for the response.

i used the pivot and it worked great!

kudos to forum members..

Paul White SSC Guru Points: 150467 More actions · Answer 5

Thanks for the feedback!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi