How to combine multiple rows in to a single recors

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to combine multiple rows in to a single recors

Author

Topic

rwilson
Starting Member

1 Post

Posted - 2010-05-26 : 09:58:11

Hi all

I am trying to combine multiple records in to one record ideally through a view if its possible? The fields ConsumerId and CampaignId would be used to group by then all the PinCode associated to the consumer and campaign would be in the same row.

So I have...

ConsumerId | CampaignId | PinCode
1 | 10 | 1234
1 | 10 | 2345
2 | 15 | 345
2 | 15 | 546
2 | 56 | 2345

and would like to output

ConsumerId | CampaignId | Pin1 | Pin2 | Pin3 | Pin4
1 | 10 | 1234 | 2345 | |
2 | 15 | 345 | 546 | |
2 | 56 | 2345 | | |

They'll only ever be 4 pins linked to a consumer and campaign

I'm on sql 2008 - I've tried looking in to pivot tables but haven't been able to get it to do what I'm looking for

thanks,
Ray

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-26 : 11:26:57

Try this

select ConsumerId
, CampaignId
, max(case when seq = 1 then PinCode else null end) as Pin1
, max(case when seq = 2 then PinCode else null end) as Pin2
, max(case when seq = 3 then PinCode else null end) as Pin3
, max(case when seq = 4 then PinCode else null end) as Pin4
from
(
select row_number() over(partition by ConsumerId,CampaignId order by PinCode) as seq, * from ConsumerTable 
) t
group by ConsumerId,CampaignId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources