December 23, 2010 at 5:42 am
Hi,
Can you please help me to convert the below rows into columns.
Input table :
dealerid amgid arrivalmonth
----------- ----------- ------------
100 200 Jan
100 200 Feb
100 201 Jan
100 201 Mar
101 203 Apr
101 203 May
101 203 Jan
102 204 Dec
Output should be like this :
dealerid amgid arrivalmonth
----------- ----------- ------------
100 200 Jan,Feb
100 201 Jan,Mar
101 203 Apr,May,Jan
102 204 Dec
Script to create the table is mentioned below.
create table vehiclearrivalmonth
(
dealeridint,
amgidint,
arrivalmonthvarchar(3)
)
insert into vehiclearrivalmonth
values (100, 200, 'Jan')
insert into vehiclearrivalmonth
values (100, 200, 'Feb')
insert into vehiclearrivalmonth
values (100, 201, 'Jan')
insert into vehiclearrivalmonth
values (100, 201, 'Mar')
insert into vehiclearrivalmonth
values (101, 203, 'Apr')
insert into vehiclearrivalmonth
values (101, 203, 'May')
insert into vehiclearrivalmonth
values (101, 203, 'Jan')
insert into vehiclearrivalmonth
values (102, 204, 'Dec')
December 23, 2010 at 6:17 am
This?
create table vehiclearrivalmonth
(
dealerid int,
amgid int,
arrivalmonth varchar(3)
)
insert into vehiclearrivalmonth
values (100, 200, 'Jan')
insert into vehiclearrivalmonth
values (100, 200, 'Feb')
insert into vehiclearrivalmonth
values (100, 201, 'Jan')
insert into vehiclearrivalmonth
values (100, 201, 'Mar')
insert into vehiclearrivalmonth
values (101, 203, 'Apr')
insert into vehiclearrivalmonth
values (101, 203, 'May')
insert into vehiclearrivalmonth
values (101, 203, 'Jan')
insert into vehiclearrivalmonth
values (102, 204, 'Dec')
select dealerid,amgid,stuff((select ',' + arrivalmonth from vehiclearrivalmonth v1
where v1.dealerid=v2.dealerid and v1.amgid=v2.amgid for xml path('')),1,1,'')arrivalmonth
from vehiclearrivalmonth v2
group by dealerid,amgid
drop table vehiclearrivalmonth
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
December 23, 2010 at 7:16 am
Excellent !! Thanks for the quick resolution.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply