how to merge two row data to one line 1

[varadha72]

Hi,

Can someone help me here.
I need to create a query that will convert my 2 or 3 line record output to one line when the ID is same.

ID...RecordCount..Name........Row.....Exception
1......1..........ABC..........10.......5
2......1..........DEF..........5........6
2......2..........DEF..........0........5
2......3..........DEF..........5........1
3......1..........GHI..........5........5

Required Output:

ID..RecordCount..Name........Row...Exception....Row....Exception...Row...Exception
1......1.........ABC.........10.......5.........NULL....NULL.......NULL....NULL
2......1.........DEF..........5.......6..........0.......5..........5........1
3......1.........GHI..........5........5.........NULL....NULL......NULL.....NULL

 

[gmmastros]

What is there are 4 rows, or 40 rows, or 400 rows. What do you want then?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk53]

not sure what you going to do with that, but

declare @tbl as table
(
ID int, 
RecordCount int,
Name varchar(20),
Row int,
Exception int
)
insert into @tbl
select 1, 1,'ABC',10,5
union 
select 2,1,'DEF',5,6
union
select 2,2,'DEF',0,5
union
select 2,3,'DEF',5,1
union
select 3,1,'GHI',5,5

;with cte as
(
select ROW_NUMBER() over(partition by id order by RecordCount) as rowNum,*
from @tbl)
select c1.ID, c1.RecordCount, c1.Name, c1.Row,c1.Exception,
		c2.Row,c2.Exception,c3.Row,c3.Exception
from cte c1
left join cte c2
	on c1.rowNum + 1 = c2.rowNum
left join cte c3
	on c2.rowNum + 1 = c3.rowNum	
where c1.rowNum=1