c# - Linq or Tsql 'Pivot' -
i'm sure covered elsewhere i'm having problems figuring out elegant solution this.
id c d 1 apple red 1 pear orange 2 apple red 2 pear orange what trying end this:
id | benefit | value 1 | c | apple 1 | c | pear 2 | c | apple 2 | c | pear 1 | d | red 1 | d | orange 2 | d | red 2 | d | orange i can in linq scanning each column , adding list.
public class samplerow { public int32 id { get; set; } public string { get; set; } public string b { get; set; } } public class sampleoutput { public int32 id { get; set; } public string description { get; set; } public string value { get; set; } } list<samplerow> rows = new list<samplerow>(); rows.add(new samplerow { id = 1, = "apple", b = "red" }); rows.add(new samplerow { id = 1, = "pear", b = "orange" }); rows.add(new samplerow { id = 2, = "apple", b = "red" }); rows.add(new samplerow { id = 2, = "pear", b = "orange" }); list<sampleoutput> output = new list<sampleoutput>(); rows.foreach(row => output.add(new sampleoutput() { id = row.id, description = "a", value = row.a }) ); rows.foreach(row => output.add(new sampleoutput() { id = row.id, description = "b", value = row.b }) ); i'm wondering if there's better way. i'm open linq or tsql solutions. data stored in sql first table/object list.
i think clean , elegant solution use unpivot.
select id, benefit, value test unpivot (value benefit in (c, d)) unpvt order benefit, id here have working demo
http://sqlfiddle.com/#!6/12884/9
hope helps
Comments
Post a Comment