Hi all
I have data as table below:
ID ProductID Name
----------------------------------------------------
1 1 AAA
2 1 BBB
3 1 CCC
4 2 DDD
5 2 EEE
6 2 FFF
7 3 GGG
8 3 HHH
9 3 III
When show on gridview I don't show this.
I want to show below:
ID ProductID Name1 Name2 Name3
---------------------------------------------------------------------
1 1 AAA BBB CCC
2 2 DDD EEE FFF
3 3 GGG HHH III
Please help me.
Thank Advance
Loading
naura paxPosted Nov 11, 2009, 2:52 AM
create table #production(ID int identity(1,1),ProductID int,Name varchar(10))
insert into #production
select 1,'AAA' union all
select 1,'BBB' union all
select 1,'CCC' union all
select 2,'DDD' union all
select 2,'EEE' union all
select 2,'FFF' union all
select 3,'GGG' union all
select 3,'HHH' union all
select 3,'III'
Select ProductID,max(Names1) as Names1,max(Names2) as Names2,max(Names3) as Names3 from (
SELECT ID,ProductID,(case when Num=1 then Name else '' end) as Names1,
(case when Num=2 then Name else '' end) as Names2,
(case when Num=3 then Name else '' end) as Names3 from (
select ID,ProductID,Name, Row_Number() over (partition by ProductID order by ProductID) as Num from #production )
tbl )
tbl2 group by productid
drop table #production
Manath PathanaPosted Nov 11, 2009, 2:30 AM
yes you query it can not select data that i want.
you have new for select it?
naura paxPosted Nov 11, 2009, 2:21 AM
'It cannot select' means? i mean what is exactly that you can't do?
Manath PathanaPosted Nov 11, 2009, 2:15 AM
naura paxPosted Nov 11, 2009, 1:19 AM
Select ID,ProductID,Names1,Names2,Names3 from (
SELECT ID,ProductID,(case when id=1 then names else '') as Names1,
,(case when id=2 then names else '') as Names2,
(case when id=3 then names else '') as Names3 from (
select ProductID,Name, Row_Number() over (partition by ProductID order by ProductID) as ID from tblProduct ) tbl ) tbl2 where len(names1)>0 and len(names2)>0 and len(names3)>0
I haven't tested it though i'm a bit not sure about pivot
Manath PathanaPosted Nov 11, 2009, 1:08 AM
you don't see about ID, you think only productID.
I want to convert row to column, productID have 3 row.
but i want to convert it to column.
please help me.
thank
naura paxPosted Nov 11, 2009, 1:01 AM
can you explain why will ID=2 and ProductID=2 be in the same line
or for that matter how can all three names be shown in front of same ID, is ID here just a rank?
ID ProductID Name1 Name2 Name3
---------------------------------------------------------------------
1 1 AAA BBB CCC
2 2 DDD EEE FFF
3 3 GGG HHH III
Nilanka DharmadasaPosted Nov 11, 2009, 12:56 AM
You can do this easily.
First get the datatble from databse.
Then you can create a datatable (For the second table you have given) from code. For this get a help from this place.
http://www.aspnettutorials.com/tutorials/controls/data-table-csharp.aspx
Then read data from first table and load them to the second one you created. For this also, you can get a help from above link.