sql server - Dynamic columns generation on the basis of rows of a table in sql -

I have four columns item_ID, a table with color, size, weight, I show my table rows in one line Wish item 1, color 1, size 1, weight 1, item 2, color 2, ..........., item 4, color 4, size 4, weight 4 ...

The following is my table

  + --------- + -------- + -------- + ---- ---- + | Item_ID | Color | Size | Weight | + --------- + -------- + -------- + -------- + | 1 | Blue | Big | 65 | | 2 | Orange | Big | 57 | 3 | Lal Small | 12 | | 4 | Violet | Medium | 34   
 My desired result will be  
  + --------- + -------- + ----- --- + -------- ++ --------- + -------- + -------- + | Item_id1 | Color1 | Size 1 | Weight1 | Item_id2 | Color 2 | Size 2 | Weight2 | .... + --------- + -------- + -------- + -------- + ------ - - + -------- + -------- + --------------- | 1 | Blue | Big | 65 | 2 | Orange | Big | 57 ... ... + --------- + -------- + -------- + -------- + + ----- ---- + -------- + -------- + -------- +   
 Thanks in advance.   
 
 
 To get this result, you will need to do a few things:  
 Current data  
 Unexpected result  
 Use dynamic SQL because you have an unknown rows   
 Since you use SQL Server 2005+ You can use Cross Application to unpublish the data, in this process,  item_id ,  color ,  size  And  weight  and convert them to several rows:  
  select col + '_' + cast (seq as varchar (50)) value from value (item_id Weight in the form of 'seq', item_id, ('color', 'color', 'color', color), ('size', size), ('weight' cast (varchar (50))) (Calls, value);   
 Get results from:  
  | Col | VALUE ---------------------- | Item_id_1 | 1 | | Color_1 | Blue | | Size_1 | Big | | Weight_1 | 65 | | Item_id_2 | 2 | | Color_2 | Orange | | Size_2 | Big | | Weight_2 | 57 | Item_id_3 | 3 |   
 As you can see from the results, you now have several rows based on your original data.  COL  values ​​are values ​​that you will use in PIVOT. Full dynamic SQL code will be similar to:  
  DECLARE @ cols AS NVARCHAR (MAX), @search AS NVARCHAR (MAX) @cols = select STUFF ((SELECT ',' + Your Select from 'Tables Cross' ('item_id', 0 union, select all 'colors', 1 union all select 'size', 2 union Select all 'selection', 3) C (cola, by) group item_id, col Item_id is sorted by, so for xml path ('', for TYPE) .value ('.', 'NVARCHAR (MAX)'), 1,1, '') @query = 'SELECT' + @ cols + 'Set (select +' '_' '+ Cast (Varchar (50) as the Secret) value from col, (item The item must be labeled as SEQ, item_id, color, size, ('' color '', color), ('' size '', size), '(' 'item' ',' 'item' '' 'item_id' Cast (item_id varchar (50)), (weight 'C' ('+ + Cols +') for 'P' executed (@ Korea) (weight) as the (exchange) value (exchange)  < / Pre> 
 The end result is:  
  | ITEM_ID_1 | COLOR_1 | SIZE_1 | WEIGHT_1 | ITEM_ID_2 | COLOR_2 | SIZE_2 | WEIGHT_2 | ITEM_ID_3 | COLOR_3 | SIZE_3 | WEIGHT_3 | ITEM_ID_4 | COLOR_4 | SIZE_4 | WEIGHT_4 | -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------- | 1 | Blue | Big | 65 | 2 | Orange | Big | 57 3 | Lal Small | 12 | 4 | Violet | Medium | 34