# Forum

### A Transpose function

Contributor
Offline
Posts: 185
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

### A Transpose function

This example shows how to transpose a resultset table in a similar way to the SAS TRANSPOSE function.

It does not attempt to replicate all the SAS Transpose function options, but these could be relatively easily added.

It is not recommended for lots of data, up to a few thousand rows should be OK.

First is a transpose module written in Lua (called "transpose.kog"):

Code: Select all

``````local trans = {}
/* A helper function to create a unique identifier out of the data in the "BY"
* fields for a row.
*/
function trans.rowSpecifier(row, by)
local x = {}
for _,c in ipairs(by) do
table.insert(x, row[c])
end
return table.concat(x,"#")
end

/* Transpose a resultset table.
* NOTE: not all the SAS transpose procedure options are supported.
*/
function trans.transpose(t, by, var, id)
local out = {}
local unique_id = {}
local unique_ids = {}
local unique_by = {}
local rows = t.rows

-- find unique values in the columns specified by the "by" field
for _,r in ipairs(rows) do
unique_id[r[id]] = 1
local x = {}
for _,c in ipairs(by) do
table.insert(x, r[c])
end
unique_by[trans.rowSpecifier(r,by)] = x
end
-- unique_by = { "1001#Grocery" => {1001,Grocery}, "1001#Beauty Care" => {1001,Beauty Care} }

-- Get all the column headers
newcols = by;
for i2,v2 in pairs(unique_id) do
table.insert(unique_ids,i2)
end
table.sort(unique_ids)
for i,v in pairs(unique_ids) do
table.insert(newcols,v)
end
-- newcols = {"Store,Category,Week_1,Week_2,Week_3,Week_4,Week_5,Week_6,Week_7,Week_8}

for i,r in ipairs(rows) do
local val = r[var]
local row_id = r[id]
unique_by[trans.rowSpecifier(r,by)][row_id] = val
end
newrows = {}
for i,v in pairs(unique_by) do
for _,v2 in pairs(unique_ids) do
local v3 = v[v2]
if v3 then table.insert(v, v3) else table.insert(v, "") end
end
table.insert(newrows,v)
end
out.colNames = newcols
out.numCols  = #newcols
out.rows     = newrows
out.numRows  = #newrows
return out
end

return trans;
``````
And here is an example of how to use the transpose module:

Code: Select all

``````/*
This example shows how to transpose a resultset table in a similar way to the SAS TRANSPOSE function

Store   Category       Week_Number    Dollars
1001    Grocery        Week_1         2154
1001    Grocery        Week_2         2117
1001    Grocery        Week_3         2147
1001    Grocery        Week_4         2085
1001    Grocery        Week_5         1901
1001    Grocery        Week_6         2175
1001    Grocery        Week_7         2147
1001    Grocery        Week_8         2030
1001    Beauty Care    Week_1         664
1001    Beauty Care    Week_2         603
1001    Beauty Care    Week_3         828
1001    Beauty Care    Week_4         678
1001    Beauty Care    Week_5         763
1001    Beauty Care    Week_6         708

Output_Table
Store  Category     Week_1 Week_2 Week_3 Week_4 Week_5 Week_6 Week_7 Week_8
1001   Grocery      2154   2117   2147   2085   1801   2175   2147   2030
1001   Beauty Care  664    603    828    678    763    708

For the SAS procedure:

PROC TRANSPOSE DATA=input_table OUT=output_table;
BY store_no category;
VAR dollars;
ID week_number;
RUN;

*/

test = "transpose_test"
create schema \$(test);
drop table \$(test).input_table;
create table \$(test).input_table (store int, category varchar(100), week_number varchar(10), dollars int);
insert into \$(test).input_table values (1001, 'Grocery', 'Week_1', 2154);
insert into \$(test).input_table values (1001, 'Grocery', 'Week_2', 2117);
insert into \$(test).input_table values (1001, 'Grocery', 'Week_3', 2147);
insert into \$(test).input_table values (1001, 'Grocery', 'Week_4', 2085);
insert into \$(test).input_table values (1001, 'Grocery', 'Week_5', 1901);
insert into \$(test).input_table values (1001, 'Grocery', 'Week_6', 2175);
insert into \$(test).input_table values (1001, 'Grocery', 'Week_7', 2147);
insert into \$(test).input_table values (1001, 'Grocery', 'Week_8', 2030);
insert into \$(test).input_table values (1001, 'Beauty Care', 'Week_1', 664);
insert into \$(test).input_table values (1001, 'Beauty Care', 'Week_2', 603);
insert into \$(test).input_table values (1001, 'Beauty Care', 'Week_3', 828);
insert into \$(test).input_table values (1001, 'Beauty Care', 'Week_4', 678);
insert into \$(test).input_table values (1001, 'Beauty Care', 'Week_5', 763);
insert into \$(test).input_table values (1001, 'Beauty Care', 'Week_6', 708);

-- Forget about any existing transpose package
local transpose = require "transpose"

input_table = select * from \$(test).input_table ;
output_table = transpose.transpose(input_table,{'STORE', 'CATEGORY'}, 'DOLLARS', 'WEEK_NUMBER')

print(table.concat(output_table.colNames,", "))
for i,v in pairs(output_table.rows) do
print(i .. ": " .. table.concat(v,", "))
end
``````

### Who is online

Users browsing this forum: No registered users and 4 guests