how to repeat columns based on rows

Question

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

The best way to do this is what is known as a cross tab. These come in two varieties, static and dynamic. I will demonstrate the static version.

Notice how I posted ddl and sample data in a readily consumable format. This is something you should do in the future.

create table #Something

(

ID int,

MyDate date,

Created varchar(10)

);

insert #Something

select 1, '1/1/2012', 'Sudheer' union all

select 1, '1/2/2013', 'Sudheer' union all

select 1, '3/3/2013', 'Sudheer' union all

select 2, '1/2/2014', 'Veera' union all

select 2, '2/5/2015', 'Veera';

with MyCTE as

(

select *,

ROW_NUMBER() over (partition by ID order by ID, MyDate) as RowNum

from #Something

)

select ID,

max(case when RowNum = 1 then MyDate end) as MyDate1,

max(case when RowNum = 1 then Created end) as Created1,

max(case when RowNum = 2 then MyDate end) as MyDate2,

max(case when RowNum = 2 then Created end) as Created2,

max(case when RowNum = 3 then MyDate end) as MyDate3,

max(case when RowNum = 3 then Created end) as Created3

from MyCTE

group by ID;

drop table #Something;

You can read more about cross tabs by following the links in my signature.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

raghu.sudheer SSC Veteran Points: 210 More actions · Answer 2

Thank you so much. I will keep in mind in future to present the data as you mentioned.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Glad that worked for you. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.