how to reorder data from row view to collumn view

• manik-255187

  SSC Rookie

  Points: 38

  More actions

  October 3, 2005 at 4:35 am

  #169264

  i'm sorry if this thread allready discussed

  i have data tabel that order in row

  ex :

  data1 data2 data3 data4 data5 data6

  i want to order it in collumn

  ex:

  data1 data2

  data3 data4

  data5 data6

  i usualy solve it with looping in user defined procedure

  i want know if there is another way to do it

  or if there is a function that ready to use

  thank's

  best regard

  manik'095

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  October 3, 2005 at 6:03 am

  #594515

  Search here for row to column or PIVOT should help you find the threads to help you

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• Papillon

  Hall of Fame

  Points: 3303

  More actions

  October 3, 2005 at 10:48 pm

  #594719

  Hi!!

  Try to understand concept of it n then use this!!

  Donot play blindly

  Create PROC sp_CrossTab  @table       AS sysname,        -- Table to crosstab  @onrows      AS nvarchar(128),  -- Grouping key values (on rows)  @onrowsalias AS sysname = NULL, -- Alias for grouping column  @oncols      AS nvarchar(128),  -- Destination columns (on columns)  @sumcol      AS sysname = NULL  -- Data cellsASDECLARE  @sql AS varchar(8000),  @NEWLINE AS char(1)SET @NEWLINE = CHAR(10)-- step 1: beginning of SQL stringSET @sql =  'SELECT' + @NEWLINE +   '  ' + @onrows +  CASE    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias    ELSE ''  ENDCREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)DECLARE @keyssql AS varchar(1000)SET @keyssql =   'INSERT INTO #keys ' +  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +  'FROM ' + @tableEXEC (@keyssql)DECLARE @key AS nvarchar(100)SELECT @key = MIN(keyvalue) FROM #keysWHILE @key IS NOT NULLBEGIN  SET @sql = @sql + ','                   + @NEWLINE +    '  SUM(CASE CAST(' + @oncols +                     ' AS nvarchar(100))' + @NEWLINE +    '        WHEN N''' + @key +           ''' THEN ' + CASE                          WHEN @sumcol IS NULL THEN '1'                          ELSE @sumcol                        END + @NEWLINE +    '        ELSE 0'                      + @NEWLINE +    '      END) AS c' + @key    SELECT @key = MIN(keyvalue) FROM #keys  WHERE keyvalue > @keyENDSET @sql = @sql         + @NEWLINE +  'FROM ' + @table      + @NEWLINE +  'GROUP BY ' + @onrows + @NEWLINE +  'ORDER BY ' + @onrowsPRINT @sql  + @NEWLINE -- For debugEXEC (@sql)GORegards

  Shashank 

  ---

  Regards,

  Papillon