0

I have face a problem to re-format a sql table. The original table can be regard as in following format

index1 index2 item1 item2
    01    001    10   100
    02    001    10   100
   ...
    01    002    20   200
    02    002    20   200
   ...

and I want to re-format it into table like

index2   01item1   01item2   02item1   02item2     ...
   001        10       100        10       100     ...
   002        20       200        20       200     ...
...

index 1 and index 2 can mean any index except for primary key (for example i can call them color {red, yellow, blue} and shape {square, circle, bar},) and items indicate to of some certain type (in this example you can say a red circle), what data does that contains (for instance, item1 means weight and data is 80, item2 means height and data is 100, etc.)

I have looked at the following questions (Efficiently convert rows to columns in sql server), I believe my solution should exists in there but still could not get it, since I can't understand how to use pivot, and also I think my case is even complex than that.

It is safe to make assumption that the total number of index exists in index 1 are the same in all kinds of index2 (that is say, if there are 10 index1 in 001, than there will be 10 index 1 in 002 as well)

(hopefully it will not make people even more confuse about my question) so in the end I want to run a query looks like:

string builder sb = new string builder;
    foreach(int indexNo2 in index2){
        foreach(int indexNo1 in index1{
            sb.append("select item1 as" + indexNo1+"item01, item2 as" +indexNo2+"item02
            where index1 =" + indexNo1 + " and index2 =" + indexNo2 + "inner join");
         }
         sb.append("unit"+endl);
  }

However this is just how I want to make the result look like, does not mean I really want to build such a long query at the end of day. PS. Even though I tried to write a c# suitcode to build a query, I want to find a solution in sql to re-format the table

Community
  • 1
  • 1
n4feng
  • 2,226
  • 3
  • 11
  • 17
  • Could you add some sample code (what have you attempted so far). Pivot is probably the way to do it. BUT - maybe a non-SQL way would be better (programming language R for example has a melt command). It is difficult to understand what you want... terms like index1 and item1 suggest a mismatch of terms (SQL index?). Does index{1} mean Primary key (or key) and item{n} mean non-key data? Thanks. – ripvlan Jul 11 '16 at 17:52
  • underscores means space since if you only add space there, the form does not show difference – n4feng Jul 11 '16 at 18:06
  • let me add some suitcode – n4feng Jul 11 '16 at 18:07
  • index 1 and index 2 can mean any index except for primary key (for example i can call them color {red, yellow, blue} and shape {square, circle, bar},) and items indicate to of some certain type (in this example you can say a red circle), what data does that contains (for instance, item1 means weight and data is 80, item2 means height and data is 100, etc.) – n4feng Jul 11 '16 at 18:27
  • Can you clarify where you're trying to get the results, please? In other words, is your UI language something which is able to generate the pivots for you, rather than pivoting in SQL? I see you reference a `string builder` - what language are you using for that? – David T. Macknet Jul 11 '16 at 18:28
  • for that code i am just trying to build a query dynamically through c# as a suitcode (only because I use c# pretty much), but I want to find a sql solution that could re-format my table on sql server – n4feng Jul 11 '16 at 18:32

0 Answers0