Post reply

how to reorder data from row view to collumn view

  • 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

  • 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

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply