0

Please help me create the select sql in Microsoft SQL Server, i want convert from the table:

enter image description here

convert to the new table, get the distinct DeviceCode and new columns get from PartName values, and the PartName values is unlimit (DVD, Mouse, Keyboard, Monitor…..):

I just attach full image in there, with more sample data, sorry i cant add a table viewer in the post: https://farm6.staticflickr.com/5628/30615995995_a0ed8b65fa_o_d.jpg

ChrisB
  • 2,497
  • 2
  • 24
  • 43
Hien Nguyen
  • 53
  • 1
  • 5
  • Can other part types show up? What about several parts of same type for a device? – jarlh Oct 28 '16 at 09:57
  • There are several parts for a device, device can add more parts later: example - pc have some parts: CPU, RAM, Motherboard, Mouse, Keyboard, Monitor, Power suply..... The Part is only 2 info: PartName and Info – Hien Nguyen Oct 28 '16 at 09:59
  • So add some more sample data, to make things more complex! – jarlh Oct 28 '16 at 10:01
  • please view the image, i just edit some more sample data: https://farm6.staticflickr.com/5628/30615995995_a0ed8b65fa_o_d.jpg – Hien Nguyen Oct 28 '16 at 10:11
  • I think the OP is re-asking this same question: http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server – Jason D Oct 31 '16 at 05:47

2 Answers2

0
 create table #d
( 
device_id int,
device_code varchar(10),part_id int,part_name varchar(100),info varchar(100)
)
insert into #d values
(1,'1.1g',1,'ram','szaga'),
(1,'1.1g',3,'mother boad','saagg'),
(1,'1.1g',4,'cpu','dgsg')


   select device_id,device_code,
      max(case when part_name = 'ram' then info end) Firstname,
      max(case when part_name = 'mother boad' then  info end) Amount,
      max(case when part_name = 'cpu' then info end) PostalCode
    from #d
    group by device_id,device_code
Chanukya
  • 5,833
  • 1
  • 22
  • 36
0

I had a new solved detail quest in Topic:

Create dynamic select get value for column name - in SQL Server

Many thanks for the consultant Yared who help me solved this as my expected.

Best Regards, Hien Nguyen

Community
  • 1
  • 1
Hien Nguyen
  • 53
  • 1
  • 5