How to select data: Row as Column

Question

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

naura pax · Answer

its not perfect but try it we can refine later create table #production(ID int identity(11)ProductID intName 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 ProductIDmax(Names1) as Names1max(Names2) as Names2max(Names3) as Names3 from ( SELECT IDProductID(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 IDProductIDName Row_Number() over (partition by ProductID order by ProductID) as Num from #production ) tbl ) tbl2 group by productid drop table #production

Manath Pathana · Answer

Hi yes you query it can not select data that i want. you have new for select it?

naura pax · Answer

hi 'It cannot select' means? i mean what is exactly that you can't do?

Manath Pathana · Answer

It can not select.

naura pax · Answer

I think you can do that in query using a combination of row_number & cross tab or pivot may be Select IDProductIDNames1Names2Names3 from ( SELECT IDProductID(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 ProductIDName 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 Pathana · Answer

Thank for interest my question. 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 pax · Answer

Hey 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 Dharmadasa · Answer

Hi 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.